第五章讲了数据库的DML语言,从本章准备入手DDL
1. 数据需求
有些列必须有值,不能为空:
- position VARCHAR(10) NOT NULL
这个操作在创建和修改表时可用,如果不加not null,默认是可空
2. 域限制
每个列都可以加个约束,:
- CHECK (searchCondition)
sex CHAR NOT NULL CHECK (sex IN (‘M’, ‘F’))
- CREATE DOMAIN DomainName [AS] dataType
[DEFAULT defaultOption]
[CHECK (searchCondition)]
CREATE DOMAIN SexType AS CHAR
DEFAULT ‘M’
CHECK (VALUE IN (‘M’, ‘F’));
#----------------------------------
Sex SexType NOT NULL
- DROP DOMAIN DomainName[RESTRICT | CASCADE]---删除约束
3.实体完整性
- 主键非空
PRIMARY KEY(staffNo)
也可以定义替代键(除了主键之外的列)唯一(没有重复数据)
UNIQUE(telNo)
每个表格只能有一个主键但可以有多个唯一约束,一个列不能同时定义主键与唯一
4.参照完整性
FOREIGNKEY (branchNo) REFERENCES Branch
一些外键的约束：可以选择在更新还是删除时触发
• CASCADE: Delete row from parent and automatically
delete matching rows in child, and so on in cascading
manner.
• SET NULL: Delete row from parent and set FK column(s)
in child to NULL. Only valid if FK columns are NOT NULL.
• SET DEFAULT: Delete row from parent and set each
component of FK in child to specified default. Only valid if
DEFAULT specified for FK columns.
• NO ACTION: Reject delete from parent. This is the
default setting if ON DELETE rule is omitted.
例子：
FOREIGN KEY (staffNo) REFERENCES Staff
ON DELETE SET NULL
#如何希望在删除时触发，ON DELETE可以改成ON UPDATE
5.企业完整性
iso允许企业在创建和修改表时自定义一些约束
- CREATE ASSERTION AssertionName CHECK (searchCondition)
例如
to define that prevents a member
of staff from managing more than 100
properties at the same time.
CREATE ASSERTION StaffNotHandlingTooMuch
CHECK (NOT EXISTS (SELECT staffNo FROM PropertyForRent GROUP BY staffNo HAVING COUNT(*) >100))
本章节只是针对完整性增强特征方面叙述，下次我们将从 数据定义开始继续讲解
网友评论