外键约束

外键约束

外键约束主要是在父子表关系中体现的一种约束操作。

例如：一个人有多本书，如果要设计表，则现在需要设计两张数据表。

--删除数据表
DROP TABLE member PURGE;
DROP TABLE book PURGE;
--清空回收站
PURGE RECYCLEBIN;
--创建数据表
CREATE TABLE member(
	mid  NUMBER,
	name VARCHAR2(20)   NOT NULL,
	CONSTRAINT pk_mid PRIMARY KEY(mid)
);
CREATE TABLE book(
	bid  NUMBER,
	title VARCHAR2(20)   NOT NULL,
	mid  NUMBER
);
--测试数据
insert into member(mid,name)values (1,'韩信');
INSERT INTO member(mid,name)values (2,'李白'); 
INSERT INTO book(bid,title,mid) values (1,'java',1),
INSERT INTO book(bid,title,mid) values (2,'c',1),
INSERT INTO book(bid,title,mid) values (3,'c++',2),
INSERT INTO book(bid,title,mid) values (4,'python',2),
--提交事务
COMMIT;

如果此时增加如下信息

insert into book(bid,title,mid) values (5,'c#',20);

此时插入成功，但是member表中并没有编号为20的成员信息，因为此时没有设置约束，所以即使现在父表（member）中不存在对应的编号，但是子表依然可以使用，这就是一个错误。

实际上，book表中的mid列的内容的取值应该是由member表中的mid列所决定，所以利用外键约束解决。在设置外键约束的时候必须要设置指定的外键列（book.mid列）需要与那张表的那个列有关。

范例：设置外键

--删除数据表
DROP TABLE member PURGE;
DROP TABLE book PURGE;
--清空回收站
PURGE RECYCLEBIN;
--创建数据表
CREATE TABLE member(
	mid  NUMBER,
	name VARCHAR2(20)   NOT NULL,
	CONSTRAINT pk_mid PRIMARY KEY(mid)
);
CREATE TABLE book(
	bid  NUMBER,
	title VARCHAR2(20)   NOT NULL,
	mid  NUMBER,
	CONSTRAINT fk_mid FOREIGN KEY(mid) REFERENCES member(mid)
);
--测试数据
insert into member(mid,name)values (1,'韩信');
INSERT INTO member(mid,name)values (2,'李白'); 
INSERT INTO book(bid,title,mid) values (1,'java',1);
INSERT INTO book(bid,title,mid) values (2,'c',1);
INSERT INTO book(bid,title,mid) values (3,'c++',2);
INSERT INTO book(bid,title,mid) values (4,'python',2);
--提交事务
COMMIT;

范例：增加正确数据

insert into book (bid,title,mid) values (5,'oracle',2);

​

范例：增加错误数据

insert into book (bid,title,mid) values (6,'c#',20);

外键约束就相当于子表中的某一个字段的内容有父表来决定其数据的具体使用范围。

对外键而言最麻烦的是它存在许多限制。

• 限制一：在删除父表之前需要先删除掉它所对应的全部字表后才可以删除

  范例：member是父表，book是子表，如果book没有删除，则member无法删除

  drop table member;
  

  ​

范例：删除member表

drop table book;
drop table member;

但是，有些时候会出现一些不合理的设计，将A表作为B表的父表，B表同时设置为A表的父表，于是无法删除。在Oracle里面专门提供了强制删除父表的操作，删除之后不关心子表。

范例：强制删除父表

drop tablej member cascade constraint;

此时强制删除member表，这个时候子表book不会被删除，但是在实际开发过程中，尽量还是按照先后顺序删除。

• 限制二：如果要作为子表外键的父表列，那么这个列必须设置唯一约束或主键约束

  范例：错误的设计

  --删除数据表
  DROP TABLE member PURGE;
  DROP TABLE book PURGE;
  --清空回收站
  PURGE RECYCLEBIN;
  --创建数据表
  CREATE TABLE member(
  	mid  NUMBER,
  	name VARCHAR2(20)   NOT NULL,
  );
  CREATE TABLE book(
  	bid  NUMBER,
  	title VARCHAR2(20)   NOT NULL,
  	mid  NUMBER,
  	CONSTRAINT fk_mid FOREIGN KEY(mid) REFERENCES member(mid)
  );
  --测试数据
  insert into member(mid,name)values (1,'韩信');
  INSERT INTO member(mid,name)values (2,'李白'); 
  INSERT INTO book(bid,title,mid) values (1,'java',1);
  INSERT INTO book(bid,title,mid) values (2,'c',1);
  INSERT INTO book(bid,title,mid) values (3,'c++',2);
  INSERT INTO book(bid,title,mid) values (4,'python',2);
  --提交事务
  COMMIT;
  

  

• 限制三：如果主表中的某一行数据有对应的子表数据，那么必须先删除子表中的全部数据

  恢复正常数据

  --删除数据表
  DROP TABLE member PURGE;
  DROP TABLE book PURGE;
  --清空回收站
  PURGE RECYCLEBIN;
  --创建数据表
  CREATE TABLE member(
  	mid  NUMBER,
  	name VARCHAR2(20)   NOT NULL,
  	CONSTRAINT pk_mid PRIMARY KEY(mid)
  );
  CREATE TABLE book(
  	bid  NUMBER,
  	title VARCHAR2(20)   NOT NULL,
  	mid  NUMBER,
  	CONSTRAINT fk_mid FOREIGN KEY(mid) REFERENCES member(mid)
  );
  --测试数据
  insert into member(mid,name)values (1,'韩信');
  INSERT INTO member(mid,name)values (2,'李白'); 
  INSERT INTO book(bid,title,mid) values (1,'java',1);
  INSERT INTO book(bid,title,mid) values (2,'c',1);
  INSERT INTO book(bid,title,mid) values (3,'c++',2);
  INSERT INTO book(bid,title,mid) values (4,'python',2);
  --提交事务
  COMMIT;
  

  范例：删除父表中的数据

  DELETE FROM member WHERE mid=1；
  

  

  由于book表中有子记录，所以父表的记录就无法删除。

  但是如果说现在不想受到子记录的困扰，那么就可以使用级联的操作关系。

  级联的关系有两种：级联删除、级联更新。

  • 级联删除：级联删除指的是在父表数据已经被删除的情况下，自动删除其对应的子表数据，在使用外键的时候，使用ON DELETE CASCAD即可。

    范例：级联删除

    --删除数据表
    DROP TABLE book PURGE;
    DROP TABLE member PURGE;
    
    --清空回收站
    PURGE RECYCLEBIN;
    --创建数据表
    CREATE TABLE member(
    	mid  NUMBER,
    	name VARCHAR2(20)   NOT NULL,
    	CONSTRAINT pk_mid PRIMARY KEY(mid)
    );
    CREATE TABLE book(
    	bid  NUMBER,
    	title VARCHAR2(20)   NOT NULL,
    	mid  NUMBER,
    	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,'李白'); 
    INSERT INTO book(bid,title,mid) values (1,'java',1);
    INSERT INTO book(bid,title,mid) values (2,'c',1);
    INSERT INTO book(bid,title,mid) values (3,'c++',2);
    INSERT INTO book(bid,title,mid) values (4,'python',2);
    --提交事务
    COMMIT;
    

    范例：级联删除

    delete from member where mid=1;
    

    

    此时，当删除了父表数据之后，子表数据会同时删除掉。

  • 级联更新

    如果说现在删除父表数据的时候，那么对应的子表数据的外键列就设置为null。使用ON DELETE NULL设置

    范例：设置级联更新

    --删除数据表
    DROP TABLE book PURGE;
    DROP TABLE member PURGE;
    
    --清空回收站
    PURGE RECYCLEBIN;
    --创建数据表
    CREATE TABLE member(
    	mid  NUMBER,
    	name VARCHAR2(20)   NOT NULL,
    	CONSTRAINT pk_mid PRIMARY KEY(mid)
    );
    CREATE TABLE book(
    	bid  NUMBER,
    	title VARCHAR2(20)   NOT NULL,
    	mid  NUMBER,
    	CONSTRAINT fk_mid FOREIGN KEY(mid) REFERENCES member(mid) ON DELETE NULL
    );
    --测试数据
    insert into member(mid,name)values (1,'韩信');
    INSERT INTO member(mid,name)values (2,'李白'); 
    INSERT INTO book(bid,title,mid) values (1,'java',1);
    INSERT INTO book(bid,title,mid) values (2,'c',1);
    INSERT INTO book(bid,title,mid) values (3,'c++',2);
    INSERT INTO book(bid,title,mid) values (4,'python',2);
    --提交事务
    COMMIT;
    

    范例：级联更新删除

    DELETE FROM member where mid=1;
    

    
    什么时候使用级联，视具体场景而定。