Learning Test: FOREIGN key

--======= test: FOREIGN key =========-----
--=======auther:su=========-----
--=======time:2015-06-26===-----

--=========================-----
--******* Theory Knowledge **********-----
--=========================-----
--Add foreign key when adding table: Foreign key (column name) references Reference foreign key table (column name) on DELETE no action/on UPDATE no ACTION/* can also pick Cascade,set null,set default*/
--already exists table plus foreign key: ALTER TABLE name ADD constraint foreign KEY constraint name foreign key (column name) references Reference foreign key table (column name [can be omitted]) on DELETE no ACTION on UPDATE no A Ction/* can also be connected to Cascade,set Null,set default*/
--no Action: No action is taken, when data in the primary key table is deleted, if there is data in the foreign key table, the delete operation in the primary key table fails, which is the default option.
--cascade: Represents cascading operations, when data in the primary key table is deleted, the corresponding data in the foreign key table is automatically deleted
--set NULL: Performs a cascade operation and sets all data in the foreign key table to a null value.
--set default: Similar to set NULL, the data in the Foreign key table is set to the default value.
--primary key table and foreign key table: T1.id is the foreign key of table T2, and is the primary key of table T1, then for t1.id, table T1 is the primary key table, table T2 is the foreign key table
--Delete foreign key ALTER TABLE name DROP constraint foreign key name
--Do NOT check constraint ALTER TABLE name NOCHECK constraint foreign key name
--CHECK Constraint ALTER TABLE name CHECK constraint foreign key name

--=========================-----
--******* Test Code **********-----
--=========================-----

--Test One: Add and delete records
--new table and foreign key
CREATE TABLE T1 (ID BIGINT NOT NULL default (0), Name nvarchar (+), PRIMARY KEY (ID))
CREATE TABLE T2 (ID BIGINT NOT NULL default (0), Class int,primary key (ID), foreign key (ID) references T1 (ID))
--=========================-----
--== inserting primary key table data
INSERT into T1
SELECT 1, ' CS1 ' UNION all
SELECT 2, ' CS2 '
--== inserting foreign key table data
INSERT into T2
SELECT 1,111 UNION
SELECT 3,333
--The message 2627:insert statement conflicts with the FOREIGN KEY constraint "fk__t2__id__32d74f96". The conflict occurs in the database "Vobao_lab", table "dbo." T1 ", column ' ID '.
--reason: When you insert a foreign key table, you find that T1 does not have a id=3 column, you can change the script to the following:
INSERT into T2
SELECT 1,111 UNION
SELECT 2,222
--Successful execution
--== Deleting data
DELETE from T1 WHERE id=1
--The message 547:delete statement conflicts with the REFERENCE constraint "fk__t2__id__32d74f96". The conflict occurs in the database "Vobao_lab", table "dbo." T2 ", column ' ID '.
--reason: T2 has corresponding record, T1 the record can not be deleted, will T2 corresponding record deleted T1 record will be able to operate
DELETE from T2 WHERE id=1
DELETE from T1 WHERE id=1
--Conclusion One: The Foreign key table has the record primary key table must have, the primary key table has the record foreign key table not necessarily, the foreign key table has the record primary key table corresponding record cannot delete (primary key table cannot delete: Message 3726)

--Test Two: CASCADE
--Create a table
CREATE TABLE T1 (ID BIGINT NOT NULL default (0), Name nvarchar (+), PRIMARY KEY (ID))
CREATE TABLE T2 (ID BIGINT NOT NULL default (0), Class int,primary KEY (ID))
ALTER TABLE T2 ADD CONSTRAINT t2_id FOREIGN KEY (ID) references T1 on DELETE CASCADE
--== inserting primary key table data
INSERT into T1
SELECT 1, ' CS1 ' UNION all
SELECT 2, ' CS2 '
--== inserting foreign key table data
INSERT into T2
SELECT 1,111 UNION
SELECT 2,222
--== Deleting data
DELETE from T1 WHERE id=1
--execution succeeds while deleting records from Id=1 in T1 and T2
DELETE from T2 WHERE id=2
--Execute successfully, delete id=2 record in T2
--Conclusion Two: CASCADE: When the records in the primary key table are deleted, the corresponding records in the foreign key table are automatically deleted; When you delete a foreign key table record, the primary key table record is not deleted

--Test Three: SET Null/default
--Create a table
CREATE TABLE T1 (ID BIGINT NOT NULL default (0), Name nvarchar (+), PRIMARY KEY (ID))
CREATE TABLE T2 (ID BIGINT NOT NULL default (0), Class int,primary KEY (ID))
ALTER TABLE T2 ADD CONSTRAINT t2_id FOREIGN KEY (ID) references T1 on DELETE SET DEFAULT
--== inserting primary key table data
INSERT into T1
SELECT 1, ' CS1 ' UNION all
SELECT 2, ' CS2 ' UNION all
SELECT 3, ' CS3 '
--== inserting foreign key table data
INSERT into T2
SELECT 1,111 UNION
SELECT 2,222
--== Deleting data
DELETE from T1 WHERE id=1
--The message 547:delete statement conflicts with the FOREIGN KEY constraint "t2_id". The conflict occurs in the database "Vobao_lab", table "dbo." T1 ", column ' ID '.
--reason: Delete id=1 records in T1 and issue an error when updating records id=1 in T2 to Id=0 because T1 does not exist Id=0 records

--== inserting primary key table data
INSERT into T1
SELECT 0, ' CS0 '
--== Deleting data
DELETE from T1 WHERE id=1
--Execute successfully, delete id=1 records in T1 and update id=1 records in T2 to Id=0
DELETE from T2 WHERE id=2
--Execute successfully, delete id=2 record in T2
--Conclusion Three: SET Null/default: When a record in the primary key table is deleted, the corresponding record in the foreign key table is automatically updated to NULL or default (execution fails if primary key value of primary key table does not allow null or no default value); When you delete a foreign key table record, Primary key table records are not deleted

--Do NOT check constraint ALTER TABLE name NOCHECK constraint foreign key name
ALTER TABLE T2 nocheck constraint t2_id
DELETE from T1 WHERE id=0
--CHECK Constraint ALTER TABLE name CHECK constraint foreign key name
ALTER TABLE T2 CHECK constraint t2_id
INSERT into T1 SELECT 0, ' CS0 '
DELETE from T1 WHERE id=0
--Delete foreign key ALTER TABLE name DROP constraint foreign key name
ALTER TABLE T2 DROP CONSTRAINT t2_id

Learning Test: FOREIGN key