数据库技术与应用--SQL Server2005 第8章 数据完整性
发布时间:2011-10-20 22:58:53
发布时间:2011-10-20 22:58:53
第8章 数据完整性
【例8-1】 创建雇用日期规则 hire_date_rule。
CREATE RULE hire_date_rule
AS @hire_date>='1980-01-01' and @hire_date<=getdate()
【例8-2】 创建性别规则sex_rule。
CREATE RULE sex_rule
AS @sex in ('男','女')
【例8-3】 创建评分规则grade_rule。
CREATE RULE grade_rule
AS @value between 1 and 100
【例8-4】 创建字符规则my_character_rule。
Create rule my_character_rule
AS @value like '[a-f]%[0-9]'
【例8-5】 查看规则hire_date_rule的文本信息。
EXECUTE sp_helptext hire_date_rule
【例8-6】 将例8-1创建的规则hire_date_rule绑定到employee表的hire_date列上。
EXEC sp_bindrule hire_date_rule,'employee.hire_date'
【例8-7】 定义用户定义数据类型pat_char,将例8-4创建的规则my_character_rule绑定到pat_var上。
EXEC sp_addtype pat_char,'varchar(10)','NOT NULL'
GO
EXEC sp_bindrule my_character_rule,pat_char,'futureonly'
【例8-8】 绑定例8-2创建的规则sex_rule 到employee 表的字段sex。
EXEC sp_bindrule sex_rule,'employee.sex'
【例8-9】 解除例8-6和例8-7绑定在employee表的hire_date列和用户定义数据类型pat_char上的规则。
EXEC sp_unbindrule 'employee.hire_date'
【例8-10】 删除例8-1和8-2中创建的规则。
DROP RULE sex_rule,hire_date_rule
【例8-11】 创建生日默认值birthday_defa。
CREATE DEFAULT birthday_defa
AS '1978-1-1'
【例8-12】 创建当前日期默认值today_defa。
CREATE DEFAULT today_defa
AS getdate()
【例8-13】 查看默认值today_defa。
EXEC sp_helptext today_defa
运行结果如图8-2所示。
【例8-14】 绑定默认值today_defa 到employee表的hire_date列上。
EXEC sp_bindefault today_defa,'employee.hire_date'
【例8-15】 解除默认值today_defa与表employee 的hire_date 列的绑定。
EXEC sp_unbindefault 'employee.hire_date'
【例8-16】 删除生日默认值birthday_defa。
DROP DEFAULT birthday_defa
【例8-17】 在Sales数据库中创建customer表,并声明主键约束。
CREATE TABLE Sales.dbo.customer
( customer_id bigint NOT NULL
IDENTITY(0,1) PRIMARY KEY,
customer_name varchar(50) NOT NULL,
linkman_name char(8),
address varchar(50),
telephone char(12) NOT NULL
)
customer_id char(5)
CONSTRAINT PK_customer PRIMARY KEY NONCLUSTERED
【例8-18】 创建一个产品信息表goods1,将产品编号goods_id列声明为主键。
CREATE TABLE goods1
( goods_id char(6) NOT NULL,
goods_name varchar(50) NOT NULL,
classification_id char(6) NOT NULL,
unit_price money NOT NULL,
stock_quantity float NOT NULL,
order_quantity float NULL
CONSTRAINT pk_p_id PRIMARY KEY (goods_id)
)ON [PRIMARY]
【例8-19】 根据商品销售的时间和商品类别来确定销售的商品的数量。
CREATE TABLE g_order
( good_type int,
order_time datetime,
order_num int,
CONSTRAINT g_o_key PRIMARY KEY (good_type,order_time)
)
【例8-20】 创建一个订货表sell_order1,与例8-18创建的产品表goods1相关联。
CREATE TABLE sell_order1
( order_id1 char(6) NOT NULL,
goods_id char(6) NOT NULL,
employee_id char(4) NOT NULL,
customer_id char(4)NOT NULL,
transporter_id char(4)NOT NULL,
order_num float NULL,
discount float NULL,
order_date datetime NOT NULL,
send_date datetime NULL,
arrival_date datetime NULL,
cost money NULL,
CONSTRAINT pk_order_id PRIMARY KEY (order_id1),
FOREIGN KEY (goods_id)REFERENCES goods1(goods_id)
)
【例8-21】 创建表sell_order2,并为goods_id、employee_id、custom_id 列定义外键约束。
CREATE TABLE sell_order2
( order_id1 char(6)
PRIMARY KEY,
goods_id char(6)NOT NULL
CONSTRAINT FK_goods_id
FOREIGN KEY (goods_id)REFERENCES Goods1(goods_id)
ON DELETE NO ACTION
ON UPDATE CASCADE,
employee_id char(4) NOT NULL
FOREIGN KEY (employee_id) REFERENCES employee(employee_id)
ON UPDATE CASCADE,
customer_id char(4) NOT NULL,
transporter_id char(4) NOT NULL,
order_num float,
discount float,
order_date datetime NOT NULL,
send_date datetime,
arrival_date datetime,
cost money,
CONSTRAINT FK_customer_id
FOREIGN KEY (customer_id) REFERENCES customer(customer_id)
)
【例8-22】 创建goods2表,使goods_name具有唯一性约束。
CREATE TABLE goods2
( goods_id char(6) NOT NULL
PRIMARY KEY,
goods_name varchar(50) NOT NULL
CONSTRAINT u_goods_name UNIQUE NONCLUSTERED,
classification_id char(6) NOT NULL,
unit_price money NOT NULL,
stock_quantity float NOT NULL,
order_quantity float
)
【例8-23】 定义一个员工信息表employees,其中员工的身份证号emp_cardid列具有唯一性。
CREATE TABLE employees
( emp_id char(8),
emp_name char(10),
emp_cardid char(18),
CONSTRAINT pk_emp_id PRIMARY KEY (emp_id),
CONSTRAINT uk_emp_cardid UNIQUE (emp_cardid)
)
【例8-24】 更改表employee2以添加未验证检查约束。
ALTER TABLE employee2
WITH NOCHECK
ADD CONSTRAINT CK_Age
CHECK (DATEDIFF(year,Birth_Date,Hire_Date)>18)
【例8-25】 创建一个订货表orders,保证各订单的订货量必须不小于10。
CREATE TABLE orders
( order_id char(8),
p_id char(8),
p_name char(10),
quantity smallint
CONSTRAINT chk_quantity CHECK (quantity>=10),
CONSTRAINT pk_orders_id PRIMARY KEY (order_id)
)
【例8-26】 创建transporters表,并定义检查约束。
CREATE TABLE transporters
( transporter_id char(4) NOT NULL,
transport_name varchar(50),
linkman_name char(8),
address varchar(50),
telephone char(12) NOT NULL
CHECK(telephone LIKE
'0[1-9][0-9][0-9]-[1-9][0-9][0-9][0-9][0-9][0-9][0-9]'
OR telephone LIKE
'0[1-9][0-9]-[1-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]')
)
【例8-27】 在Sales数据库中,为员工表employee的sex列添加默认约束,默认值是“男”。
ALTER TABLE employee
ADD CONSTRAINT sex_default DEFAULT '男' FOR sex
【例8-28】 更改表employee为hire_date列定义默认约束。
ALTER TABLE employee
ADD CONSTRAINT hire_date_df DEFAULT (getdate()) FOR hire_date
【例8-29】 添加具有默认值的可为空的列。
ALTER TABLE employee
ADD hire_date datetime
DEFAULT (getdate()) WITH VALUES
【例8-30】 使用默认约束。
--创建表purchase_order
CREATE TABLE purchase_order
( order_id2 char(6)NOT NULL,
goods_id char(6) NOT NULL,
employee_id char(4) NOT NULL,
supplier_id char(5) NOT NULL,
transporter_id char(4),
order_num float NOT NULL,
discount float
DEFAULT (0),
order_date datetime NOT NULL
DEFAULT (GetDate()),
send_date datetime,
arrival_date datetime
)
使用DEFAULT VALUES选项为purchase_order表装载数据。
INSERT INTO purchase_order
DEFAULT VALUES
【例8-31】 为表purchase_orders定义多个约束。
CREATE TABLE purchase_orders
( order_id2 char(6) NOT NULL,
goods_id char(6) NOT NULL,
employee_id char(4) NOT NULL,
supplier_id char(5) NOT NULL,
transporter_id char(4),
order_num float NOT NULL,
discount float
CHECK (discount>=0 AND discount<=50)
DEFAULT (0),
order_date datetime NOT NULL
DEFAULT (GetDate()),
send_date datetime,
arrival_date datetime,
CONSTRAINT CK_Send_date
CHECK (send_date> order_date),
CHECK (arrival_date> send_date)
)