Key constraints outside the T-SQL Foundation

Create a test table first

 Usetest;Create Tabletest01 (Id1int  not NULL, Id2int  not NULL);Create Tabletest02 (Id11int  not NULL, Id22int  not NULL);Alter Tabletest01Add constraintPk_id1Primary Key(ID1);

Consider the following relationship

The ID11 in the test02 table relies on ID1 in test01, so creating a foreign key for test02

Alter Table Add constraint Fk_id11   foreignkeyreferences test01 (ID1);

Note: ID1 in the test01 table must be a primary key or a unique index, or a foreign key based on ID1 cannot be created.

After creating the foreign key, we will find that we cannot enter data in test02 that are not in the ID1 range of test01

Insert  into Values (1,1);

View Code

547  - 0 1 Line INSERT FOREIGN KEY column ' ID1 ' . Statement has been terminated. 

View Code

Creating a foreign key fails if you already have data in test02 that are not in the test01 ID1 range before the foreign key is created

Alter Table Drop constraint Fk_id11; Insert  into Values (1,1); Alter Table Add constraint Fk_id11   Foreign Key references test01 (ID1);

View Code

547  - 0 1 Line ALTER TABLE FOREIGN KEY column ' ID1 '.

View Code

You can now force skipping existing data checks with the WITH nocheck option

Alter Table  with Nocheck   addconstraint  fk_id11  foreignkeyreferences TEST01 (ID1);

Although ID1 is set to primary key in the test01 table, NULL is not allowed, but id2 in the test02 table can allow null values

Alter Table Alter column int null; Insert  into Values (null,1);

When we delete or modify data from the test01, if there is data in the TEST02 table will be error, reject the operation;

Insert  into Values (2,1); Insert  into Values (2,1); Update set id1=3where id1=2;

View Code

547  - 0 1 Line UPDATE column ' Id11 ' . Statement has been terminated. 

View Code

At this point we can synchronously delete or modify the data in two tables by cascading .

Alter Tabletest02Drop constraintFk_id11;Alter Tabletest02 with Nocheck    Add constraintFk_id11Foreign Key(ID11)Referencestest01 (ID1) on Update Cascade;Updatetest01SetId1=3 whereId1=2;

The data in the TEST02 table is also modified accordingly

Cascading operations include Cascade/set null/set default, followed by operation on delete/on update

Where cascade is the same modification; Set NULL is the TEST02 table in which the corresponding data is modified to null;set default is the corresponding data modified to the defaults.

Key constraints outside the T-SQL Foundation