发布日期:2015-11-05 13:29 来源: 标签: 数据库 oracle教程 oracle函数 oracle约束
本章我们主要学习oracle数据库实际应用中常用约束函数有哪些?下面我们就做一下具体讲解,希望大家多多支持中国站长网络学院。
1、General Constraint Information
1)与约束相关的数据字典对象

2)与约束相关的特权
要在不同的模式中创建一个对象的外国关键约束,您必须在父表或视图中的引用键的列上有引用权限。
3)约束类型和代码

4)修改约束
不能改变的约束。他们必须删除并重新创建。通过修改表可以修改一些修改。

2、Definitions
1)禁用
允许输入数据,不管它是否符合约束
2)使
确保所有输入数据符合约束
3)freelists
指定可用索引块列表的数量。如果Oracle忽略该对象所在是自动段空间管理的表空间(ASSM)模式。
4)initrans
指定在每个分配给数据库对象的数据块中分配的并发事务项的初始数量。此值可以从1到255,默认为2。
5)norely
不执行启用的约束
6)作用
验证更改,但未验证表中先前存在的数据
7)pctree
控制更新索引数据块中的自由空间的量。在主键约束的索引中,这将是罕见的,这不被设置为零(0)。
8)依赖
强制执行约束
9)代理键
暴露的定位器如身份不代理键。一个代理完全隐藏于用户,并由系统维护--想想指数是如何工作的。如果你改变了一个自然的关键,其行动将
为你级联。地狱不断。但如果你的自动编号,真正的关键是不同步的,那么你完蛋了。刚进入同一记录的几次得到不同的自动编码重复
行。放下所有的DUPS然后试图找到所有引用其他表中的行。
如果你手上的接触位置,你有额外的工作,额外的磁盘寻找,并最终会犯错误,从而破坏你的数据完整性。你如何验证和验证你的数据?
UPC代码只是从10到13的数字。你是否用一个自动编码或条形码,条形码已经被更新。如果你使用了正确的数据库设计和SQL-92,你会做一个改变域和级联单更新。这是一个不记录的原因之一。这是在SQL Server的一个问题,因为它仍然是基于一个连续的存储模型,但其他产品都没有,所以风用工具编写脚本。
~乔塞科在comp.databases.ms-sqlserver 10 / 13 / 2005
10)表空间
表空间的任何相关指数将建
11)验证
验证以前的数据以及所有的更改

3、Tables For Constraint Demo
表的DDL
CREATE TABLE person (
person_id  NUMBER(10),
last_name  VARCHAR2(30) NOT NULL,
per_age    NUMBER(3),
per_state  VARCHAR2(2),
per_zip    VARCHAR2(5),
ssn        VARCHAR2(11),
status     VARCHAR2(1));

CREATE TABLE uclass (
class_id   NUMBER(7),
class_name VARCHAR2(35),
reg_fee    NUMBER(6,2),
reg_date   DATE);

CREATE TABLE person_uclass_ie (
person_id NUMBER(10),
class_id  NUMBER(7));

CREATE TABLE state_zip (
state    VARCHAR2(2),
zip_code VARCHAR2(5));

INSERT INTO state_zip VALUES ('WA', '98004');
INSERT INTO state_zip VALUES ('WA', '98101');
INSERT INTO state_zip VALUES ('OR', '97405');
INSERT INTO state_zip VALUES ('CA', '94002');
INSERT INTO state_zip VALUES ('NY', '10010');
COMMIT;

4、Primary Key - Type P
1)创建单列主键
ALTER TABLE <table_name>
ADD CONSTRAINT <primary_key_constraint_name>
PRIMARY KEY (<column_name>)
USING INDEX
PCTFREE <percentage of block available for update>
INITRANS <integer>
MAXTRANS <integer>
STORAGE (FREELISTS <integer>)
TABLESPACE <tablespace_name>;
eg:
desc person

SELECT constraint_name, constraint_type
FROM user_constraints
WHERE table_name = 'PERSON';

ALTER TABLE person
ADD CONSTRAINT pk_person
PRIMARY KEY (person_id)
USING INDEX
PCTFREE 0
TABLESPACE uwdata;

desc person

set linesize 121

SELECT constraint_name, constraint_type, index_name, generated, status, deferrable
FROM user_constraints
WHERE table_name = 'PERSON';

-- note a unique index is created 
SELECT index_type, uniqueness, clustering_factor
FROM user_indexes
WHERE table_name = 'PERSON';

exec dbms_stats.gather_index_stats(USER, 'PK_PERSON');

SELECT index_type, uniqueness, clustering_factor
FROM user_indexes
WHERE table_name = 'PERSON';

col column_name format a30

SELECT constraint_name, position, column_name
FROM user_cons_columns
WHERE table_name = 'PERSON';

SELECT index_name, column_position, column_name, descend
FROM user_ind_columns
WHERE table_name = 'PERSON';

INSERT INTO person
(person_id, last_name)
VALUES
(1, 'Morgan');


INSERT INTO person
(person_id, last_name)
VALUES
(2, 'Cline');

INSERT INTO person
(person_id, last_name)
VALUES
(1, 'Lofstrom');

SELECT *
FROM person;
2)创建复合主键
ALTER TABLE <table_name>
ADD CONSTRAINT <primary_key_constraint_name>
PRIMARY KEY <column_name, column_name, ....>
USING INDEX
PCTFREE <percentage of block available for update>
TABLESPACE <tablespace_name>; 
eg:
desc person_uclass_ie

SELECT COUNT(*)
FROM user_constraints
WHERE table_name = 'PERSON_UCLASS_IE';

ALTER TABLE person_uclass_ie
ADD CONSTRAINT pk_person_uclass_ie
PRIMARY KEY (person_id, class_id)
USING INDEX
PCTFREE 0;

desc person_uclass_ie

SELECT constraint_name, constraint_type, index_name, generated, status, deferrable
FROM user_constraints
WHERE table_name = 'PERSON_UCLASS_IE';

-- note a unique index is created 
SELECT index_type, uniqueness, clustering_factor
FROM user_indexes
WHERE table_name = 'PERSON_UCLASS_IE';

exec dbms_stats.gather_index_stats(USER, 'PK_PERSON_UCLASS_IE');

SELECT index_type, uniqueness, clustering_factor
FROM user_indexes
WHERE table_name = 'PERSON_UCLASS_IE';

col column_name format a30

SELECT constraint_name, position, column_name
FROM user_cons_columns
WHERE table_name = 'PERSON_UCLASS_IE'
ORDER BY 2;

SELECT index_name, column_position, column_name, descend
FROM user_ind_columns
WHERE table_name = 'PERSON_UCLASS_IE'
ORDER BY 2;

INSERT INTO person_uclass_ie
(person_id, class_id)
VALUES
(1, 1);

INSERT INTO person_uclass_ie
(person_id, class_id)
VALUES
(2, 1);

INSERT INTO person_uclass_ie
(person_id, class_id)
VALUES
(3, NULL);

INSERT INTO person_uclass_ie
(person_id, class_id)
VALUES
(1, 1);

SELECT *
FROM person_uclass_ie;
3)可创建主键
ALTER TABLE <table_name>
ADD CONSTRAINT <constraint_name>
PRIMARY KEY (<column_name_list>)
DEFERRABLE INITIALLY < IMMEDIATE | DEFERRED >
USING INDEX
PCTFREE <integer>
TABLESPACE <tablespace_name>;
eg:
desc uclass

SELECT COUNT(*)
FROM user_constraints
WHERE table_name = 'UCLASS';

ALTER TABLE uclass
ADD CONSTRAINT pk_uclass
PRIMARY KEY (class_id)
INITIALLY DEFERRED DEFERRABLE
USING INDEX
PCTFREE 0;

desc uclass
-- note NOT NULL not created on class_id

SELECT constraint_name, constraint_type, index_name, generated, status, deferrable
FROM user_constraints
WHERE table_name = 'UCLASS';

-- note a non-unique index is created 
SELECT index_type, uniqueness, clustering_factor
FROM user_indexes
WHERE table_name = 'UCLASS';

col column_name format a30

SELECT constraint_name, position, column_name
FROM user_cons_columns
WHERE table_name = 'UCLASS';

SELECT index_name, column_position, column_name, descend
FROM user_ind_columns
WHERE table_name = 'UCLASS';

INSERT INTO uclass
(class_id)
VALUES
(1);

INSERT INTO uclass
(class_id)
VALUES
(2);

INSERT INTO uclass
(class_id)
VALUES
(1);

SELECT *
FROM uclass;

COMMIT;

SELECT *
FROM uclass;
4)禁用和启用主键约束
ALTER TABLE <table_name> DISABLE PRIMARY KEY;
eg:
CREATE TABLE t (
rid NUMBER(5));

ALTER TABLE t
ADD CONSTRAINT pk_t
PRIMARY KEY (rid)
USING INDEX
PCTFREE 0;

SELECT index_name, index_type, uniqueness, pct_free
FROM user_indexes
WHERE table_name = 'T';

ALTER TABLE t DISABLE PRIMARY KEY;

SELECT index_name, index_type, uniqueness
FROM user_indexes
WHERE table_name = 'T';

ALTER TABLE t ENABLE PRIMARY KEY;

SELECT index_name, index_type, uniqueness, pct_free
FROM user_indexes
WHERE table_name = 'T';
5)禁用验证使表只读
ALTER TABLE <table_name> MODIFY CONSTRAINT <constraint_name>
DISABLE VALIDATE
eg:
SELECT constraint_name, status, validated
FROM user_constraints;

ALTER TABLE uclass MODIFY CONSTRAINT pk_uclass
DISABLE VALIDATE;

SELECT constraint_name, status, validated
FROM user_constraints
WHERE constraint_type = 'P';

INSERT INTO uclass (class_id) VALUES (201);

COMMIT;
6)启用主键,而不验证预先存在的数据
ALTER TABLE <table_name> ENABLE NOVALIDATE PRIMARY KEY;
eg:
ALTER TABLE uclass ENABLE NOVALIDATE PRIMARY KEY;

SELECT constraint_name, status, validated
FROM user_constraints
WHERE constraint_type = 'P';

INSERT INTO uclass (class_id) VALUES (101);

COMMIT;

ALTER TABLE uclass DROP PRIMARY KEY;

INSERT INTO uclass (class_id) VALUES (101);
INSERT INTO uclass (class_id) VALUES (101);
INSERT INTO uclass (class_id) VALUES (101);
COMMIT;

SELECT *
FROM uclass;

ALTER TABLE uclass
ADD CONSTRAINT pk_uclass
PRIMARY KEY (class_id)
INITIALLY IMMEDIATE DEFERRABLE
NOVALIDATE;

SELECT constraint_name, status, validated
FROM user_constraints
WHERE constraint_type = 'P';

INSERT INTO uclass (class_id) VALUES (1);
COMMIT;

INSERT INTO uclass (class_id) VALUES (101);
COMMIT;

DELETE FROM uclass
WHERE class_id = 101
AND rownum < 4;

COMMIT; 

5、Unique - Type U
1)在单一列上创建唯一约束
ALTER TABLE <table_name>
ADD CONSTRAINT <constraint_name>
UNIQUE (<column_name>)
USING INDEX
PCTFREE <pct of block available for update>
TABLESPACE <tablespace_name>
eg:
ALTER TABLE state_zip
ADD CONSTRAINT uc_state_zip_state
UNIQUE (state);

DELETE state_zip
WHERE ROWID IN (
  SELECT LEAD(ROWID)
  OVER (PARTITION BY state ORDER BY NULL)
FROM state_zip);

ALTER TABLE state_zip
ADD CONSTRAINT uc_state_zip_state
UNIQUE (state);

SELECT constraint_name, constraint_type
FROM user_constraints
WHERE table_name = 'STATE_ZIP';

col column_name format a30

SELECT constraint_name, column_name, position
FROM user_cons_columns
ORDER BY constraint_name, position;

SELECT * FROM state_zip;

INSERT INTO state_zip
(state, zip_code)
VALUES
('WA', '98004');
2)使用索引子句创建唯一约束
ALTER TABLE <table_name>
ADD CONSTRAINT <primary_key_constraint_name>
UNIQUE <column_name, column_name, ....>
USING INDEX
PCTFREE <pct of block available for update>
TABLESPACE <tablespace_name>;
eg:
ALTER TABLE state_zip
DROP CONSTRAINT uc_state_zip_state;

ALTER TABLE state_zip
ADD CONSTRAINT uc_state_zip_state
UNIQUE (state)
USING INDEX
PCTFREE 0
TABLESPACE uwdata;

SELECT constraint_name, constraint_type
FROM user_constraints
WHERE table_name = 'STATE_ZIP';

SELECT index_name
FROM user_indexes
WHERE table_name = 'STATE_ZIP';
6、Referential - Type R (Foreign Key)
1)创建对外键的单栏
ALTER TABLE <table_name>
ADD CONSTRAINT <constraint_name>
FOREIGN KEY (<column_name>)
REFERENCING <table_name> (<column_name>)
DEFERRABLE INITIALLY < IMMEDIATE | DEFERRED >;
eg:
ALTER TABLE person_uclass_ie
ADD CONSTRAINT fk_person_uclass_person_id
FOREIGN KEY (person_id)
REFERENCING person (person_id)
INITIALLY DEFERRED DEFERRABLE;

ALTER TABLE person
ADD CONSTRAINT pk_person
PRIMARY KEY (person_id)
INITIALLY DEFERRED DEFERRABLE
USING INDEX
PCTFREE 0;

ALTER TABLE person_uclass_ie
ADD CONSTRAINT fk_person_uclass_person_id
FOREIGN KEY (person_id)
REFERENCING person (person_id)
INITIALLY DEFERRED DEFERRABLE;

SELECT constraint_name, constraint_type, status
FROM user_constraints
WHERE table_name = 'PERSON_UCLASS_IE';

SELECT t.owner CHILD_OWNER,
t.table_name CHILD_TABLE,
t.constraint_name FOREIGN_KEY_NAME,
r.owner PARENT_OWNER,
r.table_name PARENT_TABLE,
r.constraint_name PARENT_CONSTRAINT
FROM user_constraints t, user_constraints r
WHERE t.r_constraint_name = r.constraint_name
AND t.r_owner = r.owner
AND t.constraint_type='R'
AND t.table_name = 'PERSON_UCLASS_IE';

-- check for index on FK
set linesize 121
col status format a6
col columns format a30 word_wrapped
col table_name format a30 word_wrapped

SELECT DECODE(b.table_name, NULL, '****', 'Ok' ) STATUS, a.table_name, a.columns, b.columns from (
  SELECT SUBSTR(a.table_name,1,30) table_name,
  SUBSTR(a.constraint_name,1,30) constraint_name, MAX(DECODE(position, 1,
  SUBSTR(column_name,1,30),NULL)) || MAX(DECODE(position, 2,', '|| SUBSTR(column_name,1,30),NULL)) || max(DECODE(position, 3,', '|| SUBSTR(column_name,1,30),NULL)) || max(DECODE(position, 4,', '|| SUBSTR(column_name,1,30),NULL)) || max(DECODE(position, 5,', '|| SUBSTR(column_name,1,30),NULL)) || max(DECODE(position, 6,', '|| SUBSTR(column_name,1,30),NULL)) || max(DECODE(position, 7,', '|| SUBSTR(column_name,1,30),NULL)) || max(DECODE(position, 8,', '|| SUBSTR(column_name,1,30),NULL)) || max(DECODE(position, 9,', '|| SUBSTR(column_name,1,30),NULL)) || max(DECODE(position,10,', '|| SUBSTR(column_name,1,30),NULL)) || max(DECODE(position,11,', '|| SUBSTR(column_name,1,30),NULL)) || max(DECODE(position,12,', '|| SUBSTR(column_name,1,30),NULL)) || max(DECODE(position,13,', '|| SUBSTR(column_name,1,30),NULL)) || max(DECODE(position,14,', '|| SUBSTR(column_name,1,30),NULL)) || max(DECODE(position,15,', '|| SUBSTR(column_name,1,30),NULL)) || max(DECODE(position,16,', '|| SUBSTR(column_name,1,30),NULL)) columns from user_cons_columns a, user_constraints b 
WHERE a.constraint_name = b.constraint_name
AND constraint_type = 'R'
GROUP BY SUBSTR(a.table_name,1,30), SUBSTR(a.constraint_name,1,30) ) a, (
  SELECT SUBSTR(table_name,1,30) table_name, 
  SUBSTR(index_name,1,30) index_name, MAX(DECODE(column_position, 1,
  SUBSTR(column_name,1,30),NULL)) || MAX(DECODE(column_position, 2,', '||SUBSTR(column_name,1,30),NULL)) || max(DECODE(column_position, 3,', '||SUBSTR(column_name,1,30),NULL)) || max(DECODE(column_position, 4,', '||SUBSTR(column_name,1,30),NULL)) || max(DECODE(column_position, 5,', '||SUBSTR(column_name,1,30),NULL)) || max(DECODE(column_position, 6,', '||SUBSTR(column_name,1,30),NULL)) || max(DECODE(column_position, 7,', '||SUBSTR(column_name,1,30),NULL)) || max(DECODE(column_position, 8,', '||SUBSTR(column_name,1,30),NULL)) || max(DECODE(column_position, 9,', '||SUBSTR(column_name,1,30),NULL)) || max(DECODE(column_position,10,', '||SUBSTR(column_name,1,30),NULL)) || max(DECODE(column_position,11,', '||SUBSTR(column_name,1,30),NULL)) || max(DECODE(column_position,12,', '||SUBSTR(column_name,1,30),NULL)) || max(DECODE(column_position,13,', '||SUBSTR(column_name,1,30),NULL)) || max(DECODE(column_position,14,', '||SUBSTR(column_name,1,30),NULL)) || max(DECODE(column_position,15,', '||SUBSTR(column_name,1,30),NULL)) || max(DECODE(column_position,16,', '||SUBSTR(column_name,1,30),NULL)) columns from user_ind_columns group by SUBSTR(table_name,1,30), SUBSTR(index_name,1,30) ) b where a.table_name = b.table_name (+) and b.columns (+) like a.columns || '%';

-- FK should be indexed to prevent deadlocks
CREATE INDEX ix_puie_person_id
ON person_uclass_ie (person_id);

-- Verify FK problem addressed
SELECT DECODE(b.table_name, NULL, '****', 'Ok' ) STATUS, a.table_name, a.columns, b.columns from (
  SELECT SUBSTR(a.table_name,1,30) table_name,
  SUBSTR(a.constraint_name,1,30) constraint_name, MAX(DECODE(position, 1,
  SUBSTR(column_name,1,30),NULL)) || MAX(DECODE(position, 2,', '|| SUBSTR(column_name,1,30),NULL)) || max(DECODE(position, 3,', '|| SUBSTR(column_name,1,30),NULL)) || max(DECODE(position, 4,', '|| SUBSTR(column_name,1,30),NULL)) || max(DECODE(position, 5,', '|| SUBSTR(column_name,1,30),NULL)) || max(DECODE(position, 6,', '|| SUBSTR(column_name,1,30),NULL)) || max(DECODE(position, 7,', '|| SUBSTR(column_name,1,30),NULL)) || max(DECODE(position, 8,', '|| SUBSTR(column_name,1,30),NULL)) || max(DECODE(position, 9,', '|| SUBSTR(column_name,1,30),NULL)) || max(DECODE(position,10,', '|| SUBSTR(column_name,1,30),NULL)) || max(DECODE(position,11,', '|| SUBSTR(column_name,1,30),NULL)) || max(DECODE(position,12,', '|| SUBSTR(column_name,1,30),NULL)) || max(DECODE(position,13,', '|| SUBSTR(column_name,1,30),NULL)) || max(DECODE(position,14,', '|| SUBSTR(column_name,1,30),NULL)) || max(DECODE(position,15,', '|| SUBSTR(column_name,1,30),NULL)) || max(DECODE(position,16,', '|| SUBSTR(column_name,1,30),NULL)) columns from user_cons_columns a, user_constraints b 
WHERE a.constraint_name = b.constraint_name
AND constraint_type = 'R'
GROUP BY SUBSTR(a.table_name,1,30), SUBSTR(a.constraint_name,1,30) ) a, (
  SELECT SUBSTR(table_name,1,30) table_name, 
  SUBSTR(index_name,1,30) index_name, MAX(DECODE(column_position, 1,
  SUBSTR(column_name,1,30),NULL)) || MAX(DECODE(column_position, 2,', '||SUBSTR(column_name,1,30),NULL)) || max(DECODE(column_position, 3,', '||SUBSTR(column_name,1,30),NULL)) || max(DECODE(column_position, 4,', '||SUBSTR(column_name,1,30),NULL)) || max(DECODE(column_position, 5,', '||SUBSTR(column_name,1,30),NULL)) || max(DECODE(column_position, 6,', '||SUBSTR(column_name,1,30),NULL)) || max(DECODE(column_position, 7,', '||SUBSTR(column_name,1,30),NULL)) || max(DECODE(column_position, 8,', '||SUBSTR(column_name,1,30),NULL)) || max(DECODE(column_position, 9,', '||SUBSTR(column_name,1,30),NULL)) || max(DECODE(column_position,10,', '||SUBSTR(column_name,1,30),NULL)) || max(DECODE(column_position,11,', '||SUBSTR(column_name,1,30),NULL)) || max(DECODE(column_position,12,', '||SUBSTR(column_name,1,30),NULL)) || max(DECODE(column_position,13,', '||SUBSTR(column_name,1,30),NULL)) || max(DECODE(column_position,14,', '||SUBSTR(column_name,1,30),NULL)) || max(DECODE(column_position,15,', '||SUBSTR(column_name,1,30),NULL)) || max(DECODE(column_position,16,', '||SUBSTR(column_name,1,30),NULL)) columns from user_ind_columns group by SUBSTR(table_name,1,30), SUBSTR(index_name,1,30) ) b where a.table_name = b.table_name (+) and b.columns (+) like a.columns || '%';
SELECT * FROM person;
SELECT * FROM person_uclass_ie;

INSERT INTO person_uclass_ie
(person_id, class_id)
VALUES
(2, 202);
COMMIT;

INSERT INTO person_uclass_ie
(person_id, class_id)
VALUES
(3, 202);
COMMIT;
SELECT * FROM person_uclass_ie;
INSERT INTO person
(person_id, last_name)
VALUES
(3, 'Havemeyer');

INSERT INTO person_uclass_ie
(person_id, class_id)
VALUES
(3, 202);
COMMIT;

SELECT * FROM person;
SELECT * FROM person_uclass_ie;
2)用复合外键
ALTER TABLE <table_name>
ADD CONSTRAINT <constraint_name>
FOREIGN KEY (<column_name, column_name, ....>)
REFERENCING <table_name> (<column_name,column_name,....>);
eg:
ALTER TABLE state_zip
ADD CONSTRAINT pk_state_zip
PRIMARY KEY (state, zip_code)
USING INDEX
PCTFREE 0;

ALTER TABLE person
ADD CONSTRAINT fk_person_state_zip
FOREIGN KEY (per_state, per_zip)
REFERENCES state_zip (state, zip_code);

col column_name format a30

SELECT constraint_name, column_name, position
FROM user_cons_columns
WHERE table_name = 'PERSON'
ORDER BY 1,3;

UPDATE person
SET per_state = 'WA', per_zip = '98004'
WHERE person_id = 1;

UPDATE person
SET per_state = 'WA', per_zip = '98005'
WHERE person_id = 2;
3)可创建外键
ALTER TABLE <table_name>
ADD CONSTRAINT <constraint_name>
FOREIGN KEY (<column_name, column_name, ....>)
REFERENCING <table_name> (<column_name,column_name,....>)
INITIALLY DEFERRED DEFERRABLE;
eg:
ALTER TABLE person
DROP CONSTRAINT fk_person_state_zip;

ALTER TABLE person
ADD CONSTRAINT fk_person_state_zip
FOREIGN KEY (per_state, per_zip)
REFERENCES state_zip (state, zip_code)
INITIALLY DEFERRED DEFERRABLE;

UPDATE person
SET per_state = 'WA', per_zip = '98004'
WHERE person_id = 1;

UPDATE person
SET per_state = 'WA', per_zip = '98005'
WHERE person_id = 2;

COMMIT;
4)设置约束
SET CONSTRAINTS <IMMEDIATE | DEFERRED>;
eg:
SELECT constraint_name, constraint_type, deferrable, deferred
FROM user_constraints
WHERE table_name = 'PERSON';

SET CONSTRAINTS ALL IMMEDIATE;

SELECT constraint_name, constraint_type, deferrable, deferred
FROM user_constraints
WHERE table_name = 'PERSON';

SELECT * FROM person;

INSERT INTO person
(person_id, last_name, per_age, per_state, per_zip)
VALUES
(2, 'Cline', 57, 'WA', '98005');

SET CONSTRAINTS ALL DEFERRED;

SELECT constraint_name, constraint_type, deferrable, deferred
FROM user_constraints
WHERE table_name = 'PERSON';

INSERT INTO person
(person_id, last_name, per_age, per_state, per_zip)
VALUES
(2, 'Cline', 57, 'WA', '98005');

COMMIT;
5)禁用约束
ALTER TABLE <table_name>
DISABLE CONSTRAINT <constraint_name>;
eg:
ALTER TABLE person
DISABLE CONSTRAINT fk_person_state_zip;

INSERT INTO person
(person_id, last_name, per_age, per_state, per_zip)
VALUES
(2, 'Cline', 57, 'WA', '98005');

INSERT INTO person
(person_id, last_name, per_age, per_state, per_zip)
VALUES
(2, 'Cline', 57, 'OR', '98005');

INSERT INTO person
(person_id, last_name, per_age, per_state, per_zip)
VALUES
(2, 'Cline', 57, 'XX', 'ABCDE');

COMMIT;
-- fix the data if the constraint does not re-enable and repeat enable
6)允许约束与例外条款
ALTER TABLE <table_name>
ENABLE CONSTRAINT <constraint_name>;
eg:
ALTER TABLE person
DISABLE CONSTRAINT pk_person;

SELECT constraint_name, status
FROM user_constraints
WHERE table_name = 'PERSON';

@?\rdbms\admin\utlexcpt.sql

desc exceptions

ALTER TABLE person
ENABLE PRIMARY KEY
EXCEPTIONS INTO exceptions;

SELECT * FROM exceptions;

ALTER TABLE person
ENABLE CONSTRAINT fk_person_state_zip
EXCEPTIONS INTO exceptions;

SELECT * FROM exceptions;
7)删除级联
ADD CONSTRAINT <constraint_name>
FOREIGN KEY (<column_name>)
REFERENCES <table_name> (<column_name>)
ON DELETE CASCADE;
eg:
INSERT INTO person
(person_id, last_name, per_age, per_state, per_zip)
VALUES
(1, 'Morgan', 54, 'WA', '98004');

INSERT INTO person
(person_id, last_name, per_age, per_state, per_zip)
VALUES
(2, 'Cline', 57, 'NY', '10010');
COMMIT;

ALTER TABLE person
ADD CONSTRAINT fkocd_person_state_zip
FOREIGN KEY (per_state, per_zip)
REFERENCES state_zip (state, zip_code)
ON DELETE CASCADE;

SELECT constraint_name, delete_rule
FROM user_constraints;

SELECT * FROM person;

SELECT * FROM state_zip;

DELETE FROM state_zip
WHERE zip_code = '10010';
8)删除设置为空
ALTER TABLE <table_name>
ADD CONSTRAINT <constraint_name>
FOREIGN KEY (<column_name>)
REFERENCES <table_name> (<column_name>)
ON DELETE SET NULL;
eg:
ALTER TABLE person
DROP CONSTRAINT fkocd_person_state_zip;

ALTER TABLE person
ADD CONSTRAINT fkdsn_person_state_zip
FOREIGN KEY (per_state, per_zip)
REFERENCES state_zip (state, zip_code)
ON DELETE SET NULL;

SELECT constraint_name, delete_rule
FROM user_constraints;

SELECT * FROM person;

SELECT * FROM state_zip;

DELETE FROM state_zip
WHERE zip_code = '98004';

SELECT * FROM state_zip;

SELECT * FROM person;
7、Check - Type C
1)创建与等于
ALTER TABLE <table_name>
ADD CONSTRAINT <constraint_name>
CHECK (<column_name> = <condition>);
eg:
 ALTER TABLE <table_name>
ADD CONSTRAINT <constraint_name>
CHECK (<column_name> = <condition>); 
col search_condition format a40

SELECT table_name, constraint_name, search_condition
FROM user_constraints
WHERE constraint_type = 'C';

desc person

ALTER TABLE person
ADD CONSTRAINT cc_person_status
CHECK (status = 'X');

SELECT constraint_name, constraint_type, search_condition
FROM user_constraints
WHERE table_name = 'PERSON';

UPDATE person
SET status = 'X'
WHERE person_id = 1;

UPDATE person
SET status = NULL
WHERE person_id = 2;

UPDATE person
SET status = 'Z'
WHERE person_id = 3;

ALTER TABLE person
DROP CONSTRAINT cc_person_status; 
2)创建检查约束,不等于
ALTER TABLE
ADD CONSTRAINT <constraint_name>
CHECK (<column_name> != <value>);
eg:
ALTER TABLE person
ADD CONSTRAINT cc_person_status
CHECK (status != 'X');

UPDATE person
SET status = NULL;

ALTER TABLE person
ADD CONSTRAINT cc_person_status
CHECK (status != 'X');

SELECT constraint_name, constraint_type, search_condition
FROM user_constraints
WHERE table_name = 'PERSON';

UPDATE person
SET status = 'A'
WHERE person_id = 1;

UPDATE person
SET status = '4'
WHERE person_id = 2;

UPDATE person
SET status = 'X'
WHERE person_id = 3;
3)创建检查约束
ALTER TABLE <table_name>
ADD CONSTRAINT <constraint_name>
CHECK (<column_name> LIKE <condition>);
eg:
ALTER TABLE person
ADD CONSTRAINT cc_person_ssn
CHECK (ssn LIKE '___-__-____');

SELECT constraint_name, constraint_type, search_condition
FROM user_constraints
WHERE table_name = 'PERSON';

UPDATE person
SET ssn = '333-22-4444'
WHERE person_id = 1;

UPDATE person
SET ssn = '123-45-6789'
WHERE person_id = 2;

UPDATE person
SET ssn = 'Oops'
WHERE person_id = 3;

ALTER TABLE uclass
ADD CONSTRAINT cc_uclass_class_name
CHECK (class_name LIKE 'Ora%');

SELECT constraint_name, constraint_type, search_condition
FROM user_constraints
WHERE table_name = 'UCLASS';

INSERT INTO uclass VALUES (101, 'Oracle');
INSERT INTO uclass VALUES (201, 'Orxcle');
4)创建检查约束,不喜欢
ALTER TABLE <table_name>
ADD CONSTRAINT <constraint_name>
CHECK (<column_name> NOT LIKE <condition>);
eg:
ALTER TABLE uclass
ADD CONSTRAINT cc_uclass_class_name
CHECK (class_name NOT LIKE '%O%');

SELECT constraint_name, constraint_type, search_condition
FROM user_constraints
WHERE table_name = 'UCLASS';

INSERT INTO uclass VALUES (101, 'Basic Oracle');
INSERT INTO uclass VALUES (201, 'Oracle SQL');
INSERT INTO uclass VALUES (301, 'oracle SQL');
5)创建检查约束
ALTER TABLE <table_name>
ADD CONSTRAINT <constraint_name>
CHECK (<column_name> 
IN (<comma delimited list of values>);
eg:
ALTER TABLE person DROP CONSTRAINT cc_person_status;

UPDATE person
SET status = NULL;

ALTER TABLE person
ADD CONSTRAINT cc_person_status
CHECK (status IN ('N', 'Y'));

SELECT constraint_name, constraint_type, search_condition
FROM user_constraints
WHERE table_name = 'PERSON';

UPDATE person
SET status = 'Y'
WHERE person_id = 1;

UPDATE person
SET status = 'N'
WHERE person_id = 1;

UPDATE person
SET status = 'y'
WHERE person_id = 1;
6)创建与不在
ALTER TABLE <table_name>
ADD CONSTRAINT <constraint_name>
CHECK (<column_name> 
NOT IN (<comma delimited list of values>);
eg:
ALTER TABLE person DROP CONSTRAINT cc_person_status;

UPDATE person
SET status = NULL;

ALTER TABLE person
ADD CONSTRAINT cc_person_status
CHECK (status NOT IN ('A','B','C','D'));

SELECT constraint_name, constraint_type, search_condition
FROM user_constraints
WHERE table_name = 'PERSON';

UPDATE person
SET status = 'A'
WHERE person_id = 1;

UPDATE person
SET status = 'D'
WHERE person_id = 1;

UPDATE person
SET status = 'E'
WHERE person_id = 1;
7)创建检查约束
ALTER TABLE <table_name>
ADD CONSTRAINT <constraint_name>
CHECK (<column_name> BETWEEN <lower_value>
AND <higher_value>);
eg:
ALTER TABLE person
ADD CONSTRAINT cc_person_age
CHECK (per_age BETWEEN 18 AND 60);

SELECT constraint_name, constraint_type, search_condition
FROM user_constraints
WHERE table_name = 'PERSON';

UPDATE person
SET per_age = 57
WHERE person_id = 1;

UPDATE person
SET per_age = 59
WHERE person_id = 2;

UPDATE person
SET per_age = 17
WHERE person_id = 3;
8)用布尔运算符创建检查约束
ALTER TABLE <table_name>
ADD CONSTRAINT <constraint_name>
CHECK (<column_name>) > (<condition>);
eg:
ALTER TABLE person
ADD CONSTRAINT cc_person_age
CHECK (per_age > 30);

UPDATE person
SET per_age = 57
WHERE person_id = 1;

UPDATE person
SET per_age = 27
WHERE person_id = 3;
9)函数可用于检查约束,但不能进行赋值
ALTER TABLE uclass
ADD CONSTRAINT cc_uclass_reg_fee
CHECK (reg_fee =  ROUND(reg_fee,0));

INSERT INTO uclass
(class_id, class_name, reg_fee)
VALUES
(401, 'Check Constraints', 23.0);

INSERT INTO uclass
(class_id, class_name, reg_fee)
VALUES
(401, 'Check Constraints', 23.1);

-- SYSDATE, SYSTIMESTAMP, and USER can not be used in a check constraint
ALTER TABLE uclass
ADD CONSTRAINT cc_uclass_reg_date
CHECK (reg_date > SYSDATE);
8、ALTER CONSTRAINT
修改约束语法不存在。
9、DROP CONSTRAINT
1)通用约束降。将删除任何约束名称
ALTER TABLE <table_name>
DROP CONSTRAINT <primary_key_constraint_name>; 
eg:
SELECT constraint_name, table_name
FROM user_constraints;

ALTER TABLE person DROP CONSTRAINT pk_person;

SELECT constraint_name, table_name
FROM user_constraints; 
2)具体关键滴
ALTER TABLE <table_name> DROP PRIMARY KEY 
eg:
ALTER TABLE uclass DROP PRIMARY KEY;

SELECT constraint_name, table_name
FROM user_constraints
WHERE constraint_type = 'P'; 
3)指数在PK滴滴的演示
conn / as sysdba

GRANT select ON ind$ TO uwclass;

conn uwclass/uwclass

CREATE TABLE t (
idcol NUMBER(5),
chcol VARCHAR2(5));

ALTER TABLE t
ADD CONSTRAINT pk_t
PRIMARY KEY(idcol)
USING INDEX
PCTFREE 0;

SELECT constraint_name, table_name, index_name
FROM user_constraints
WHERE constraint_type = 'P';

col object_name format a30

SELECT object_id, object_name
FROM user_objects
WHERE object_type = 'INDEX';

SELECT property
FROM sys.ind$
WHERE obj# = 63069;
-- property = 4097

ALTER TABLE t DROP PRIMARY KEY;

SELECT table_name, index_name
FROM user_indexes
ORDER BY 1;

CREATE UNIQUE INDEX ix_t
ON t(idcol);

ALTER TABLE t
ADD CONSTRAINT pk_t
PRIMARY KEY(idcol)
USING INDEX;

SELECT constraint_name, table_name, index_name
FROM user_constraints
WHERE constraint_type = 'P';

SELECT object_id, object_name
FROM user_objects
WHERE object_type = 'INDEX';

SELECT property
FROM sys.ind$
WHERE obj# = 63070;
-- property = 1

ALTER TABLE t DROP PRIMARY KEY;

SELECT table_name, index_name
FROM user_indexes
ORDER BY 1;
4)具体独特的约束降
ALTER TABLE DROP UNIQUE (<column_name>); 
eg:
ALTER TABLE state_zip
ADD CONSTRAINT uc_state_zip_state
UNIQUE (state)
USING INDEX
PCTFREE 0;

ALTER TABLE state_zip DROP UNIQUE (state); 
5)与依赖关系下降的唯一约束
ALTER TABLE <table_name>
DROP CONSTRAINT <constraint_name>
CASCADE;
eg:
ALTER TABLE state_zip
ADD CONSTRAINT uc_state_zip_state
UNIQUE (state, zip_code)
USING INDEX
PCTFREE 0;

ALTER TABLE person
ADD CONSTRAINT fk_person_state_zip
FOREIGN KEY (per_state, per_zip)
REFERENCES state_zip (state, zip_code);

ALTER TABLE state_zip DROP CONSTRAINT uc_state_zip_state;

ALTER TABLE state_zip DROP CONSTRAINT uc_state_zip_state CASCADE;
6)删除外键
ALTER TABLE <table_name>
DROP CONSTRAINT <constraint_name>; 
eg:
ALTER TABLE person
DROP CONSTRAINT fk_person_state_zip; 
7)用一个外来密钥依赖性降主键
ALTER TABLE <table_name>
DROP CONSTRAINT <primary_key_constraint_name>
CASCADE CONSTRAINTS; 
eg:
ALTER TABLE state_zip DROP PRIMARY KEY;

ALTER TABLE state_zip DROP PRIMARY KEY CASCADE;

SELECT table_name, constraint_name, constraint_type
FROM user_constraints; 
8)带外键约束的表降
DROP TABLE <table_name> CASCADE CONSTRAINTS [PURGE]; 
eg:
SELECT table_name, constraint_name, r_constraint_name
FROM user_constraints
WHERE constraint_type = 'R';

SELECT table_name
FROM user_constraints
WHERE constraint_name = 'PK_STATE_ZIP';

DROP TABLE state_zip;

DROP TABLE state_zip CASCADE CONSTRAINTS;

SELECT object_name, original_name, type
FROM recyclebin;

FLASHBACK TABLE state_zip TO BEFORE DROP;

SELECT constraint_name, constraint_type
FROM user_constraints
WHERE table_name = 'STATE_ZIP'; 
10、 Rename Constraint
重命名约束
ALTER TABLE <table_name>
RENAME CONSTRAINT <current_constraint_name>
TO <new_constraint_name>; 
eg:
SELECT constraint_name
FROM user_constraints;

ALTER TABLE state_zip
RENAME CONSTRAINT "BIN$HY86N3B2RQi/1ODAiR9CTw==$0"
TO pk_state_zip;

SELECT constraint_name
FROM user_constraints; 
11、Constraint Related Queries 
1)子表的引用约束及其关联父表。
SELECT t.owner CHILD_OWNER,
t.table_name CHILD_TABLE,
t.constraint_name FOREIGN_KEY_NAME,
r.owner PARENT_OWNER,
r.table_name PARENT_TABLE,
r.constraint_name PARENT_CONSTRAINT
FROM user_constraints t, user_constraints r
WHERE t.r_constraint_name = r.constraint_name
AND t.r_owner = r.owner
AND t.constraint_type='R'
AND t.table_name = <child_table_name>; 
2)列表键和引用表和列
SELECT DECODE(c.status,'ENABLED','C','c') t,
SUBSTR(c.constraint_name,1,31) relation,
SUBSTR(cc.column_name,1,24) columnname,
SUBSTR(p.table_name,1,20) tablename
FROM all_cons_columns cc, all_constraints p,
     all_constraints c
WHERE c.owner = upper('UWCLASS')
AND c.table_name = upper('PERSON')
AND c.constraint_type = 'R'
AND p.owner = c.r_owner
AND p.constraint_name = c.r_constraint_name
AND cc.owner = c.owner
AND cc.constraint_name = c.constraint_name
AND cc.table_name = c.table_name
UNION ALL
SELECT DECODE(c.status,'ENABLED','P','p') t,
SUBSTR(c.constraint_name,1,31) relation,
SUBSTR(cc.column_name,1,24) columnname,
SUBSTR(c.table_name,1,20) tablename
FROM all_cons_columns cc, all_constraints p,
     all_constraints c
WHERE p.owner = upper('UWCLASS')
AND p.table_name = upper('PERSON')
AND p.constraint_type in ('P','U')
AND c.r_owner = p.owner
AND c.r_constraint_name = p.constraint_name
AND c.constraint_type = 'R'
AND cc.owner = c.owner
AND cc.constraint_name = c.constraint_name
AND cc.table_name = c.table_name
ORDER BY 1, 4, 2, 3
/  

Procedure to DISABLE all  constraints  CREATE OR REPLACE PROCEDURE disable_fk_constra 
3)程序禁用所有约束
CREATE OR REPLACE PROCEDURE disable_fk_constraint IS

CURSOR fke_cur IS
SELECT table_name, constraint_name
FROM user_constraints
WHERE constraint_type = 'R'
AND status = 'ENABLED';

ExStr VARCHAR2(4000);

BEGIN
  FOR fke_rec IN fke_cur LOOP
    ExStr := 'ALTER TABLE ' || fke_rec.table_name ||
             'DISABLE CONSTRAINT ' ||
              fke_rec.constraint_name;
    BEGIN
      EXECUTE IMMEDIATE ExStr;
    EXCEPTION
      WHEN OTHERS THEN
        NULL;
    END;
  END LOOP;
END disable_fk_constraint;

4)找到索引外键
这个脚本被发现在oraqa归因于汤姆Kyte Oracle虽然链接到他的网站不工作。
set linesize 121
col status format a6
col columns format a30 word_wrapped
col table_name format a30 word_wrapped

SELECT DECODE(b.table_name, NULL, '****', 'Ok' ) STATUS, a.table_name, a.columns, b.columns from (
  SELECT SUBSTR(a.table_name,1,30) table_name,
  SUBSTR(a.constraint_name,1,30) constraint_name, MAX(DECODE(position, 1,
  SUBSTR(column_name,1,30),NULL)) || MAX(DECODE(position, 2,', '|| SUBSTR(column_name,1,30),NULL)) || max(DECODE(position, 3,', '|| SUBSTR(column_name,1,30),NULL)) || max(DECODE(position, 4,', '|| SUBSTR(column_name,1,30),NULL)) || max(DECODE(position, 5,', '|| SUBSTR(column_name,1,30),NULL)) || max(DECODE(position, 6,', '|| SUBSTR(column_name,1,30),NULL)) || max(DECODE(position, 7,', '|| SUBSTR(column_name,1,30),NULL)) || max(DECODE(position, 8,', '|| SUBSTR(column_name,1,30),NULL)) || max(DECODE(position, 9,', '|| SUBSTR(column_name,1,30),NULL)) || max(DECODE(position,10,', '|| SUBSTR(column_name,1,30),NULL)) || max(DECODE(position,11,', '|| SUBSTR(column_name,1,30),NULL)) || max(DECODE(position,12,', '|| SUBSTR(column_name,1,30),NULL)) || max(DECODE(position,13,', '|| SUBSTR(column_name,1,30),NULL)) || max(DECODE(position,14,', '|| SUBSTR(column_name,1,30),NULL)) || max(DECODE(position,15,', '|| SUBSTR(column_name,1,30),NULL)) || max(DECODE(position,16,', '|| SUBSTR(column_name,1,30),NULL)) columns from user_cons_columns a, user_constraints b 
WHERE a.constraint_name = b.constraint_name
AND constraint_type = 'R'
GROUP BY SUBSTR(a.table_name,1,30), SUBSTR(a.constraint_name,1,30) ) a, (
  SELECT SUBSTR(table_name,1,30) table_name, 
  SUBSTR(index_name,1,30) index_name, MAX(DECODE(column_position, 1,
  SUBSTR(column_name,1,30),NULL)) || MAX(DECODE(column_position, 2,', '||SUBSTR(column_name,1,30),NULL)) || max(DECODE(column_position, 3,', '||SUBSTR(column_name,1,30),NULL)) || max(DECODE(column_position, 4,', '||SUBSTR(column_name,1,30),NULL)) || max(DECODE(column_position, 5,', '||SUBSTR(column_name,1,30),NULL)) || max(DECODE(column_position, 6,', '||SUBSTR(column_name,1,30),NULL)) || max(DECODE(column_position, 7,', '||SUBSTR(column_name,1,30),NULL)) || max(DECODE(column_position, 8,', '||SUBSTR(column_name,1,30),NULL)) || max(DECODE(column_position, 9,', '||SUBSTR(column_name,1,30),NULL)) || max(DECODE(column_position,10,', '||SUBSTR(column_name,1,30),NULL)) || max(DECODE(column_position,11,', '||SUBSTR(column_name,1,30),NULL)) || max(DECODE(column_position,12,', '||SUBSTR(column_name,1,30),NULL)) || max(DECODE(column_position,13,', '||SUBSTR(column_name,1,30),NULL)) || max(DECODE(column_position,14,', '||SUBSTR(column_name,1,30),NULL)) || max(DECODE(column_position,15,', '||SUBSTR(column_name,1,30),NULL)) || max(DECODE(column_position,16,', '||SUBSTR(column_name,1,30),NULL)) columns from user_ind_columns group by SUBSTR(table_name,1,30), SUBSTR(index_name,1,30) ) b where a.table_name = b.table_name (+) and b.columns (+) like a.columns || '%'; 

相关评论

专题信息
    ORACLE数据库系统是美国ORACLE公司(甲骨文)提供的以分布式数据库为核心的一组软件产品,是目前最流行的客户/服务器(CLIENT/SERVER)或B/S体系结构的数据库之一。比如SilverStream就是基于数据库的一种中间件。ORACLE数据库是目前世界上使用最为广泛的数据库管理系统,作为一个通用的数据库系统,它具有完整的数据管理功能;本教程主要是针对oracle应用过程中我们常用的函数进行深入讲解,让大家能够通过代码实例更快、更有效的掌握oracle函数,希望对大家有所帮助。