12完整性约束
<h1>完整性约束</h1>
<h2>数据完整性约束</h2>
<ul>
<li>
<p>数据完整性约束简介
<strong>完整性与安全性的区别：</strong>完整性的目的是防止所有不合主义、不正确的数据进入数据库。安全性的目的是控制数据库的非法存取。</p>
</li>
<li>
<p>在建表的时候就要设计好约束
<strong>注意：</strong>日后修改约束并不是一个好的办法，设计表的时候就应该考虑完整的约束。另，约束越多，性能越差。</p>
</li>
<li>5个约束，隐性约束
<ul>
<li>非空约束：</li>
<li>唯一约束：</li>
<li>主键约束：</li>
<li>检查约束：</li>
<li>主-外键约束：</li>
</ul></li>
</ul>
<h2>非空约束NK</h2>
<ul>
<li>NULL值插入会报错，并且不用指定非空键名，报错中也是有非空键名的</li>
</ul>
<pre><code>DROP TABLE MEMBER PURGE;
CREATE TABLE MEMBER (
mid NUMBER,
NAME VARCHAR2(200) NOT NULL
);
INSERT INTO MEMBER(mid,NAME) VALUES (1,'李兴华'）;
INSERT INTO MEMBER (mid,NAME) VALUES (2,NULL);
INSERT INTO MEMBER (mid) VALUES(3);</code></pre>
<h2>唯一约束（UNIQUE，简称UK）</h2>
<ul>
<li>唯一约束关键字是UNIQUE。</li>
<li>默认的报错信息不明显，可以使用CONSTRAINT 关键字指定名字。</li>
<li>所有的约束都是数据库对象，保存在数据字典中，。</li>
<li>NULL值并不会违反唯一约束。</li>
</ul>
<pre><code>DROP TABLE MEMBER PURGE ;
CREATE TABLE MEMBER (
mid NUMBER,
NAME VARCHAR2(50) NOT NULL,
email VARCHAR2(200) UNIQUE
);
SELECT * FROM MEMBER ;
INSERT INTO MEMBER (mid,name,email) VALUES(1,'李兴华','mldnqa@163.com');
INSERT INTO MEMBER (mid,NAME,email) VALUES(2,'董鸣楠','mldnqa@163.com');--报错信息不明显
--指定约束名
DROP TABLE MEMBER PURGE ;
CREATE TABLE MEMBER (
mid NUMBER,
NAME VARCHAR2(50) NOT NULL,
email VARCHAR2(200) ,
CONSTRAINT uk_email UNIQUE(email)
);
SELECT * FROM MEMBER ;
INSERT INTO MEMBER (mid,name,email) VALUES(1,'李兴华','mldnqa@163.com');
INSERT INTO MEMBER (mid,NAME,email) VALUES(2,'董鸣楠','mldnqa@163.com');
--NULL值并不违反唯一约束
INSERT INTO MEMBER (mid,name,email) VALUES(1,'李兴华',NULL);
INSERT INTO MEMBER (mid,NAME,email) VALUES(2,'董鸣楠',NULL);</code></pre>
<h2>主键约束（primary key , 简称PK）</h2>
<ul>
<li>错误信息不明显，也是需要手动指定约束名。</li>
<li>NULL值报错，与唯一报错不一样，要指定名字。</li>
<li>由此可以看出主键约束=非空约束+唯一约束</li>
<li>有复合主键，但是不建议使用重复主键</li>
<li>主键约束是唯一标记一个记录的，实体表都要有，关系表可以不要。</li>
</ul>
<pre><code>DROP TABLE MEMBER PURGE ;
CREATE TABLE MEMBER (
mid NUMBER PRIMARY KEY,
NAME VARCHAR2(50) NOT NULL,
email VARCHAR2(200) ,
CONSTRAINT uk_email UNIQUE(email)
);
INSERT INTO MEMBER (mid,NAME,email) VALUES(NULL,'李兴华','mldnqa@163.com');
INSERT INTO MEMBER (mid,NAME,email) VALUES(1,'李兴华','mldnqa@163.com');
INSERT INTO MEMBER (mid,NAME,email) VALUES(1,'李楠','mldnqa@163.com');
DROP TABLE MEMBER PURGE ;
CREATE TABLE MEMBER (
mid NUMBER ,
NAME VARCHAR2(50) NOT NULL,
email VARCHAR2(200) ,
CONSTRAINT pk_mid PRIMARY KEY(mid),
CONSTRAINT uk_email UNIQUE(email)
);</code></pre>
<h2>检查约束（check ,简称ck)</h2>
<ul>
<li>设置ck约束也是有两种方法，要手动去设置约束的名字才行。</li>
</ul>
<pre><code>DROP TABLE MEMBER PURGE ;
CREATE TABLE MEMBER (
mid NUMBER,
NAME VARCHAR2(50) NOT NULL,
age NUMBER,
sex VARCHAR2(10) CHECK (sex IN('女','男')),
email VARCHAR2(200) ,
CONSTRAINT uk_email UNIQUE(email),
CONSTRAINT pk_mid PRIMARY KEY(mid),
CONSTRAINT ck_age CHECK (age BETWEEN 0 AND 200)
);
select * FROM MEMBER;
INSERT INTO MEMBER (mid,NAME,age,sex,email) VALUES(1,'李兴华',40,'男','mldnq@163.com');
INSERT INTO MEMBER (mid,NAME,age,sex,email) VALUES(2,'李兴华',800,'男','mldnw@163.com');
INSERT INTO MEMBER (mid,NAME,age,sex,email) VALUES(3,'李兴华',80,'无','mldne@163.com');</code></pre>
<h2>主-外键约束（PRIMARY KEY，简写FK）</h2>
<h3>主-外键约束设置与使用</h3>
<ul>
<li>同样有两种写法，一种是指定约束名，一种是不指定约束名</li>
</ul>
<p><strong>注意：</strong>关联字段必须是主键或者唯一约束。
<strong>删除问题：</strong>删除父表前，需要先删除所有子表的对应数据。</p>
<pre><code>DROP TABLE MEMBER PURGE ;
DROP TABLE advice PURGE;
CREATE TABLE MEMBER (
mid NUMBER,
NAME VARCHAR2(50) NOT NULL,
CONSTRAINT pk_mid PRIMARY KEY (mid)
);
CREATE TABLE advice(
adid NUMBER,
CONTENT VARCHAR2(500) NOT NULL,
mid NUMBER ,
CONSTRAINT pk_adid PRIMARY KEY(adid)
);
SELECT * FROM MEMBER ;
SELECT * FROM advice ;
INSERT INTO MEMBER(mid,NAME) VALUES(1,'李兴华');
INSERT INTO MEMBER(mid,NAME) VALUES(2,'董鸣楠');
COMMIT;
INSERT INTO advice (adid,CONTENT,mid) VALUES(1,'应该提供内部沟通机制，设置总裁邮件',1);
INSERT INTO advice (adid,CONTENT,mid) VALUES(2,'为了使公司内部良性发展，所有的部门领导应该重新应聘上岗',1);
INSERT INTO advice (adid,CONTENT,mid) VALUES(3,'要多开展员工培训活动，让员工更加有归属感',2);
INSERT INTO advice (adid,CONTENT,mid) VALUES(4,'应该开展多元化业务，更加满足市场需求',2);
INSERT INTO advice (adid,CONTENT,mid) VALUES(5,'大力发展技术部门，为本公司设计自己的ERP系统，适应电子化信息发展要求',2);
COMMIT;
SELECT m.mid,m.name,COUNT(a.adid) FROM MEMBER m RIGHT JOIN advice a ON m.mid=a.mid GROUP BY m.mid,m.name ;
INSERT INTO advice (adid,CONTENT,mid) VALUES(6,'岗位职责透明化',99);
COMMIT;
DROP TABLE MEMBER PURGE ;
DROP TABLE advice PURGE;
CREATE TABLE MEMBER (
mid NUMBER,
NAME VARCHAR2(50) NOT NULL,
CONSTRAINT pk_mid PRIMARY KEY (mid)
);
CREATE TABLE advice(
adid NUMBER,
CONTENT VARCHAR2(500) NOT NULL,
mid NUMBER ,
CONSTRAINT pk_adid PRIMARY KEY(adid),
CONSTRAINT fk_mid FOREIGN KEY(mid) REFERENCES MEMBER(mid)
);
CREATE TABLE advice(
adid NUMBER,
CONTENT VARCHAR2(500) NOT NULL,
mid NUMBER REFERENCES MEMBER(mid),
CONSTRAINT pk_adid PRIMARY KEY(adid)
);
DELETE FROM MEMBER WHERE mid=1 ;
DELETE FROM advice WHERE mid=1 ;
DELETE FROM MEMBER WHERE mid=1 ;
COMMIT;
SELECT * FROM MEMBER ;
SELECT * FROM advice ;</code></pre>
<h3>联级删除(on delete cascade)</h3>
<ul>
<li>级联删除建立后，删除父表的记录会把子表相应的记录也删除掉</li>
</ul>
<pre><code>DROP TABLE MEMBER PURGE ;
DROP TABLE advice PURGE;
CREATE TABLE MEMBER (
mid NUMBER,
NAME VARCHAR2(50) NOT NULL,
CONSTRAINT pk_mid PRIMARY KEY (mid)
);
CREATE TABLE advice(
adid NUMBER,
CONTENT VARCHAR2(500) NOT NULL,
mid NUMBER ,
CONSTRAINT pk_adid PRIMARY KEY(adid),
CONSTRAINT fk_mid FOREIGN KEY(mid) REFERENCES MEMBER(mid) ON DELETE CASCADE
);
INSERT INTO MEMBER(mid,NAME) VALUES(1,'李兴华');
INSERT INTO MEMBER(mid,NAME) VALUES(2,'董鸣楠');
COMMIT;
INSERT INTO advice (adid,CONTENT,mid) VALUES(1,'应该提供内部沟通机制，设置总裁邮件',1);
INSERT INTO advice (adid,CONTENT,mid) VALUES(2,'为了使公司内部良性发展，所有的部门领导应该重新应聘上岗',1);
INSERT INTO advice (adid,CONTENT,mid) VALUES(3,'要多开展员工培训活动，让员工更加有归属感',2);
INSERT INTO advice (adid,CONTENT,mid) VALUES(4,'应该开展多元化业务，更加满足市场需求',2);
INSERT INTO advice (adid,CONTENT,mid) VALUES(5,'大力发展技术部门，为本公司设计自己的ERP系统，适应电子化信息发展要求',2);
COMMIT;
DELETE FROM MEMBER WHERE mid=1 ;
COMMIT;
SELECT * FROM MEMBER ;
SELECT * FROM advice ;</code></pre>
<h3>级联设置null (on delete set null)</h3>
<ul>
<li>删除父表，子表对应的数据外键设置为null。</li>
<li>开发中应该设置表为哪类型？根据实际的需要设置。</li>
<li>删除表的时候要选子表，才可以删除父表。</li>
<li>也可以强制删除父表，但是此时不能使用purge。</li>
</ul>
<pre><code>DROP TABLE MEMBER PURGE ;
DROP TABLE advice PURGE;
CREATE TABLE MEMBER (
mid NUMBER,
NAME VARCHAR2(50) NOT NULL,
CONSTRAINT pk_mid PRIMARY KEY (mid)
);
CREATE TABLE advice(
adid NUMBER,
CONTENT VARCHAR2(500) NOT NULL,
mid NUMBER ,
CONSTRAINT pk_adid PRIMARY KEY(adid),
CONSTRAINT fk_mid FOREIGN KEY(mid) REFERENCES MEMBER(mid) ON DELETE SET NULL
);
INSERT INTO MEMBER(mid,NAME) VALUES(1,'李兴华');
INSERT INTO MEMBER(mid,NAME) VALUES(2,'董鸣楠');
COMMIT;
INSERT INTO advice (adid,CONTENT,mid) VALUES(1,'应该提供内部沟通机制，设置总裁邮件',1);
INSERT INTO advice (adid,CONTENT,mid) VALUES(2,'为了使公司内部良性发展，所有的部门领导应该重新应聘上岗',1);
INSERT INTO advice (adid,CONTENT,mid) VALUES(3,'要多开展员工培训活动，让员工更加有归属感',2);
INSERT INTO advice (adid,CONTENT,mid) VALUES(4,'应该开展多元化业务，更加满足市场需求',2);
INSERT INTO advice (adid,CONTENT,mid) VALUES(5,'大力发展技术部门，为本公司设计自己的ERP系统，适应电子化信息发展要求',2);
COMMIT;
SELECT * FROM MEMBER ;
SELECT * FROM advice;
DELETE FROM MEMBER WHERE mid=1 ;
COMMIT;</code></pre>
<p><img src="https://www.showdoc.com.cn/server/api/attachment/visitfile/sign/30b109b61173405cfad65c677e4d9a6a?showdoc=.jpg" alt="" /></p>
<h3>强制删除父表cascade constraint</h3>
<ul>
<li>强制删除父表，子表的数据不改变，但是不能使用purge</li>
</ul>
<pre><code>DROP TABLE MEMBER ;
DROP TABLE MEMBER CASCADE CONSTRAINT;</code></pre>
<h2>查看约束(两个数据字典)</h2>
<ul>
<li>第一个数据字典中字母的意思。
<code>PRIMARY KEY (P)、FOREIGN KEY(R)、CHECK(C)、NOT NULL(C)、UNIQUE(Q)</code></li>
</ul>
<pre><code>SELECT constraint_name,constraint_type,table_name FROM user_constraints WHERE table_name IN('MEMBER','ADVICE');
SELECT * FROM user_cons_columns WHERE table_name IN('MEMBER','ADVICE');</code></pre>
<h2>修改约束</h2>
<h3>为表增加约束（alter语句）</h3>
<ul>
<li>一般使用<code>alter add constraint</code> 的方式。</li>
<li>非空约束的不能用以上方式，要使用<code>alter modify constraint</code>。</li>
</ul>
<p><strong>注意：</strong>为表添加约束时一定要保证列的数据满足约束条件，否则添加失败，这就是为什么设计表时就应该添加适当的约束。</p>
<pre><code>DROP TABLE MEMBER PURGE ;
CREATE TABLE MEMBER(
mid NUMBER,
NAME VARCHAR2(30),
age NUMBER
);
SELECT constraint_name,constraint_type,table_name FROM user_constraints WHERE table_name='MEMBER';
SELECT * FROM User_Cons_Columns WHERE table_name='MEMBER';
ALTER TABLE MEMBER ADD CONSTRAINT pk_mid PRIMARY KEY(mid);
ALTER TABLE MEMBER ADD CONSTRAINT ck_age CHECK(age BETWEEN 0 AND 200);
ALTER TABLE MEMBER MODIFY(NAME VARCHAR2(30) NOT NULL);</code></pre>
<h3>禁用及启用约束</h3>
<ul>
<li>约束过多影响性能，插入大量数据的时候可以临时禁用约束。</li>
<li>再次启用约束时，需要保证已存数据不违反约束条件。</li>
<li>禁用表advice 的外键，member 表的主键会报错，唯有添加cascade 关键字才可以禁用。</li>
<li>停用主键后，主键也还是存在的，与前面说的外键的关联键必须是非空唯一，不矛盾。</li>
<li>禁用约束<code>ALTER TABLE advice DISABLE CONSTRAINT pk_adid</code>。</li>
<li>启用约束<code>ALTER TABLE advice ENABLE CONSTRAINT pk_adid</code>。</li>
<li>禁用的主键是其他表的外键<code>ALTER TABLE MEMBER DISABLE CONSTRAINT pk_mid CASCADE;</code>。</li>
</ul>
<pre><code>DROP TABLE MEMBER PURGE ;
DROP TABLE advice PURGE;
CREATE TABLE MEMBER (
mid NUMBER,
NAME VARCHAR2(50) NOT NULL,
CONSTRAINT pk_mid PRIMARY KEY (mid)
);
CREATE TABLE advice(
adid NUMBER,
CONTENT VARCHAR2(500) NOT NULL,
mid NUMBER ,
CONSTRAINT pk_adid PRIMARY KEY(adid),
CONSTRAINT fk_mid FOREIGN KEY(mid) REFERENCES MEMBER(mid) ON DELETE SET NULL
);
INSERT INTO MEMBER(mid,NAME) VALUES(1,'李兴华');
INSERT INTO MEMBER(mid,NAME) VALUES(2,'董鸣楠');
COMMIT;
INSERT INTO advice (adid,CONTENT,mid) VALUES(1,'应该提供内部沟通机制，设置总裁邮件',1);
INSERT INTO advice (adid,CONTENT,mid) VALUES(2,'为了使公司内部良性发展，所有的部门领导应该重新应聘上岗',1);
INSERT INTO advice (adid,CONTENT,mid) VALUES(3,'要多开展员工培训活动，让员工更加有归属感',2);
INSERT INTO advice (adid,CONTENT,mid) VALUES(4,'应该开展多元化业务，更加满足市场需求',2);
INSERT INTO advice (adid,CONTENT,mid) VALUES(5,'大力发展技术部门，为本公司设计自己的ERP系统，适应电子化信息发展要求',2);
COMMIT;
ALTER TABLE advice DISABLE CONSTRAINT pk_adid;--禁用表 advice 主键mid
INSERT INTO advice (adid,content,mid)VALUES(1,'主键重复的数据',1);
INSERT INTO advice (adid,content,mid)VALUES(1,'主键重复的数据',1);
SELECT * FROM MEMBER ;
SELECT * FROM advice;
ALTER TABLE MEMBER DISABLE CONSTRAINT pk_mid;--禁用表advice 的外键，member 表的主键会报错
ALTER TABLE MEMBER DISABLE CONSTRAINT pk_mid CASCADE;--唯有添加cascade 关键字才可以禁用
INSERT INTO MEMBER(mid,NAME) VALUES(1,'李兴华'）;
INSERT INTO MEMBER(mid,NAME) VALUES(1,'魔乐科技'）;
COMMIT;
--启用约束之前，要处理错误的数据。
ALTER TABLE MEMBER ENABLE CONSTRAINT pk_mid;
ALTER TABLE advice ENABLE CONSTRAINT pk_adid;</code></pre>
<h3>删除约束</h3>
<ul>
<li>删除有关联的约束也是要使用<code>CASCADE</code>关键字。</li>
</ul>
<pre><code>ALTER TABLE advice DROP CONSTRAINT PK_ADID ;
ALTER TABLE MEMBER DROP CONSTRAINT PK_MID CASCADE;</code></pre>
<h2>数据库综合实</h2>
<ul>
<li>建表，创建数据，完成五个问题</li>
</ul>
<pre><code>DROP TABLE grade;
DROP TABLE sporter;
DROP TABLE item;
PURGE RECYCLEBIN ;
-- 创建数据表
CREATE TABLE sporter(
sporterid NUMBER(4),
NAME VARCHAR2(30) NOT NULL,
sex VARCHAR2(10) DEFAULT '女',
department VARCHAR2(30) NOT NULL,
CONSTRAINT pk_sporterid PRIMARY KEY(sporterid),
CONSTRAINT ck_sex CHECK(sex IN('女','男'))
);
CREATE TABLE item (
itemid VARCHAR2(4),
itemname VARCHAR2(30) NOT NULL,
LOCATION VARCHAR2(30) NOT NULL,
CONSTRAINT pk_itemid PRIMARY KEY(itemid)
);
CREATE TABLE grade(
sporterid NUMBER(4),
itemid VARCHAR2(30),
mark NUMBER(1),
CONSTRAINT fk_sporterid FOREIGN KEY(sporterid) REFERENCES sporter(sporterid) ON DELETE CASCADE,
CONSTRAINT fk_itemid FOREIGN KEY(itemid) REFERENCES item(itemid) ON DELETE CASCADE,
CONSTRAINT ck_mark CHECK(mark IN (6,4,2,0))
);
--查询全部数据表
select * FROM tab WHERE tname IN ('SPORTER','GRADE','ITEM')
--查看约束
SELECT constraint_name 约束名,constraint_type 约束类型,table_name 表名 FROM user_constraints WHERE table_name IN ('SPORTER','GRADE','ITEM') ORDER BY table_name;
--增加数据
INSERT INTO sporter(sporterid,NAME,sex,department) VALUES (1001,'李明','男','计算机系');
INSERT INTO sporter(sporterid,NAME,sex,department) VALUES (1002,'张三','男','数学系');
INSERT INTO sporter(sporterid,NAME,sex,department) VALUES (1003,'李四','男','计算机系');
INSERT INTO sporter(sporterid,NAME,sex,department) VALUES (1004,'王二','男','物理系');
INSERT INTO sporter(sporterid,NAME,sex,department) VALUES (1005,'李娜','女','心理系');
INSERT INTO sporter(sporterid,NAME,sex,department) VALUES (1006,'孙丽','女','数学系');
COMMIT;
SELECT * FROM sporter ;
INSERT INTO item(itemid,itemname,LOCATION) VALUES('x001','男子五千米','一操场');
INSERT INTO item(itemid,itemname,LOCATION) VALUES('x002','男子标枪','一操场');
INSERT INTO item(itemid,itemname,LOCATION) VALUES('x003','男子跳远','二操场');
INSERT INTO item(itemid,itemname,LOCATION) VALUES('x004','女子跳高','二操场');
INSERT INTO item(itemid,itemname,LOCATION) VALUES('x005','女子三千米','三操场');
COMMIT;
SELECT * FROM item;
INSERT INTO grade(sporterid,itemid,mark) VALUES(1001,'x001',6);
INSERT INTO grade(sporterid,itemid,mark) VALUES(1002,'x001',4);
INSERT INTO grade(sporterid,itemid,mark) VALUES(1003,'x001',2);
INSERT INTO grade(sporterid,itemid,mark) VALUES(1004,'x001',0);
INSERT INTO grade(sporterid,itemid,mark) VALUES(1001,'x003',4);
INSERT INTO grade(sporterid,itemid,mark) VALUES(1002,'x003',6);
INSERT INTO grade(sporterid,itemid,mark) VALUES(1004,'x003',2);
INSERT INTO grade(sporterid,itemid,mark) VALUES(1003,'x003',0);
INSERT INTO grade(sporterid,itemid,mark) VALUES(1005,'x004',6);
INSERT INTO grade(sporterid,itemid,mark) VALUES(1006,'x004',4);
INSERT INTO grade(sporterid,itemid,mark) VALUES(1001,'x004',2);
INSERT INTO grade(sporterid,itemid,mark) VALUES(1002,'x004',0);
INSERT INTO grade(sporterid,itemid,mark) VALUES(1003,'x002',6);
INSERT INTO grade(sporterid,itemid,mark) VALUES(1005,'x002',4);
INSERT INTO grade(sporterid,itemid,mark) VALUES(1006,'x002',2);
INSERT INTO grade(sporterid,itemid,mark) VALUES(1001,'x002',0);
COMMIT;
SELECT * FROM grade;
--数据操作（5道题）
--求出目前总积分最高的系名及其积分。
SELECT S.DEPARTMENT, SUM(G.MARK)
FROM SPORTER S
JOIN GRADE G
ON S.SPORTERID = G.SPORTERID
GROUP BY S.DEPARTMENT
HAVING SUM(G.MARK) = (SELECT MAX(SUM(G.MARK))
FROM SPORTER S
JOIN GRADE G
ON S.SPORTERID = G.SPORTERID
GROUP BY S.DEPARTMENT);
--找出在一操场进行比赛的各项目名称及其冠军的姓名。
SELECT I.ITEMNAME, S.NAME
FROM ITEM I
JOIN GRADE G
ON I.ITEMID = G.ITEMID
JOIN SPORTER S
ON G.SPORTERID = S.SPORTERID
JOIN (SELECT I.ITEMID IID, MAX(G.MARK) MAX
FROM ITEM I
JOIN GRADE G
ON I.ITEMID = G.ITEMID
WHERE I.LOCATION = '一操场'
GROUP BY I.ITEMID) TEMP
ON TEMP.IID = I.ITEMID
AND TEMP.MAX = G.MARK
WHERE I.LOCATION = '一操场'
--找出参加张三所参加过的项目的其他同学的姓名。
SELECT *
FROM SPORTER
WHERE SPORTERID IN
(SELECT DISTINCT SPORTERID
FROM GRADE
WHERE ITEMID IN
(SELECT ITEMID
FROM GRADE
WHERE SPORTERID IN
(SELECT SPORTERID FROM SPORTER WHERE NAME = '张三')))
AND NAME <> '张三';
--经查张三因为使用违禁药品，其成绩都记为0分，请在数据库中作出相应修改。
SELECT * FROM grade WHERE sporterid IN (SELECT sporterid FROM sporter WHERE NAME='张三');
UPDATE grade SET mark=0 WHERE sporterid IN (SELECT sporterid FROM sporter WHERE NAME='张三');
SELECT * FROM sporter s JOIN grade g ON s.sporterid=g.sporterid AND s.name='张三';
--经组委会协商，需要删除女子跳高比赛项目。
SELECT * FROM item WHERE itemname='女子跳高';
DELETE FROM item WHERE itemname='女子跳高';--因为已经设置级联删除了，所以直接删除即可。</code></pre>
<h2>小结</h2>
<ul>
<li>数据的完整性约束，是保证数据的有效性。</li>
<li>约束共有五个。</li>
<li>定义约束使用的关键字是<code>constraint</code>。</li>
<li>外键有两种级联删除。</li>
<li>约束可以修改，但是尽量在建表时就设置好。</li>
<li>约束可以禁用及启用。</li>
</ul>