Oracle之表约束

最新推荐文章于 2023-08-12 17:31:12 发布

wowuyula12

最新推荐文章于 2023-08-12 17:31:12 发布

阅读量1.7k

点赞数

文章标签： oracle table insert date null delete

本文链接：https://blog.csdn.net/wowuyula12/article/details/6494894

版权

3.2约束（重点）

在数据库表的开发中，约束是必不可少的支持，使用约束可以
更好的保证数据库中数据的完整性

3.2.1约束的分类
在实际中，约束主要分为以下五种约束：
    1.主键约束：主键表示是一个唯一的标识，本身不能为空
      例如：身份证号码是唯一分，不可重复的，不可为空
    2.唯一约束：在一个表中只允许建立一个主键约束，而其他列
       如果不希望出现重复值ude话，那么就可以使用唯一的约束
    3.检查约束：检查一个列的内容是否合法
       例如年龄只能在0-150之间
       例如性别只能是男女中性
    4.非空约束：姓名这样的字段内容就不能为空
    5.外键约束：在两张表中进行约束操作

3.2.2主键约束(PRIMARY KEY)
  主键约束一般都是在ID上使用，而且本身已经默认了内容不能为空，
  主键约束可以在建议表的时候指定：

  范例:建立person表 pid使用主键约束
  CREATE TABLE person(
    pid VARCHAR2(18) PRIMARY KEY,
    name VARCHAR2(200),
    age NUMBER(3),
    birthday DATE,
    sex VARCHAR2(2) DEFAULT '男'
  );

  范例：插入数据,其中插入的主键重复和为空

  INSERT INTO person (pid,name,age,birthday，sex)
  VALUES('320882198711262832','张三',30,TO_DATE
  ('1987-11-26','yyyy-mm-dd'),'女');

  INSERT INTO person(pid,name,age,birthday)
  VALUES('320882198711262832','李四',30,TO_DATE
  ('1988-01-16','yyyy-mm-dd'));
  此时显示错误信息：违反唯一约束条件,但是错误信息不友好

测试插入空值：

INSERT INTO person(name,age,birthday)
  VALUES('李四',30,TO_DATE
  ('1988-01-16','yyyy-mm-dd'));
第 1 行出现错误:
ORA-01400: 无法将 NULL 插入 ("SCOTT"."PERSON"."PID")
错误信息为用户SCOTT 下的person表中的PID字段不能为空

以上的约束是属于系统自动分配好的约束名称，也可以通过CONSTRAINT 指定一个约束的名字

范例：
将person中的pid 指定名称
DROP TABLE person ;
CREATE TABLE person(
    pid VARCHAR2(18),
    name VARCHAR2(200),
    age NUMBER(3),
    birthday DATE,
    sex VARCHAR2(2) DEFAULT '男'，
    CONSTRAINT person_pid_pk PRIMARY KEY(pid)
  );

  然后重复上面的插入重复PID 的SQL语句和NULL值的SQL语句，
  我们发现报的错误和之前完全一致
  CONSTRAINT person_pid_pk 就表示之前在建立数据库表的时候指定的约束名称

3.2.3非空约束(NOT NULL)
  使用非空约束，表示一个字段不能为空即，插入的数据必须有值
  DROP TABLE person ;
CREATE TABLE person(
    pid VARCHAR2(18),
    name VARCHAR2(200) NOT NULL,
    age NUMBER(3) NOT NULL,
    birthday DATE,
    sex VARCHAR2(2) DEFAULT '男'，
    CONSTRAINT person_pid_pk PRIMARY KEY(pid)
  );
  插入空值进行测试：

  INSERT INTO person(pid,age,birthday)
  VALUES('320882198711262832',30,TO_DATE
  ('1988-01-16','yyyy-mm-dd'));
  第 1 行出现错误:
ORA-01400: 无法将 NULL 插入 ("SCOTT"."PERSON"."NAME")
插入 age 也是一样

3.2.4唯一约束(UNIQUE)
  表示一个字段中的内容是唯一的,其他列不允许重复我们设定姓名不能重复

DROP TABLE person ;
CREATE TABLE person(
    pid VARCHAR2(18),
    name VARCHAR2(200) UNIQUE NOT NULL,
    age NUMBER(3) NOT NULL,
    birthday DATE,
    sex VARCHAR2(2) DEFAULT '男'，
    CONSTRAINT person_pid_pk PRIMARY KEY(pid)
  );

INSERT INTO person (pid,name,age,birthday，sex)
  VALUES('1111111111','张三',30,TO_DATE
  ('1987-11-26','yyyy-mm-dd'),'女');
  第一条插入成功
  INSERT INTO person (pid,name,age,birthday，sex)
  VALUES('320882198711262832','张三',30,TO_DATE
  ('1987-11-26','yyyy-mm-dd'),'女');

第 1 行出现错误:
ORA-00001: 违反唯一约束条件
注意：使用 CONSTRAINT 添加唯一约束

  DROP TABLE person ;
CREATE TABLE person(
    pid VARCHAR2(18),
    name VARCHAR2(200)NOT NULL,
    age NUMBER(3) NOT NULL,
    birthday DATE,
    sex VARCHAR2(2) DEFAULT '男'，
    CONSTRAINT person_pid_pk PRIMARY KEY(pid),
    CONSTRAINT person_name_uk UNIQUE(name)
  );
  重复上面的插入数据操作，此时的错误信息为
第 1 行出现错误:
ORA-00001: 违反唯一约束条件 (SCOTT.PERSON_NAME_UK)
我们很明确的看到了违反UNIQUE的字段是name

3.2.3检查约束(CHECK)
使用检查约束来判断一个列中插入的数据是否违反检查条件

DROP TABLE person ;
CREATE TABLE person(
    pid VARCHAR2(18),
    name VARCHAR2(200)NOT NULL,
    age NUMBER(3) NOT NULL CHECK(age BETWEEN 0 AND 150 ),
    birthday DATE,
    sex VARCHAR2(2) DEFAULT '男' CHECK(sex IN('男','女','中'))，
    CONSTRAINT person_pid_pk PRIMARY KEY(pid),
    CONSTRAINT person_name_uk UNIQUE(name)
  );
插入错误的年龄：
INSERT INTO person (pid,name,age,birthday，sex)
  VALUES('320882198711262832','张三',300,TO_DATE
  ('1987-11-26','yyyy-mm-dd'),'女');
  错误信息为：
第 1 行出现错误:
ORA-02290: 违反检查约束条件 (SCOTT.SYS_C009689)

我们接着插入错误的性别
INSERT INTO person (pid,name,age,birthday，sex)
  VALUES('320882198711262832','张三',300,TO_DATE
  ('1987-11-26','yyyy-mm-dd'),'无');
  错误信息为：
第 1 行出现错误:
ORA-02290: 违反检查约束条件 (SCOTT.SYS_C009690)

我们看到错误信息和之前一样都是不友好的，而且是累加的
我们可以CONSTRAINT 来改善显示的错误信息
DROP TABLE person ;
CREATE TABLE person(
    pid VARCHAR2(18),
    name VARCHAR2(200)NOT NULL,
    age NUMBER(3) NOT NULL ,
    birthday DATE,
    sex VARCHAR2(2) DEFAULT '男' CHECK(sex IN('男','女','中'))，
    CONSTRAINT person_pid_pk PRIMARY KEY(pid),
    CONSTRAINT person_name_uk UNIQUE(name),
    CONSTRAINT person_age_ck CHECK(age BETWEEN 0 AND 150),
    CONSTRAINT person_sex_ck CHECK(sex IN('男','女','中'))
  );
第 1 行出现错误:
  ORA-02290: 违反检查约束条件 (SCOTT.PERSON_SEX_CK)
  错误信息比较易懂

3.2.6主-外键约束(FOREIGN KEY)
之前所讲的全部约束都是针对一张表的，那么主外键约束是针对两张表的约束
为什么需要主外键约束呢？
范例：要求完成一个程序，一本书要属于一个人
   书本身应该是一个表，一个书中必须要有一个字段是属于哪个人的
DROP TABLE book;
   DROP TABLE person ;
CREATE TABLE person(
    pid VARCHAR2(18),
    name VARCHAR2(200)NOT NULL,
    age NUMBER(3) NOT NULL ,
    birthday DATE,
    sex VARCHAR2(2) DEFAULT '男' CHECK(sex IN('男','女','中'))，
    CONSTRAINT person_pid_pk PRIMARY KEY(pid),
    CONSTRAINT person_name_uk UNIQUE(name),
    CONSTRAINT person_age_ck CHECK(age BETWEEN 0 AND 150),
    CONSTRAINT person_sex_ck CHECK(sex IN('男','女','中'))
  );
CREATE TABLE book(
  bid NUMBER PRIMARY KEY,
  BNAME VARCHAR(30),
  BPRICE NUMBER(5,2),
  pid VARCHAR2(18)
);
INSERT INTO person (pid,name,age,birthday，sex)
  VALUES('320882198711262832','张三',30,TO_DATE
  ('1987-11-26','yyyy-mm-dd'),'女');
INSERT INTO book (bid,bname,bprice,pid) VALUES(
1,'JAVA SE',89.9,'320882198711262832');
确实符合要求，但是如果我们插入以下的数据
INSERT INTO book (bid,bname,bprice,pid) VALUES(
2,'JAVA SE',89.9,'000000000000');
此编号的人员并不存在，如果不存在，则此数据肯定不应该插入，
此时，如果想要解决这样的问题，则肯定要使用主外键约束
修改 book的建表语句为
DROP TABLE book;
CREATE TABLE book(
  bid NUMBER PRIMARY KEY,
  BNAME VARCHAR(30),
  BPRICE NUMBER(5,2),
  pid VARCHAR2(18),
  CONSTRAINT person_book_pid_fk FOREIGN KEY(pid) REFERENCES person(pid)
);

我们整个脚本为：

  DROP TABLE book;
DROP TABLE person ;
CREATE TABLE person(
    pid VARCHAR2(18),
    name VARCHAR2(200)NOT NULL,
    age NUMBER(3) NOT NULL ,
    birthday DATE,
    sex VARCHAR2(2) DEFAULT '男' CHECK(sex IN('男','女','中'))，
    CONSTRAINT person_pid_pk PRIMARY KEY(pid),
    CONSTRAINT person_name_uk UNIQUE(name),
    CONSTRAINT person_age_ck CHECK(age BETWEEN 0 AND 150),
    CONSTRAINT person_sex_ck CHECK(sex IN('男','女','中'))
  );
CREATE TABLE book(
  bid NUMBER PRIMARY KEY,
  BNAME VARCHAR(30),
  BPRICE NUMBER(5,2),
  pid VARCHAR2(18),
   CONSTRAINT person_book_pid_fk FOREIGN KEY(pid) REFERENCES person(pid)
);
INSERT INTO person (pid,name,age,birthday，sex)
  VALUES('320882198711262832','张三',30,TO_DATE
  ('1987-11-26','yyyy-mm-dd'),'女');
INSERT INTO book (bid,bname,bprice,pid) VALUES(
1,'JAVA SE',89.9,'320882198711262832');
此时进行上面的测试插入得出错误信息
ORA-02291: 违反完整约束条件 (SCOTT.PERSON_BOOK_PID_FK) - 未找到父项关键字
此时，可以保证两张表的数据的完整性，不会出现找不到对应数据的情况
在使用主外键关联的时候也要有以下的注意点：
在字表中设置的外键在父表中必须是逐渐
删除时应该先删除子表，再删除父表
测试：
DROP TABLE person; 报错表中的主键被外键引用
DROP TABLE book;   删除成功
DROP TABLE person; 删除成功

如果我们不想删除子表，想只删除父表可以使用强制性的删除手段
DROP TABLE person CASCADE CONSTRAINT 如下：
DROP TABLE person CASCADE CONSTRAINT;
  不管约束，而直接删除，但是这种方法一般不使用

回顾：
   咱们之前学习的emp 和dept 也是主外键关联的2张表

   范例：现在向emp表中增加一个50部门的员工(我们知道部门表里面没有50部门)

   INSERT INTO emp(empno,ename,job,sal,mgr,hiredate,comm,deptno)
   VALUES(8888,'张三','经理',90000，null,sysdate,null,50);
  ORA-02291: 违反完整约束条件 (SCOTT.FK_DEPTNO) - 未找到父项关键字
  无法插入成功
  在主外键关联中也可以使用级联删除的情况
  以现在数据库中的数据为例：
  DELETE FROM person WHERE pid='320882198711262832';
错误信息：违反完整约束条件 (SCOTT.PERSON_BOOK_PID_FK) - 已找到子记录
此时要想完成删除操作，则必须要将book表中对应的数据删除掉
如果，希望一个表中的数据在删除时，可以自动删除掉其对应的字表中对应的记录修改如下
CREATE TABLE book(
  bid NUMBER PRIMARY KEY,
  BNAME VARCHAR(30),
  BPRICE NUMBER(5,2),
  pid VARCHAR2(18),
   CONSTRAINT person_book_pid_fk FOREIGN KEY(pid) REFERENCES person(pid) ON DELETE CASCADE
);

完整的SQL脚本如下：
DROP TABLE book;
   DROP TABLE person ;
CREATE TABLE person(
    pid VARCHAR2(18),
    name VARCHAR2(200)NOT NULL,
    age NUMBER(3) NOT NULL ,
    birthday DATE,
    sex VARCHAR2(2) DEFAULT '男' CHECK(sex IN('男','女','中'))，
    CONSTRAINT person_pid_pk PRIMARY KEY(pid),
    CONSTRAINT person_name_uk UNIQUE(name),
    CONSTRAINT person_age_ck CHECK(age BETWEEN 0 AND 150),
    CONSTRAINT person_sex_ck CHECK(sex IN('男','女','中'))
  );
CREATE TABLE book(
  bid NUMBER PRIMARY KEY,
  BNAME VARCHAR(30),
  BPRICE NUMBER(5,2),
  pid VARCHAR2(18),
   CONSTRAINT person_book_pid_fk FOREIGN KEY(pid) REFERENCES person(pid)ON DELETE CASCADE
);
INSERT INTO person (pid,name,age,birthday，sex)
  VALUES('320882198711262832','张三',30,TO_DATE
  ('1987-11-26','yyyy-mm-dd'),'女');
INSERT INTO book (bid,bname,bprice,pid) VALUES(
1,'JAVA SE',89.9,'320882198711262832');

建立好表并且插入好数据后，我们再进行删除
DELETE FROM person WHERE pid='320882198711262832';
我们发现删除成功了此时再去book表中查询图书记录
SELECT * FROM book;
发现对应的记录也被删除了

3.2.7修改约束（了解）

如果一张表已经建议完成之后，则可以为其增加约束。
如我们通过下面的SQL语句建立了一个person表
CREATE TABLE person(
    pid VARCHAR2(18),
    name VARCHAR2(200)NOT NULL,
    age NUMBER(3) NOT NULL ,
    birthday DATE,
    sex VARCHAR2(2) DEFAULT '男'
  );
此时，需要为表中添加若干个约束，添加约束的语法如下：

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

     关于约束的类型的命名一定要统一;
        PRIMARY KEY:主键字段_pk (主键约束)
        UNIQUE:字段_UK(唯一约束)
        CHECK:字段_CK(检查约束)
        FOREIGN KEY:父字段_子字段_FK(外键约束)

范例：为person表添加该有的约束
  ALTER TABLE person ADD CONSTRAINT person_pid_pk PRIMARY KEY(pid);
  ALTER TABLE person ADD CONSTRAINT person_name_UK UNIQUE(name);
  ALTER TABLE person ADD CONSTRAINT person_age_CK CHECK(age BETWEEN 0 AND 150);
  ALTER TABLE person ADD CONSTRAINT person_sex_CK CHECK(sex IN('男','女','中'));

如果想要增加主外键约束，则可以创建一个没有约束的book表
CREATE TABLE book(
  bid NUMBER,
  BNAME VARCHAR(30),
  BPRICE NUMBER(5,2),
  pid VARCHAR2(18)
);

为表中增加我们的主键和外键约束

ALTER TABLE book ADD CONSTRAINT persomn_bid_pk PRIMARY KEY(bid);
ALTER TABLE book ADD CONSTRAINT person_book_pid_fk FOREIGN KEY(pid) REFERENCES person(pid);

测试：直接删除person表
DROP TABLE person;

错误信息：表中的唯一主键被外键引用说明外键关联生效了
既然可以增加约束，那么就可以删除约束，删除页数的时候要指定约束的名称。

ALTER TABLE 表名称 DROP CONSTRAINT 约束名称；
范例：删除person 表中的age 和sex上的约束

ALTER TABLE person DROP CONSTRAINT person_age_CK;
ALTER TABLE person DROP CONSTRAINT person_sex_CK;
ALTER TABLE book DROP CONSTRAINT person_book_pid_fk;