oracle 查看表结构约束,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;