数据库实验三.docx

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约束有了进一步的了解和使用。