SQL SERVER Cascade Delete

There are three of tables:

Company

Address

Contact

Establish foreign key in address and contact, foreign key ID is company's ID,

Then you cannot delete company arbitrarily. But if the foreign key constraints in the Cascade Delete selected, it will be associated with the address and contact with the delete.

SQL Server Cascade Delete: Automatically delete secondary table (foreign KEY constraint) content when deleting primary table

SQL Server cascade deletes an operation that contains a primary key value that is referenced by a foreign key column in an existing row of another table. In a cascade delete, the outer key value references all rows of the deleted primary key value are also removed.

Such as:

Create DATABASE Temp

Go

Use Temp

Go

CREATE TABLE UserInfo

(

UserId int identity (primary key),

UserName varchar (20),--User name

Password varchar () NOT null--password

)

CREATE TABLE Userdetails

(

ID int identity (primary key),

Name varchar () not NULL,--real name

UserId int,

FOREIGN KEY (userid) references UserInfo (userid) on DELETE cascade

)

Insert UserInfo values (' ly ', ' Jeff ')

Insert UserInfo values (' wzq ', ' wzqwzq ')

Insert UserInfo values (' LG ', ' LGLG ')

Insert Userdetails VALUES (' John Doe ', 1)

Insert Userdetails values (' Harry ', 2)

Insert Userdetails values (' Liu Liu ', 3)

At this point: delete from UserInfo Where UserId = 1 To remove the userid=1 contents of the UserInfo table and the Userdetails table

Insert UserInfo values (' ly ', ' Jeff ')

Insert UserInfo values (' wzq ', ' wzqwzq ')

Insert UserInfo values (' LG ', ' LGLG ')

Insert Userdetails VALUES (' John Doe ', 1)

Insert Userdetails values (' Harry ', 2)

Insert Userdetails values (' Liu Liu ', 3)

At this point: delete from UserInfo Where UserId = 1 Deletes the contents of the userid=1 of the UserInfo table and Userdetails table.

SQL SERVER Cascade Delete