- 代码区:
create table Salvaging
( prj_num char(8) primary key,
prj_name varchar(50),
start_date datetime,
end_date datetime,
prj_status bit,
);
create table Stock
( mat_num char(8) primary key,
mat_name varchar(20) not null,
speci varchar(20) not null,
warehouse char(20),
amount int,
unit decimal(18,2),
total as([amount]*[unit]),
check(mat_num like '[m][0-9][0-9][0-9]'),
);
create table Out_stock
( prj_num char(8),
mat_num char(8),
amount int,
get_date datetime default getdate(), /* get_date属性的默认值为系统时间 */
department char(20),
primary key(prj_num,mat_num),
/* 主键由两个属性构成,必须作为表格完整性约束*/
foreign key(prj_num) references Salvaging(prj_num),
/* 表格完整性约束条件,prj_num是外键,被参照表是Salvaging*/
foreign key(mat_num) references Stock(mat_num),
/* 表格完整性约束条件,mat_num是外键,被参照表是Stock*/
);
结果视图
dbo.Salvaging
dbo.Out_stock
dbo.Stock