约束

oracle用约束 (constraints) 来防止无效数据、错误数据输入到表中。
你可以使用约束做下面的事：在插入、更新行或者从表中删除行的时候强制表中的数据遵循规则。对于成功的操作，约束必须被满足。如果表之间有依赖关系，防止表的删除

所有的约束存储在数据字典中。如果给约束一个有意义的名字，约束易于引用，约束命名必须遵守标准的对象命名规则。如果不命名你的约束，Oracle 服务器将用格式 SYS_Cn 产生一个名字，
这里 n 是一个唯一的整数，所以约束名是唯一的。

在实际中，约束只要分为以下五种约束：

主键约束(primary key)：主键表示是一个唯一的标识，本身不能为空，非空且唯一。例如：身份证编号是唯一的，不可重复的，不可为空的
唯一约束(unique)：在一个表中只允许建立一个主键约束，而其他列如果不希望出现重复值的话，则就可以使用唯一约束。 UNIQUE 约束允许输入空值
检查约束(check)：检查一个列的内容是否合法.例如：年龄，只能在 0~150 之间,性别，只能是男、女
非空约束(not null)：姓名这样的字段里面的内容就不能为空。
外键约束(foreign key)：用来维护从表（Child Table）和主表（Parent Table）之间的引用完整性. 外键约束是个有争议性的约束，它一方面能够维护数据库的数据一致性，数据的完整性。防止错误的垃圾数据入库；另外一方面它会增加表插入、更新等SQL性能的额外开销，不少系统里面通过业务逻辑控制来取消外键约束。例如在数据仓库中，就推荐禁用外键约束。

CREATE TABLE LHR.EMPLOYEES
( EMPLOYEE_ID NUMBER(6,0),
FIRST_NAME VARCHAR2(20),
LAST_NAME VARCHAR2(25) CONSTRAINT EMP_LAST_NAME_NN NOT NULL ENABLE,
CONSTRAINT EMP_SALARY_MIN CHECK (salary > 0) ENABLE,
CONSTRAINT EMP_EMAIL_UK UNIQUE (EMAIL)
CONSTRAINT dept_id_pk PRIMARY KEY (department_id)
CONSTRAINT person_book_pid_fk FOREIGN KEY(pid) REFERENCES person(pid)
);

C　　　　　　(check constraint on a table)
P　　　　　　(primary key)
U　　　　　　(unique key)
R　　　　　　(referential integrity)
V　　　　　　(with check option, on a view)
O　　　　　　(with read only, on a view)

约束名称建议自己定义一套命名规则，否则使用系统生成的约束名，很难能把它和对应的表、字段联系起来。

　　　　非空约束 　　　　NN_表名_列名
　　　　唯一约束 　　　　UK_表名_列名
　　　　主键约束 　　　　PK_表名
　　　　外键约束 　　　　FK_表名_列名
　　　　条件约束 　　　　CK_表名_列名
　　　　默认约束 　　　　DF_表名_列名

1、外键约束：

外键约束定义在具有父子关系的子表中，外键约束使得子表中的列对应父表的主键列，用以维护数据库的完整性。不过出于性能和后期的业务系统的扩展的考虑，很多时候，外键约束仅出现在数据库的设计中，实际会放在业务程序中进行处理。外键约束注意以下几点：

　　外键约束的子表中的列和对应父表中的列数据类型必须相同，列名可以不同
　　对应的父表列必须存在主键约束（PRIMARY KEY）或唯一约束（UNIQUE）
　　外键约束列允许NULL值，对应的行就成了孤行了

外键基于数据值，并且纯粹是逻辑的，不是物理的，指针。

在使用主-外键关联的时候，也要有以下的注意点：
1 在子表中设置的外键在 父表中必须是主键
2 删除时应该先删除子表，再删除父表。

FOREIGN KEY 被用于在表约束级定义子表中的列。
REFERENCES 确定父表中的表和列。
ON DELETE CASCADE 指出当父表中的行被删除时，子表中相依赖的行也将被级联删除。
ON DELETE SET NULL 当父表的值被删除时，转换外键值为空。
默认行为被称为约束规则，该规则不允许引用数据的更新或删除。
无 ON DELETE CASCADE 或 ON DELETE SET NULL 选项，如果父表中的行在子表中引用，则它不能被删除。

注：如果在外键定义中使用了ON DELETE SET NULL 或ON DELETE CASCADE，无论删除操作是在父表这一端还是子表这一端都不会产生违反引用完整性的问题，但是却留下了安全隐患。这两个子句要谨慎使用，只有在不得已时使用，谨记谨记！！！

外键约束对DML与DDL的影响：

INSERT：只有操作是在子表或从表这一端时才会产生违反引用完整性约束的问题，父表则不然。

DELETE：只有操作是在父表或主表这一端时才会产生违反引用完整性约束的问题，子表则不然。

UPDATE：子表父表直接操作都会违反引用完整性约束。两种解决方法：

1）先更新子表的引用列为空，再更新父表的主键的列的值，然后把子表的引用列更新成新的父表的值；

2）使用ON DELETE SET NULL，先更新父表，然后将子表外键为空的记录更新为新的值。

DDL语句：DROP TABLE与TRUNCATE TABLE，操作父表，违反引用完整性约束，子表则不然。

2、外键索引

子表外键未加索引很容易导致死锁。在以下两种情况下，Oracle 在修改父表后会对子表加一个全表锁：
1. 如果更新了父表的主键，由于外键上没有索引，所以子表会被锁住。
2. 如果删除了父表中的一行，由于外键上没有索引，整个子表也会被锁住

---查询没有索引的外键
COLUMN COLUMNS format a30 word_wrapped
COLUMN tablename format a15 word_wrapped
COLUMN constraint_name format a15 word_wrapped
SELECT TABLE_NAME,
CONSTRAINT_NAME,
CNAME1 || NVL2(CNAME2, ',' || CNAME2, NULL) ||
NVL2(CNAME3, ',' || CNAME3, NULL) ||
NVL2(CNAME4, ',' || CNAME4, NULL) ||
NVL2(CNAME5, ',' || CNAME5, NULL) ||
NVL2(CNAME6, ',' || CNAME6, NULL) ||
NVL2(CNAME7, ',' || CNAME7, NULL) ||
NVL2(CNAME8, ',' || CNAME8, NULL) COLUMNS
FROM (SELECT B.TABLE_NAME,
B.CONSTRAINT_NAME,
MAX(DECODE(POSITION, 1, COLUMN_NAME, NULL)) CNAME1,
MAX(DECODE(POSITION, 2, COLUMN_NAME, NULL)) CNAME2,
MAX(DECODE(POSITION, 3, COLUMN_NAME, NULL)) CNAME3,
MAX(DECODE(POSITION, 4, COLUMN_NAME, NULL)) CNAME4,
MAX(DECODE(POSITION, 5, COLUMN_NAME, NULL)) CNAME5,
MAX(DECODE(POSITION, 6, COLUMN_NAME, NULL)) CNAME6,
MAX(DECODE(POSITION, 7, COLUMN_NAME, NULL)) CNAME7,
MAX(DECODE(POSITION, 8, COLUMN_NAME, NULL)) CNAME8,
COUNT(*) COL_CNT
FROM (SELECT SUBSTR(TABLE_NAME, 1, 30) TABLE_NAME,
SUBSTR(CONSTRAINT_NAME, 1, 30) CONSTRAINT_NAME,
SUBSTR(COLUMN_NAME, 1, 30) COLUMN_NAME,
POSITION
FROM USER_CONS_COLUMNS) A,
USER_CONSTRAINTS B
WHERE A.CONSTRAINT_NAME = B.CONSTRAINT_NAME
AND B.CONSTRAINT_TYPE = 'R'
GROUP BY B.TABLE_NAME, B.CONSTRAINT_NAME) CONS
WHERE COL_CNT > ALL
(SELECT COUNT(*)
FROM USER_IND_COLUMNS I
WHERE I.TABLE_NAME = CONS.TABLE_NAME
AND I.COLUMN_NAME IN (CNAME1, CNAME2, CNAME3, CNAME4, CNAME5,
CNAME6, CNAME7, CNAME8)
AND I.COLUMN_POSITION <= CONS.COL_CNT
GROUP BY I.INDEX_NAME);

3、添加约束

添加的原则：
你可以添加、删除或禁用一个约束，但你不能修改它的结构。
你可以用 ALTER TABLE 语句的 MODIFY子句添加一个NOT NULL约束到一个已经存在的列。只有在表是空的或者每个行的该列都有非空值的情况下，你才可以定义一个NOT NULL列。
你可以延迟检查约束的有效，直到事务结束。

ALTER TABLE students ADD CONSTRAINT pk_students PRIMARY KEY (student_id) USING INDEX TABLESPACE student_index;
ALTER TABLE students ADD CONSTRAINT uk_students_license UNIQUE (state, license_no) USING INDEX TABLESPACE student_index;
alter table emp add constraint con check(dept_salary>0);
ALTER TABLE students ADD CONSTRAINT fk_students_state FOREIGN KEY (state) REFERENCES state_lookup (state);
alter table emp modify ename not null;

删除约束：

很多时候不使用外键，很多人认为会让删除操作比较麻烦，比如要删除父表中的某条数据，但某个子表中又有对该条数据的引用，这时就会导致删除失败。我们有两种方式来优化这种场景：

　　第一种方式简单粗暴，删除的时候，级联删除掉子表中的所有匹配行，在创建外键时，通过 on delete cascade 子句指定该外键列可级联删除：

SQL> alter table emp add constraint emp_deptno_fk foreign key(deptno) references dept (deptno) on delete cascade;

　　第二种方式，删除父表中的对应行，会将对应子表中的所有匹配行的外键约束列置为NULL，通过 on delete set null 子句实施：

SQL> alter table emp add constraint emp_deptno_fk foreign key(deptno) references dept(deptno) on delete set null;

ALTER TABLE table_name DROP PRIMARY KEY|UNIQUE(column)| CONSTRAINT constraint[CASCADE]; 
ALTER TABLE test1 DROP(pk) CASCADE CONSTRAINTS; 
ALTER TABLE DM.EMP DROP CONSTRAINT SYS_C00115525;

可以使用强制性的删除手段(不管约束，而直接删除，但是这种做法一般不使用)
DROP TABLE person CASCADE CONSTRAINT;

重命名约束
ALTER TABLE METAONE.T1 RENAME CONSTRAINT SYS_C0059699 TO PK_T1

启用禁用约束

---禁用约束

ALTER TABLE 表 DISABLE CONSTRAINT 约束名 [CASCADE];
ALTER TABLE DM.EMP DISABLE CONSTRAINT UK_EMP_COMM;
ALTER TABLE TEST DISABLE PRIMARY KEY CASCADE;
参数CASCADE子句用来关闭存在有完整性关系的约束，比如DISABLE一个主键，如果没有CASCADE子句就会出错，此时使用CASCADE子句DISABLE主键可以将该主键与相关外键一起DISABLE掉。使用的情况：例如在数据库系统中大规模装入数据时，为了系统的效率您不得不牺牲数据的一致性来关闭一些约束，甚至删除一些约束将主表主键禁止的同时，也将禁止依赖于此主键的外键禁止了．

使用上面脚本禁用约束的时候会删除索引，如果你想禁用约束的时候，保留索引，可以使用如下脚本(当然你再启用约束的时候会重新重建索引，这对大表显然开销相当大)
ALTER TABLE DM.EMP DISABLE CONSTRAINT UK_EMP_COMM KEEP INDEX;

启用约束
如果关闭主键约束时使用了CASCADE，此时使用ENABLE不会将主键约束与外键约束同时打开，只能对主键约束与外键约束分别使用ENABLE CONSTRAINT 子句打开。

推迟约束
Deferring Constraint Checks

当前事务的Constraint Checks全部推迟
SET CONSTRAINTS ALL DEFERRED;
SET CONSTRAINT XXX DEFERRED;

当前会话的Constraint Checks．
ALTER SSSSION SET CONSTRAINTS ALL deferred;
ALTER SSSSION SET CONSTRAINT xxx deferred;

IMMEDIATE语句级：Oracle performs its constraint checking after the statement has been completely executed.

DEFERRED(事务级)：A constraint is deferred if the system checks that it is satisfied only on commit. If a deferred constraint is violated,then commit causes the transaction to rollback;(推迟到事务提交时才检查约束,谨慎使用)

4、查询约束

1、查询主键
select con.owner,
       con.constraint_name,
       con.constraint_type,
       con.table_name,
       col.column_name,
       con.status,
       con.last_change
  from user_constraints con, user_cons_columns col
 where con.constraint_name = col.constraint_name
   and con.constraint_type = 'P';
2、查询表外键
select u.owner,u.table_name,'alter table ' || table_name || ' disable constraint ' ||constraint_name || ';',u.status from user_constraints u 
where constraint_type = 'R' and table_name = '&table_name';

3、查询表与表之间的主外键关系

select a.owner "主键拥有者",
       a.table_name "主键表",
       b.column_name "主键列",
       C.OWNER "外键拥有者",
       c.table_name "外键表",
       d.column_name "外键列",
       C.constraint_name "约束名",
       'alter table ' || C.table_name || ' disable constraint ' ||
       C.constraint_name || ';' "禁用外键约束"
  from user_constraints a
  left join user_cons_columns b on a.constraint_name = b.constraint_name
  left join user_constraints C ON C.R_CONSTRAINT_NAME = a.constraint_name
  left join user_cons_columns d on c.constraint_name = d.constraint_name
 where a.constraint_type = 'P'
   and a.table_name = 'BULK_PLAN_MILESTONE'
 order by a.table_name;

5、ORA-02298: cannot validate (TEST.FK_AN_ID) - parent keys not found

select * from "TEST"."ROWER_INFO" a where not exists ( select 1 from "TEST"."AN_REQUEST" b where b.ID=a.LOAN_ID);
delete from "TEST"."ROWER_INFO" a where not exists ( select 1 from "TEST"."AN_REQUEST" b where b.ID=a.LOAN_ID);
ALTER TABLE "TEST"."ROWER_INFO" ADD CONSTRAINT "FK_AN_ID" FOREIGN KEY ("LOAN_ID") REFERENCES "TEST"."AN_REQUEST" ("ID") ENABLE;

---获取删除多余数据的SQL：
SELECT ' delete from '
||a.table_name
||' a where not exists ( select 1 from '
||c_pk.table_name
|| ' b where b.'
|| b.column_name
||'=a.'
||a.column_name
||');'
FROM user_cons_columns a
JOIN user_constraints c
ON a.constraint_name = c.constraint_name
JOIN user_constraints c_pk
ON c.r_constraint_name = c_pk.constraint_name
JOIN user_cons_columns b
ON c_pk.constraint_name = b.constraint_name
WHERE c.constraint_type = 'R'
AND a.table_name = '&Table_Name'
AND a.constraint_name ='&FK_NAME';

5、scripts

删除所有外键约束
select 'alter table '||table_name||' drop constraint '||constraint_name||';' from user_constraints where constraint_type='R'

禁用所有外键约束
select 'alter table '||table_name||' disable constraint '||constraint_name||';' from user_constraints where constraint_type='R'

启用所有外键约束
select 'alter table '||table_name||' enable constraint '||constraint_name||';' from user_constraints where constraint_type='R';

先不验证已有数据的CONSTRANT,加上参数NOVALIDATE.*/

ALTER TABLE PN_POST ADD CONSTRAINT POST_CLOB_FK FOREIGN KEY (POST_BODY_ID) REFERENCES PN_POST_BODY_CLOB (OBJECT_ID) NOVALIDATE;

/*启用约束:
enable( validate) :启用约束,创建索引,对已有及新加入的数据执行约束.
enable novalidate :启用约束,创建索引,仅对新加入的数据强制执行约束,而不管表中的现有数据.

禁用约束:
disable( novalidate):关闭约束,删除索引,可以对约束列的数据进行修改等操作.
disable validate :关闭约束,删除索引,不能对表进行插入/更新/删除等操作.*/

posted @ 2021-09-10 18:13 harrison辉阅读(293) 评论(0) 收藏举报

刷新页面返回顶部

DBA日常

约束

公告