SQL语句（八）参照完整性

1.参照完整性

      参照完整性主要是定义外码，将一个关系的主码放在另一个关系中，作为该关系的属性，就称其为外码。外码的取值有两种情况，一种为空，另外一种就是被参照表的主码的域。

2.实例

1.创建表时定义参照完整性
①先定义nation3的实体完整性，再定义supplier3在nation3上的列级参照完整性
CREATE TABLE nation3(/*国家表*/
  nationkey INTEGER PRIMARY KEY,           /*国家编号*/
  name CHAR(25),                           /*国家名称*/
  regionkey INTEGER,                       /*地区编号*/
  comment VARCHAR(152)                     /*备注*/
);

CREATE TABLE supplier3(/*供应商基本表*/
   suppkey INTEGER PRIMARY KEY,            /*供应商编号*/
   name CHAR(50),                          /*供应商名称*/
   address VARCHAR(80),                    /*供应商地址*/
   nation INTEGER REFERENCES nation3(nationkey),/*国家编号*/
   phone CHAR(30),                          /*供应商电话*/
   acctbal REAl,
   comment VARCHAR(101)                     /*备注*/
);
②先定义nation3的实体完整性，再定义supplier4在nation3上的表级参照完整性
CREATE TABLE supplier4(/*供应商基本表*/
   suppkey INTEGER PRIMARY KEY,            /*供应商编号*/
   name CHAR(50),                          /*供应商名称*/
   address VARCHAR(80),                    /*供应商地址*/
   nation INTEGER,                          /*国家编号*/
   phone CHAR(30),                          /*供应商电话*/
   acctbal REAl,
   comment VARCHAR(101),                     /*备注*/
   CONSTRAINT nation_3 FOREIGN KEY(nation)REFERENCES nation3(nationkey)
);
2.创建表后定义参照完整性
CREATE TABLE nation4(/*国家表*/
  nationkey INTEGER PRIMARY KEY,           /*国家编号*/
  name CHAR(25),                           /*国家名称*/
  regionkey INTEGER,                       /*地区编号*/
  comment VARCHAR(152)                     /*备注*/
);
ALTER TABLE nation4
ADD CONSTRAINT regionkey_4
FOREIGN KEY(regionkey)REFERENCES region(regionkey);
3.外码由多个属性组成时定义参照完整性
CREATE TABLE partsupp3(/*零件供应联系表*/
  partkey INTEGER,/*零件编号*/
  suppkey INTEGER,/*供应商编号*/
  availqty INTEGER,/*可用数量*/
  supplycost REAL,/*供应价格*/
  comment VARCHAR(199),/*备注*/
  PRIMARY KEY(partkey,suppkey),/*定义主码*/
  FOREIGN KEY(partkey)REFERENCES part(partkey),
  FOREIGN KEY(suppkey)REFERENCES supplier(suppkey)
  );
4.定义参照完整性的违约处理
 CREATE TABLE customer3(/*顾客表*/
  custkey INTEGER PRIMARY KEY,/*顾客编号*/
  name VARCHAR(25),/*姓名*/
  address VARCHAR(40),/*地址*/
  nationkey INTEGER,/*国籍编号*/
  phong CHAR(15),/*电话*/
  acctbal CHAR(10),/*账户余额*/
  mktsegment CHAR(10),/*市场分区*/
  comment VARCHAR(117),/*备注*/
  CONSTRAINT nationkey_3 FOREIGN KEY(nationkey)REFERENCES nation(nationkey)
  ON DELETE CASCADE
  ON UPDATE SET NULL
 );
5.删除参照完整性
ALTER TABLE nation4
DROP CONSTRAINT regionkey_4;
6.插入一条记录，验证参照完整性是否起到作用
SELECT *
FROM nation3;
INSERT
INTO supplier3
VALUES(1,'lllllll','中国',40,'178233233233',1,'vip');

 

3.说明

       在实例内容6中，插入到supplier3中的nationkey 40在nation3中并不存在，因此插入时违反了参照完整性。关于参照完整性的违约处理，一般情况下对参照表和被参照表的操作违反了参照完整性时，系统选择默认的策略，即拒绝执行，如果想让系统采用其他的策略则必须在创建表格时显示地加以说明。