1、数据库实验三实验三实验名称:表的完整性约束(2课时)一、实验目的理解并掌握关系的完整性约束,熟练使用T-SQL语句为每张表添加表约束。二、实验环境采用Client/Server模式,学生为客户端,是MS SQL SERVER 2000的中文客户端。登录用户名是:学号;密码为:* 。用户名和密码以任课老师给出为准。三、实验内容与步骤1、 通过企业管理器修改表约束,并生成脚本。2、 通过T-SQL语句修改表约束。分别为每张表建立主键约束。为相关表建立外键约束。在表employee加入CHECK约束:输入的员工编号必须以E开头的5位数编号,性别只能为M/F。为销售主表sales中的发票编号字段建立U
2、NIQUE约束。四、 实验结果1. 通过企业管理器修改表约束,并生成脚本。为相关表建立外键约束。通过企业管理器为sales表建立外码,字段为:cust_id,被参照表为customer在表employee加入CHECK约束:输入的员工编号必须以E开头的5位数编号,性别只能为M/F。通过企业管理器在表employee加入CHECK约束:输入的员工编号必须以E开头的5位数编号,性别只能为M/F。首先建立check约束:CK_employee : (emp_no between E0000 and E9999)CK_sex(sex=M or sex=F)实验图如下:测试CK_employee约束测试
3、语句:insert into employee (emp_no,emp_name,sex,dept,title,date_hired,salary) values(D0003,张三,M,业务,职员,1900/01/21,43200)测试CK_sex约束测试语句:insert into employee (emp_no,emp_name,sex,dept,title,date_hired,salary) values(E0003,张三,D,业务,职员,1900/01/21,43200)为销售主表sales中的发票编号字段建立UNIQUE约束。创建unique约束,如下图:测试unique约束:测
4、试语句:insert into sales(order_no,cust_id,sale_id,tot_amt,order_date,ship_date,invoice_no)values(1,00001,00001,25343,2012-11-11,2012-11-11,000001);insert into sales(order_no,cust_id,sale_id,tot_amt,order_date,ship_date,invoice_no)values(2,00002,00001,25343,2012-11-11,2012-11-11,000001);脚本如下:USE testGO/
5、* Object: Table dbo.customer Script Date: 2012/11/28 15:00:29 */SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOCREATE TABLE dbo.customer( cust_id nchar(5) NOT NULL, cust_name nchar(20) NOT NULL, addr nchar(40) NOT NULL, tel_no nchar(10) NOT NULL, zip nchar(6) NULL, CONSTRAINT PK_customer PRIMARY KEY C
6、LUSTERED ( cust_id ASC)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON PRIMARY) ON PRIMARYGO/* Object: Table dbo.employee Script Date: 2012/11/28 15:00:29 */SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOCREATE TABLE dbo.employ
7、ee( emp_no nchar(5) NOT NULL, emp_name nchar(10) NOT NULL, sex nchar(1) NOT NULL, dept nchar(4) NOT NULL, title nchar(6) NOT NULL, date_hired datetime NOT NULL, birthday datetime NULL, salary int NOT NULL, addr nchar(50) NULL, Mod_date datetime NOT NULL, CONSTRAINT PK_employee PRIMARY KEY CLUSTERED
8、( emp_no ASC)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON PRIMARY) ON PRIMARYGO/* Object: Table dbo.product Script Date: 2012/11/28 15:00:29 */SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOCREATE TABLE dbo.product( prod_id
9、nchar(5) NOT NULL, prod_name nchar(20) NOT NULL, CONSTRAINT PK_product PRIMARY KEY CLUSTERED ( prod_id ASC)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON PRIMARY) ON PRIMARYGO/* Object: Table dbo.sale_item Script Date: 2012/
10、11/28 15:00:29 */SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOCREATE TABLE dbo.sale_item( order_no int NOT NULL, prod_id nchar(5) NOT NULL, qty int NOT NULL, unit_price numeric(9, 2) NOT NULL, order_date datetime NULL, CONSTRAINT PK_sale_item PRIMARY KEY CLUSTERED ( order_no ASC, prod_id ASC)WITH (P
11、AD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON PRIMARY) ON PRIMARYGO/* Object: Table dbo.sales Script Date: 2012/11/28 15:00:29 */SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOCREATE TABLE dbo.sales( order_no int NOT NULL, cust_id n
12、char(5) NOT NULL, sale_id nchar(5) NOT NULL, tot_amt numeric(9, 2) NOT NULL, order_date datetime NOT NULL, ship_date datetime NOT NULL, invoice_no nchar(10) NOT NULL, CONSTRAINT PK_invoice_no PRIMARY KEY CLUSTERED ( order_no ASC)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = O
13、FF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON PRIMARY) ON PRIMARYGOALTER TABLE dbo.employee ADD CONSTRAINT DF_employee_Mod_date DEFAULT (getdate() FOR Mod_dateGOALTER TABLE dbo.sales WITH CHECK ADD CONSTRAINT FK_sales_customer FOREIGN KEY(cust_id)REFERENCES dbo.customer (cust_id)GOALTER TABLE
14、dbo.sales CHECK CONSTRAINT FK_sales_customerGOALTER TABLE dbo.employee WITH CHECK ADD CONSTRAINT CK_employee CHECK (emp_no=E0000 AND emp_no=E9999)GOALTER TABLE dbo.employee CHECK CONSTRAINT CK_employeeGOALTER TABLE dbo.employee WITH CHECK ADD CONSTRAINT CK_sex CHECK (sex=M OR sex=F)GOALTER TABLE dbo
15、.employee CHECK CONSTRAINT CK_sexGO2.通过T-SQL语句修改表约束。分别为每张表建立主键约束。为product表建立主键约束:use testGoalter table productadd primary key(prod_id)GOuse testGoalter table sale_itemadd primary key(order_no, prod_id)GOuse testGoalter table salesadd primary key(order_no)GOuse testGoalter table customeradd primary k
16、ey(cust_id)GOuse testGoalter table employeeadd primary key(emp_no)GO为相关表建立外键约束。为sales表建立外键约束语句为:use testGoalter table salesadd foreign key (cust_id) REFERENCES customer(cust_id)GO在表employee加入CHECK约束:输入的员工编号必须以E开头的5位数编号,性别只能为M/F。语句为:use testGoalter table employeeadd constraint CK_employee CHECK( emp_no between E0000 and E9999)GOuse testGoalter table employeeadd constraint CK_sex CHECK( sex=M or sex=F)GO实验结果:为销售主表sales中的发票编号字段建立UNIQUE约束。语句为:use testGoalter table salesadd constraint IX_invoice_no UNIQUE( invoice_no)实验截图:实验总结:通过对通过企业管理器和用T-SQL创建外码,创建check 约束,以及unique约束有了进一步的了解和使用。
copyright@ 2008-2022 冰豆网网站版权所有
经营许可证编号:鄂ICP备2022015515号-1