約束是表中列的屬性，用來維護(hù)數(shù)據(jù)結(jié)構(gòu)完整性的一種手段

約束的種類：

NOT NULL

UNIQUE

PARIAMRY KEY

FOREIGN KEY

CHECK

enble validate 檢查現(xiàn)有數(shù)據(jù)和新數(shù)據(jù)是否符合約束

enable novlidate 檢查新數(shù)據(jù)是否符合約束

disable validate 給表加鎖

disable novalidate

創(chuàng)建表的時(shí)候帶有約束信息:

create table e (empno number(4) constraint pk_e_empno(約束名) primary key,

ename varchar2(10) not null, not null只能在列級(jí)別

email varchar2(30),

constraint uk_e_email unique (email)); 約束寫在后面為表級(jí)別約束，要指定列名

查看約束信息:

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 約束只能在列級(jí)別指定不能在表級(jí)別指定！

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;

測試數(shù)據(jù)依賴關(guān)系:

delete dept where deptno=40;

刪除約束:

alter table e drop constraint fk_e_deptno;

外鍵約束的兩種特殊情況(主鍵刪除有級(jí)聯(lián))

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;

約束的狀態(tài):

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;

刪除外鍵，重新建立父子關(guān)系(子表為e，父表為d，d表拷貝dept表數(shù)據(jù)):

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);

父子關(guān)系建立后對(duì)父表結(jié)構(gòu)修改的影響:

alter table d drop (deptno);

ORA-12992: cannot drop parent key column

強(qiáng)制刪除父表主鍵:

alter table d drop (deptno) cascade constraints;

總結(jié)

以上是生活随笔為你收集整理的oracle 查看表结构约束,oracle 约束的全部內(nèi)容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網(wǎng)站內(nèi)容還不錯(cuò)，歡迎將生活随笔推薦給好友。