Simply say the foreign key

Foreign key use everyone is not unfamiliar, is we use to maintain data referential integrity role ~ Spicy Today I'll share some of the restrictions on foreign keys.

1. A foreign key refers to a primary key that requires another table, or a candidate key. (This is better understood, do not write code ╮ (╯_╰) ╭)

2, the foreign key is created after the index is not automatically created, this is a developer to consider whether the relevant index on the external key can be obtained to improve the efficiency of the query

3, by default, if you insert a non-existent foreign key value in the reference table or delete a referenced data in the main table, the database will error. Like we make a chestnut.

CREATE TABLEpk_table (IDINT IDENTITY(1,1)PRIMARY KEY, NameNVARCHAR( -))INSERT  intodbo. Pk_table (Name)VALUES('PRIMARY KEY 1'),('PRIMARY KEY 2');CREATE TABLEFk_table1 (IDINT PRIMARY KEY, PKID1INT CONSTRAINTFk_1FOREIGN KEY REFERENCESPk_table (ID) on DELETENO ACTION, NameNVARCHAR( -)UNIQUE)INSERT  intodbo. Fk_table1 (ID,PKID1, Name)VALUES(1,1N'FOREIGN key 1'),(2,2N'FOREIGN Key 2' );CREATE TABLEfk_table1_1 (IDINT IDENTITY(1,1)PRIMARY KEY, FKID1INT CONSTRAINTFk_1_1FOREIGN KEY REFERENCESFk_table1 (ID) on DELETENO ACTION, NameNVARCHAR( -))INSERT  intodbo. Fk_table1_1 (FKID1, Name)VALUES(1N'foreign Key 1_1'),(1N'foreign Key 1_2'),(2N'foreign Key 2_1'),(2N'foreign Key 2_2');

And then delete a

DELETE  from WHERE = 2 or DELETE  from WHERE = 2

Database directly throw the wrong ~ Normal, people are such a paper definition.

But the behavior is more than NO action one, there are cascade (cascade Delete), set null (set empty) and set default (set defaults) 3 kinds of behavior is a literal better understanding.

Set NULL and set DEFAULT it's better to understand than to say ~

If you define a cascade, you must be careful. Or define just the table just change fk_table1 to CASCADE

CREATE TABLEpk_table (IDINT IDENTITY(1,1)PRIMARY KEY, NameNVARCHAR( -))INSERT  intodbo. Pk_table (Name)VALUES('PRIMARY KEY 1'),('PRIMARY KEY 2');CREATE TABLEFk_table1 (IDINT PRIMARY KEY, PKID1INT CONSTRAINTFk_1FOREIGN KEY REFERENCESPk_table (ID) on DELETE CASCADE, NameNVARCHAR( -)UNIQUE)INSERT  intodbo. Fk_table1 (ID,PKID1, Name)VALUES(1,1N'FOREIGN key 1'),(2,2N'FOREIGN Key 2' );CREATE TABLEfk_table1_1 (IDINT IDENTITY(1,1)PRIMARY KEY, FKID1INT CONSTRAINTFk_1_1FOREIGN KEY REFERENCESFk_table1 (ID) on DELETENO ACTION, NameNVARCHAR( -))

And then a Delete ~

DELETE from dbo. pk_table WHERE ID = 2

The DELETE statement conflicts with the REFERENCE constraint "Fk_1". The conflict occurred in the database "Test", table "dbo." Fk_table1 ", column ' PKID1 '.

In fact, because the Fk_table1 line can not be deleted caused by ~ So, if the definition of Casade to pay attention to the use of multiple nested

Simply say the foreign key