完整约束3（学习笔记）_Oracle开发

完整约束三（学习笔记）

主外键约束FK

FOREIGN KEY(FK)

CONSTRAINT fk_外键名 FOREIGN KEY(列名) REFERENCES 表2名(列名)--一般表2的主键

先删除DROP TABLE MEMBER PURGE;

创建表-成员

CREATE TABLE MEMBER (    mid NUMBER ,    NAME VARCHAR2(20) NOT NULL,    --创建主键约束   CONSTRAINT PK_mid PRIMARY KEY(mid));

添加数据

INSERT INTO MEMBER(mid,NAME)VALUES(1,'张无忌');INSERT INTO MEMBER(mid,NAME)VALUES(2,'张三丰');

创建另一张表建议表

CREATE TABLE advice(      adid NUMBER,      content CLOB NOT NULL,      mid NUMBER,      CONSTRAINT PK_adid PRIMARY KEY(adid));

为avvice添加数据

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,'要多开展员工培训活动，让员工更加有归属感',1);INSERT INTO advice(adid,content,mid)VALUES(4,'应该开展多元化业务，更加满足市场需求',2);INSERT INTO advice(adid,content,mid)VALUES(5,'大力发展技术部门，为本公司设计自己的ERP系统，适应电子化信息发展要求',2);COMMIT;--查询advice表SELECT * FROM advice;

查询出每位成员的完整信息和所提出的意见数量

SELECT m.mid,m.name,COUNT(a.mid)FROM MEMBER m,advice aWHERE m.mid=a.midGROUP BY m.mid,m.name;可以看出可以正确统计，advice中的MID应该取值范围应该由成员表中的mid的内容现在并没有限制

在 advice中增加一条编号不在member中mid范围内的数据

INSERT INTO advice(adid,content,mid) VALUES(6,'岗位职责透明化',99);--查询advice表SELECT * FROM advice;--可以添加成功

指定主-外键约束

--先删除

DROP TABLE MEMBER PURGE;DROP TABLE advice PURGE;

创建同样的表

创建表成员CREATE TABLE MEMBER (    mid NUMBER ,    NAME VARCHAR2(20) NOT NULL,    --创建主键约束   CONSTRAINT PK_mid PRIMARY KEY(mid));--创建另一张表建议表CREATE TABLE advice(      adid NUMBER,      content CLOB NOT NULL,      mid NUMBER,      CONSTRAINT PK_adid PRIMARY KEY(adid),      CONSTRAINT FK_mid FOREIGN KEY(mid) REFERENCES MEMBER(mid));--添加数据INSERT INTO MEMBER(mid,NAME)VALUES(1,'刘楠');INSERT INTO MEMBER(mid,NAME)VALUES(2,'董方卓');--查询SELECT * FROM MEMBER;--为avvice添加数据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,'要多开展员工培训活动，让员工更加有归属感',1);INSERT INTO advice(adid,content,mid)VALUES(4,'应该开展多元化业务，更加满足市场需求',2);INSERT INTO advice(adid,content,mid)VALUES(5,'大力发展技术部门，为本公司设计自己的ERP系统，适应电子化信息发展要求',2);COMMIT;--查询advice表SELECT * FROM advice;

此时再在 advice中增加一条编号不在member中mid范围内的数据

INSERT INTO advice(adid,content,mid) VALUES(6,'岗位职责透明化',99);

错误的信息就添加不进去了

删除父表时要先删除子表对应的数据

示例:删除member中编号为1的数据

DELETE FROM MEMBER WHERE mid=1;

会报错

不能删除

需要删除子表advice中mid=1的数据再删除父表member中mid=1的数据DELETE FROM advice WHERE mid=1;DELETE FROM MEMBER WHERE mid=1;

这时可以删除

也可以在创建表时设置级联

可以在创建表时设置级联删除--在外键设置后面设置ON DELETE CASCADE

--先删除

DROP TABLE advice PURGE;DROP TABLE MEMBER PURGE;

创建表成员CREATE TABLE MEMBER (    mid NUMBER ,    NAME VARCHAR2(20) NOT NULL,    --创建主键约束   CONSTRAINT PK_mid PRIMARY KEY(mid));--创建另一张表建议表CREATE TABLE advice(      adid NUMBER,      content CLOB NOT NULL,      mid NUMBER,      CONSTRAINT PK_adid PRIMARY KEY(adid),      CONSTRAINT FK_mid FOREIGN KEY(mid) REFERENCES MEMBER(mid) ON DELETE CASCADE              --主外键约束添加级联删除);

这样删除父表中的记录时，可以直接删除

但查询子表发现子表中的记录也被删除掉了

不想删除子表中的记录时可以用

在外键后加上ON DELETE SET NULL

--删除表DROP TABLE advice;DROP TABLE MEMBER;--创建表CREATE TABLE MEMBER(     mid NUMBER,     NAME VARCHAR2(30) NOT NULL,     CONSTRAINT PK_mid PRIMARY KEY(mid)   );CREATE TABLE advice(   adid NUMBER,   CONTENT CLOB NOT NULL,   mid NUMBER,   CONSTRAINT PK_adid PRIMARY KEY(adid),   CONSTRAINT FK_mid FOREIGN KEY(mid)  REFERENCES MEMBER(mid) ON DELETE SET NULL                --设置删除时子表记录相关的列为NULL);

这样删除父表中的记录时，可以直接删除

但查询子表发现子表中的记录没有删除掉，只是相关的字段为NULL

强制删除表

DROP FROM MEMBER WHERE mid=1 CASCADE CONSTRAINT;

父表会直接删除，子表内容仍然存在

查看约束

SELECT constraint_name,constraint_type,table_name FROM user_constraints;--查看EMP表的约束SELECT constraint_name,constraint_type,table_nameFROM user_constraintsWHERE table_name='EMP'

查看user_cons_columns数据字典select * from user_cons_columns;

修改约束

增加约束语法

ALTER TABLE 表名称

ADD CONSTRAINT 约束名称 约束类型(约束字段)

删除表DROP TABLE advice;DROP TABLE MEMBER;--创建表CREATE TABLE MEMBER(     mid NUMBER,     NAME VARCHAR2(30)        );CREATE TABLE advice(   adid NUMBER,   CONTENT CLOB ,   mid NUMBER);--为member表的mid字段增加主键约束ALTER TABLE MEMBERADD CONSTRAINT PK_mid PRIMARY KEY(mid);--为member age字段增加检查约束--先增加age字段ALTER TABLE MEMBERADD(age NUMBER);SELECT * FROM MEMBER;ALTER TABLE MEMBERADD CONSTRAINT ck_age CHECK(age BETWEEN 0 AND 200); --检查member表中的字段SELECT constraint_name,constraint_type,table_nameFROM User_Constraints WHERE table_name='MEMBER';--为name字段设置非空ALTER TABLE MEMBERMODIFY(NAME NOT NULL);

启用和禁用约束

ALTER TABLE 表名称 ENABLE |DISABLE CONSTRAINT 约束名称 [CASCADE]

--cascade关闭存在完整性关系的约束（主要是外键）

--禁用advcie中的Pk-adid主键约束ALTER TABLE advice DISABLE CONSTRAINT pk_adid;--禁用memeber表中的pk_mid主键约束ALTER TABLE MEMBER DISABLE CONSTRAINT pk_mid;--启用约束ALTER TABLE 表名称 ENABLE CONSTRAINT 约束名称--启用memeber表中的pk_mid主键约束ALTER TABLE MEMBER ENABLE CONSTRAINT pk_mid--删除约束ALTER TABLE 表名称 DROP CONSTRAINT 约束名[CASCADE]--删除advcie中的pk_adid约束-无关外键ALTER TABLE advice DROP CONSTRAINT pk_adid;--删除member中的pk_mid-有关外键ALTER TABLE MEMBER DROP CONSTRAINT pk_mid CASCADE;