Foreign Key Relationships

Question

Foreign Key Relationships

Bryan P. Clauss

SSC Eights!

Points: 959
More actions
October 11, 2005 at 8:53 am

#166313

Question: When I create foreign key constraints in my tables, do they have any effect on insert/update/selects?
Thanks.

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 1

The server will have to validate that the data modifications you do do not break the fk constraint. Won't have any effects on the select unless you add indexes.

Adam Wilbur SSCertifiable Points: 7001 More actions · Answer 2

Also, in regards to Updates, if you specified cascading updates, this will obviously have an effect on your data.

A.J.
DBA with an attitude

rsharma SSCarpal Tunnel Points: 4865 More actions · Answer 3

It also has an effect on the locking behavior (it is one of the best practices to index your FKs)...one such example to show how locking behavior gets effected because of the FK:

/**********************************************************************************************/

Two tables: TableA and TableB which are related by a foreign key relationship (See the script below).

Session1: User 1 holds a lock on TableA (The parent table) through an update statement. He does not commit/rollback the transaction.

Session2: User 2 tries to do an insert into TableB. Since the lock timeout setting is set to 5 seconds, the client gets an error that the Lock Request timed out. (Occurs because internally the engine tries to validate the insert criteria by doing a select on the primary Table "TableA").

Script to reproduce the issue:

DROP TABLE TABLEB

DROP TABLE TABLEA

CREATE TABLE TABLEA(COL1 INT NOT NULL, COL2 VARCHAR(10), COL3 INT, COL4 VARCHAR(10))

GO

ALTER TABLE TABLEA ADD CONSTRAINT PK_TABLEA PRIMARY KEY CLUSTERED (COL1)

GO

CREATE TABLE TABLEB (COL1 INT NOT NULL, COL2 VARCHAR(30) NOT NULL, COL3 VARCHAR(30))

GO

ALTER TABLE TABLEB ADD CONSTRAINT PK_TABLEB PRIMARY KEY CLUSTERED (COL1, COL2)

GO

ALTER TABLE TABLEB ADD CONSTRAINT FK_TABLEB_TO_TABLEA FOREIGN KEY (COL1) REFERENCES TABLEA (COL1)

GO

INSERT INTO TABLEA (COL1, COL2, COL3, COL4) VALUES (1, 'TEST', 1, 'TEST-SQL')

INSERT INTO TABLEA (COL1, COL2, COL3, COL4) VALUES (2, 'TEST2', 1, 'TEST-SQL2')

INSERT INTO TABLEA (COL1, COL2, COL3, COL4) VALUES (3, 'TEST3', 1, 'TEST-SQL3')

INSERT INTO TABLEB (COL1, COL2, COL3) VALUES (1, 'TEST', 'TEST-SQL')

GO

--SESSION 1:

set cursor_close_on_commit off

set ansi_defaults off

BEGIN TRAN

UPDATE TABLEA SET COL4 = 'TEST-SQL1' WHERE COL1 = 2

--Do Not Commit Yet

--SESSION 2:

set cursor_close_on_commit off

set ansi_defaults off

set transaction isolation level read uncommitted

begin tran

select * from tableA with (nolock) where col1 = 2

set lock_timeout 5000

INSERT INTO TABLEB (COL1, COL2, COL3) VALUES (2, 'TEST-MOD', 'TESTSQL1')

--you will get the lock timeout error after 5 seconds

--ROLLBACK

/**********************************************************************************************/

A solution in the above mentioned "INSERT" scenario of a block would be to create the PK as non-clustered index.

Hth

Norman Kelm SSCommitted Points: 1922 More actions · Answer 4

non-clustered PK on which table and why would this solve the problem?

rsharma SSCarpal Tunnel Points: 4865 More actions · Answer 5

Non-clustered index on the PK of the parent table. You can try it out using the scripts given above.

The pages in the data chain and the rows in them are ordered on the value of the clustered index key so, when the PK of the parent table is a clustered index, you will see that the lock output will be like this:

spid blocked object_name mode indid

54 0 TABLEA IX 0

54 0 TABLEA IX 1

54 0 TABLEA X 1

55 54 TABLEA IS 0

55 54 TABLEA IS 1

55 54 TABLEA S 1

55 54 TABLEB IX 0

55 54 TABLEB IX 1

55 54 TABLEB X 1

As you can see, the second session (55) is trying to take an implicit S lock on the parent table on the clustered index (indid = 1) and since the first session (54) already has an exclusive lock on it, the second session gets blocked.

After changing the index to be a non-clustered index, the select will be satisfied off the non-clustered index itself without any need to go to the data pages.

spid blocked object_name mode indid

54 0 TABLEA IX 0

54 0 TABLEA X 0

55 0 TABLEB IX 0

55 0 TABLEB IX 1

55 0 TABLEB X 1

Hth