管理约束（二）--维护约束

 

1、建表时定义约束，既可以在列级定义，也可以在表级定义。对于NOT NULL约束，只能在列级定义，不能再表级定义。

（1）列级定义

语法：

column   [CONSTRAINT     constraint_name]     constraint_type,

02:01:01 SQL> create table t1 (

02:01:19   2  deptno number(2) constraint pk_deptid primary key,

02:01:25   3  name varchar2(20),

02:01:30   4  loc varchar2(20));

Table created.

02:12:07 SQL> desc t1;

Name         Null?    Type

--------- -------- -----------------

DEPTNO      NOT NULL NUMBER(2)

NAME                          VARCHAR2(20)

LOC                           VARCHAR2(20)

02:12:15 SQL> insert into t1 values (10,'Cisco','BeiJing');

1 row created.

02:12:59 SQL> insert into t1 values (10,'Cisco','BeiJing');

insert into t1 values (10,'Cisco','BeiJing')

*

ERROR at line 1:

ORA-00001: unique constraint (SCOTT.PK_DEPTID) violated

（2）表级定义

语法：

column, … ,

[CONSTRAINT     constraint_name]     constraint_type(column, … ,),

create table t2(

2   empno number(4) ,name varchar2(15),job varchar2(10),

3  manager_id number(4),hire_date date,sal number(7,2),

4  comm number(7,2),deptno number(2),

5  constraint pk_emp_id primary key(empno)

6     using index tablespace users01,

7  constraint fk_dept_id foreign key (deptno)

8    references t1(deptno)）；

Table created.

03:43:32 SQL> select * from t1;

DEPTNO NAME                 LOC

---------- -------------------- --------------------

10 Cisco                BeiJing

03:43:46 SQL> insert into t2 values(7788,'tom','sales','7936',sysdate,3000,0,20);

insert into t2 values(7788,'tom','sales','7936',sysdate,3000,0,20)

*

ERROR at line 1:

ORA-02291: integrity constraint (SCOTT.FK_DEPT_ID) violated - parent key not found

在t1的deptno没有20的，所以插入20的记录出错，参考性约束。

03:44:59 SQL> insert into t2 values(7788,'tom','sales','7936',sysdate,3000,0,10);

1 row created.

03:45:04 SQL> select * from t2;

EMPNO NAME            JOB        MANAGER_ID HIRE_DATE                  SAL       COMM     DEPTNO

---------- --------------- ---------- ---------- ------------------- ---------- ---------- ----------

7788 tom             sales            7936 2011-02-25 03:45:04       3000          0         10

2、建表后增加约束

（1) Not Null

Not null 约束只能用modify去修改。

04:19:22 SQL> alter table test modify ename not null;

Table altered.

（2）unique

04:27:41 SQL> alter table test

04:30:25   2   add constraint uni_ename unique(ename);

Table altered.

04:32:06 SQL> alter table test

04:32:07   2   add constraint uni_ename unique(ename)

04:32:10   3  using index (create index ind_name on test(ename));

add constraint uni_ename unique(ename)

*

ERROR at line 2:

ORA-02261: such unique or primary key already exists in the table

04:39:10 SQL> alter table t1

04:39:17   2   add constraint u_name unique(name)

04:39:20   3  using index (create index ind_name on t1(name));

Table altered.

（3）check

04:42:10 SQL> alter table t1

04:42:13   2   add constraint chk_loc check (loc in ('BeiJing','ShangHai'));

Table altered.

04:42:19 SQL> insert  into t1 values(40,'Microsoft' ,'TianJing');

insert into t1 values(40,'Microsoft' ,'TianJing')

*

ERROR at line 1:

ORA-02290: check constraint (SCOTT.CHK_LOC) violated

04:43:03 SQL> insert into t1 values(40,'Microsoft' ,'ShangHai');

1 row created.

04:43:18 SQL> select * from t1;

DEPTNO NAME                 LOC

---------- -------------------- --------------------

10 Cisco                BeiJing

20 C%ommd               ShangHai

30 Commd                ShangHai

40 Microsoft            ShangHai

3、修改约束名

04:47:47 SQL> alter table t1

04:47:49   2   rename constraint chk_loc to ck_loc;

Table altered.

4、删除约束

04:48:59 SQL> alter table t1

04:49:00   2    drop constraint ck_loc;

Table altered

04:51:01 SQL> select constraint_name,constraint_type,table_name from user_constraints

04:51:42   2    where table_name='T1';

CONSTRAINT_NAME                C TABLE_NAME

------------------------------ - ------------------------------

PK_DEPTID                      P T1

U_NAME                         U T1

04:52:18 SQL> alter table t1

04:52:31   2    drop  primary key cascade;

Table altered.

04:52:37 SQL> select constraint_name,constraint_type,table_name from user_constraints

04:52:42   2    where table_name='T1';

CONSTRAINT_NAME                C TABLE_NAME

------------------------------ - ------------------------------

U_NAME                         U T1