oracle 約束

約束是表中列的屬性，用來維護資料結構完整性的一種手段
約束的種類：
NOT NULL
UNIQUE
PARIAMRY KEY
FOREIGN KEY
CHECK

enble validate 檢查現有資料和新資料是否符合約束
enable novlidate 檢查新資料是否符合約束
disable validate 給表加鎖
disable novalidate

建立表的時候帶有約束資訊:
create table e (empno number(4) constraint pk_e_empno（約束名） primary key,
ename varchar2(10) not null, not null只能在列級別
email varchar2(30),
constraint uk_e_email unique (email)); 約束寫在後面為表級別約束，要指定列名

檢視約束資訊:
select constraint_name,
constraint_type,
SEARCH_CONDITION,
R_CONSTRAINT_NAME
from user_constraints
where table_name='E';

select constraint_name,column_name from user_cons_columns
where table_name='E';

新增約束:
alter table e add (sal number(7,2),deptno number(2));

alter table e add constraint ck_e_sal check (sal>999 and sal is not null);

alter table e add constraint fk_e_deptno foreign key(deptno) references dept (deptno);

注意:not null 約束只能在列級別指定不能在表級別指定！
alter table e modify (ename varchar2(10) constraint nn_e_ename not null);

select table_name,constraint_type from user_constraints where constraint_name='PK_DEPT';

select table_name,column_name from user_cons_columns
where constraint_name='PK_DEPT';

insert into e values (1,'X1','x1@uplooking.com',1000,10);
insert into e values (2,'X2','x2@uplooking.com',2000,20);
insert into e values (3,'X3','x3@uplooking.com',2000,40);
commit;

測試資料依賴關係:
delete dept where deptno=40;

刪除約束:
alter table e drop constraint fk_e_deptno;

外來鍵約束的兩種特殊情況（主鍵刪除有級聯）
alter table e add constraint fk_e_deptno foreign key(deptno) references dept (deptno) on delete set null;

alter table e add constraint fk_e_deptno foreign key(deptno) references dept (deptno) on delete cascade;

約束的狀態:
select constraint_name,
status
from user_constraints
where table_name='E';

禁止約束:
alter table e modify constraint UK_E_EMAIL disable;

啟用約束:
alter table e modify constraint UK_E_EMAIL enable;

刪除外來鍵，重新建立父子關係(子表為e，父表為d，d表複製dept表資料):
alter table e drop constraint fk_e_deptno;

create table d as select * from dept;

alter table d add constraint pk_d_deptno primary key (deptno);

alter table e add constraint fk_e_deptno foreign key(deptno) references d (deptno);

父子關係建立後對父表結構修改的影響:
alter table d drop (deptno);
ORA-12992: cannot drop parent key column

強制刪除父表主鍵:
alter table d drop (deptno) cascade constraints;