05 cascading property in foreign key (cascading referential integraty constraint)

As mentioned above, the role of foreign key constraints, the Genderid in the Tblperson table is a foreign key, and the ID in Tblgender is the primary key. By setting a FOREIGN key constraint, you can limit the value of Genderid in Tblperson to the range of IDs in the Tblgender table. The data in Tblgender as shown in the following table:

Id Gender 1     Male2    Female3    Unknown

Then the value of the Genderid column in the Tblperson table can only be 1, 2, 3

Id   Name     Email            genderid Address1    John    [email protected]    1         America2    boria    [email protected]    2North        America3     Judy    [email protected]      2        Japan4    July    [ Email protected]    3        China

But now our question is, what if we delete the data with ID 1 in the Tblgender table? At this point, the first row in the Tblperson table, John, has no gender that can be quoted. In fact, at this point SQL Server will not allow us to delete the data in Tblgender. If we do Delete from tblgender where Id = 1 we get the following error:

 Msg 547 , Level 16 , State 0 , line 2  the DELETE statement conflicted With the REFERENCE constraint   " tblperson _GENDERID_FK  . The conflict occurred in  database  sample  , table "  dbo.tblperson  , column "  genderid   "  

At this point we open the foreign key in the Tblperson table by using the graphical tool TBLPERSON_GENDERID_FK see the Delete rule under Insert and update (INSERT and update specific) with 4 options to choose from, They were 1) No Action 2) Cascade 3) Set Null 4) Set Default

Here we describe in detail the meanings of these four options.

1) No action: The Foreign key table does not do anything when you delete a record in the primary key table table. This is also the default option when we create a primary foreign key constraint, which causes an exception to occur if the primary key data is deleted.

2) Cascade: When deleting records from the primary key table, delete the corresponding data from the foreign key table in the Reference primary key table

3) Set NULL: When you delete a record in the primary key table, the corresponding data value for the foreign key table in the reference primary key table is null

4) Set default: When deleting records from the primary key table, set the value of the corresponding data for the foreign key table in the Reference primary key table

05 cascading property in foreign key (cascading referential integraty constraint)