I have never paid attention to writing SQL statements before.
/* Create a database DL */
Use master
If exists (select 1 from Master .. sysdatabases where name = 'dl ')
DROP DATABASE DL
Go
Use master
Go
Create Database DL
On
(
Name = dl_data,
Filename = 'd:/program files/Microsoft SQL Server/MSSQL/data/dl_data.mdf ',
Size = 4,
Maxsize = 10,
Filegrowth = 1
)
Log On
(
Name = dl_log,
Filename = 'd:/program files/Microsoft SQL Server/MSSQL/data/dl_log.ldf ',
Size = 4,
Maxsize = 10,
Filegrowth = 1
)
Go
/* Create a Cortana */
Use DL
/* If exists (select 1 from Master .. sysdatabases where object_id ('dl .. student ') is not null )*/
If object_id ('dl .. subjects ') is not null
Drop table subjects
Create Table subjects
(
Subid int primary key,/* Primary Key */
Subname nvarchar (50) not null
)
Go
/* Create a student information table */
Use DL
If object_id ('dl .. student ') is not null
Drop table student
Create Table student
(
Stuid int primary key,/* Primary Key */
Stuname nvarchar (50) not null,
Stugrade int not null
)
/* Create a single subject */
Use DL
If object_id ('dl .. subobject') is not null
Drop table subject
Create Table subject
(
Subid int not null,
Stuid int not null,
Stugrade int not null
)
/* Set limits */
Begin transaction
Go
Alter table subject
Add constraint fk_stuid foreign key (stuid)
References student (stuid)
On Delete cascade on update cascade/* Delete it together */
Alter table subject
Add constraint fk_subid foreign key (subid)
References subjects (subid)
On Delete cascade on update Cascade
Go
/* Insert data */
Use DL
Declare @ inum int
Set @ inum = 1
Truncate table subject
While @ inum <10
Begin
Insert into subject (subid, stuid, stugrade)
Values (1, @ inum, 50 + @ inum * 3/2)
Set @ inum = @ inum + 1
End
Truncate table subjects
Set @ inum = 1
While @ inum <10
Begin
Insert into subjects (subid, subname)
Values (@ inum, @ inum + 1)
Set @ inum = @ inum + 1
End
There is a problem with writing the constraint. After that, the SQL Enterprise Manager may be stuck for a long time. Check the cause again.