SQL statement creation and primary foreign key delete operations for the database

1 CREATE TABLE Usertype 2 3 (  4 5 Id int  primary key identity (),  Name nvarchar (+) NOT NULL 6 7) Go

1 CREATE TABLE UserInfo2 3  (  4 5 Id int primary key identity (),  loginpwd varchar () NOT NULL,  Loginnam e varchar (1) NOT NULL,  Name varchar (+) NOT NULL,  Gender bit is not null default check (gender=1 or gender=0), 
   email varchar (+) NOT NULL,  Adress nvarchar (a) not NULL,  Phone int. NOT NULL,  blogdespt nvarchar (a) de Fault ' This man is lazy, nothing left ',  usertypeid int foreign  key  references usertype (ID) 6 7) 8 9 Go

1 CREATE TABLE Blogtype 2 3 (  4 5 Id int primary key identity (),  Name nvarchar () NOT NULL 6 7) 8 9  Go

1 CREATE TABLE Blog2 3  (4 5  Id int primary key identity (),  Title Nvarchar (+) NOT NULL,  Summary NVARC Har (max),  essay text,  blogdata datetime NOT NULL default GETDATE (),  Clicks int. NOT NULL default 0,  Blo Gtypeid int foreign key references Blogtype (ID),  UserId int foreign key references UserInfo (ID) 6 7  ) go

1 CREATE TABLE Comment2 3  (  4 5 Id int primary key identity (),  commenttext text NOT NULL,  Commentta ry int NOT null default 0,  commentdate datetime NOT NULL default GETDATE (),  commentblog int foreign key Referenc Es Blog (Id) 6 7) Go

1 insert into usertype (name) VALUES (' admin ') insert into usertype (name) values (' Normal user ') 2  3 insert INTO UserInfo (loginpwd , Loginname,name,gender,email,adress,phone,blogdespt,usertypeid) VALUES (' 123 ', ' Admin ', ' then Moon ', 0, ' [Email protected] ', ' Beijing-Haidian district ', ' 13811389272 ', ' I am the administrator, welcome to my personal blog ', 1) 4  5 insert INTO Blogtype (Name) VALUES (' Sports ') insert into Blogtype (N AME) VALUES (' finance ') insert into Blogtype (name) VALUES (' house ') insert into Blogtype (name) VALUES (' entertainment ') insert into Blogtype (Nam e) VALUES (' Computer technology ') insert into Blogtype (Name) VALUES (' other ') 6  7 insert INTO Blog (Title,summary,essay,blogtypeid, Clicks) VALUES (' This my first blog post ', ' Open blog ', ' Hello everyone, I just opened a blog, hoping to make more friends here! ', 6,0) 8  9  insert INTO Comment (Commenttext,commenttary,commentblog) VALUES (' good ', 0,1) go12 insert INTO Com ment (Commenttext,commenttary,commentblog) VALUES (' Very good! ', 0,1) go

Table deletion problem with primary foreign KEY constraint relationship in SQL Server Bobo the primary key in the Foreign key table client exists in Orderforcard. When a user buys a card, a record is added to the client table, and when the transaction is revoked, the record in the client is deleted and the record in the Orderforcard table is deleted. You can use the following method.

The first kind (this method is not good): 1, disable the constraint alter Tablexxxnocheck constraint all 2, delete the data delete fromxxx3, restore the constraint alter TABLEXXX Check constraint all the second method: the data of the foreign key table is deleted automatically when the data in the table containing the primary key is deleted. ALTER TABLE dbo. Orderforcard add constraint fk_orderfor_reference_client foreign key (ClientId) references dbo. Client (ClientId) on DELETE cascade so that when the data is deleted without the data in the two tables are deleted in turn and delete the records in the main table directly, the record containing the foreign key is automatically deleted.

SQL Cascade Delete-delete primary table delete from table simultaneously delete table with primary foreign key relationship CREATE TABLE A (ID varchar () primary key, password varchar () NOT NULL)

CREATE TABLE B (id int identity (primary) key, name varchar () NOT NULL, UserID varchar, foreign KEY (userid) re Ferences A (ID) on DELETE cascade) Table B creates a master code ID for the external code userid corresponding to a, declaring a cascade delete test data: Insert a VALUES (' One ', ' AAA ') insert a values (' 23 ', ' AAA ') Insert B values (' Da ', ' one ') Insert B values (' Das ', ' one ') Insert B values (' WW ', ' 23 ') Delete the data of ID ' 11 ' in table A and find that the UserID in B is "11 "is also automatically deleted by the database, which is cascading delete delete a where id= ' 11 '

SQL statement creation and primary foreign key delete operations for the database