create table 学生(学号 char(5) primary key,姓名 varchar(8),专业 varchar(20),入学日期 smalldatetime)
create table 收费 (学年 char(4),学号 char(5) references 学生(学号),学费 numeric(6,2),书费 numeric(6,2),总金额 as 学费+书费 primary key(学年,学号) )
create table 收费 (学年 char(4),学号 char(5) references 学生(学号),学费 numeric(6,2),书费 numeric(6,2),总金额 as 学费+书费 primary key(学年,学号) )
create table 收费 (学年 char(4),学号 char(5) references 学生(学号),学费 numeric(6,2),书费 numeric(6,2),总金额 as 学费+书费 primary key(学年,学号) )
drop table [dbo].[学生]
GOif exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[收费]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[收费]
GO
--建立基本表
CREATE TABLE [学生] (
[学号] [char] (5)NOT NULL ,
[姓名] [Nvarchar] (8) NULL ,
[专业] [Nvarchar] (20) NULL ,
[入学日期] [smalldatetime] NULL
) ON [PRIMARY]
GOCREATE TABLE [收费] (
[学年] [char] (4) NOT NULL ,
[学号] [char] (5) NOT NULL ,
[学费] [Nnumeric](6, 2) NULL ,
[书费] [numeric](6, 2) NULL ,
[总金额] AS ([学费] + [书费])
) ON [PRIMARY]
GO
--增加主键约束
ALTER TABLE [学生] WITH NOCHECK ADD
PRIMARY KEY CLUSTERED
(
[学号]
) ON [PRIMARY]
GOALTER TABLE [收费] WITH NOCHECK ADD
PRIMARY KEY CLUSTERED
(
[学年],
[学号]
) ON [PRIMARY]
GO
--增加外键约束并且级连更新/删除
ALTER TABLE [收费] ADD
CONSTRAINT [FK__收费学号] FOREIGN KEY
(
[学号]
) REFERENCES [dbo].[学生] (
[学号]
) ON DELETE CASCADE ON UPDATE CASCADE
GO