[FAQs about foreign key constraints]

[FAQs about foreign key constraints]

 

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

Author: js_szy

Date: 2010-11-08

 

Version:

Microsoft SQL Server 2005-9.00.1399.06 (Intel x86)

Oct 14 2005 00:33:37

Copyright (c) 1988-2005 Microsoft Corporation

Developer Edition on Windows NT 5.1 (build 2600: Service Pack 3)

 

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

--> Test data: [TB]

If object_id ('[TB]') is not null drop table [TB]

Go

--> Test data: [Ta]

If object_id ('[Ta]') is not null drop table [Ta]

Go

 

Create Table [Ta]

(

Id int primary key,

Name varchar (1)

)

 

Create Table [TB]

(

Id1 int constraint fk_1 foreign key references TA (ID ),

Name1 varchar (1)

)

 

Insert into [Ta]

Select 1, 'A' Union all

Select 2, 'B'

 

Insert into [TB]

Select 1, 'q' Union all

Select 1, 'w' Union all

Select 2,'s'

 

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

I. Create foreign key constraints:

 

1. Create a foreign key constraint when creating a table:

 

Create Table [TB]

(

Id1 int constraint fk_1 foreign key references TA (ID ),

Name1 varchar (1)

)

 

2. Create a constraint on an existing table

 

Alter tabletb add constraint fk_1 foreign key (id1) References TA (ID)

 

 

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

Ii. Delete constraints:

 

Alter tabletb drop constraint fk_1

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

III. The following describes some common errors:

 

1. Create a sub-table first

 

/*

Error Message

Message 1767, level 16, status 0, 1st rows

The foreign key 'fk _ Tb _ id1 _ 7814d14c 'References an invalid table 'ta '.

Message 1750, level 16, status 0, 1st rows

The constraint cannot be created. See the preceding error message.

 

Solution:

Create a primary table first

*/

 

2. Delete the primary table first

/*

Error message:

Message 3726, level 16, state 1, 2nd rows

The object 'Ta' cannot be deleted because it is being referenced by a foreign key constraint.

Message 2714, level 16, status 6, 3rd rows

An object named 'ta 'already exists in the database.

 

Solution:

Delete a sub-table first

*/

 

3. insert sub-table data. The primary table does not have the key value inserted by the sub-table.

 

Insert into [TB]

Select 5, 'q' -- 5 does not exist in the master table

 

/*

Error message:

Message 547, level 16, status 0, 1st rows

The insert statement conflicts with the foreign key constraint "FK _ Tb _ id1 _ 5d60db10. This conflict occurs in the Database "szy", table "DBO. Ta", column 'id '.

The statement has been terminated.

Solution:

The primary table must be inserted before the child table is inserted.

*/

 

4. Delete primary table data first

 

Delete from Ta where id = 1

/*

Error message:

Message 547, level 16, status 0, 68th rows

The delete statement conflicts with the reference constraint "FK _ Tb _ id1 _ 047aa831. This conflict occurs in the Database "szy", table "DBO. TB", column 'id1 '.

The statement has been terminated.

 

Solution:

Delete the sub-table first and then the master table

Delete from TB where id1 = 1

Delete from Ta where id = 1

*/

 

5. Add a foreign key constraint to an existing table

(If the table has a key value that is not associated with the primary table and the sub-table, the following error is returned)

 

Alter tabletb add foreign key (id1) References TA (ID)

 

/*

Message 547, level 16, status 0, 20th rows

The alter table statement conflicts with the foreign key constraint "FK _ Tb _ id1 _ 23f3538a. This conflict occurs in the Database "szy", table "DBO. Ta", column 'id '.

*/

 

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

 

Conclusion: for the Operation Sequence of tables with foreign key constraints, the master is created first and then the child is created. Delete the child and then the master.