- --NOT NULL
- --UNIQUE
- --PRIMARY KEY
- --FOREIGN KEY
- --CHECK
列级别启动约束
SQL> create table t03 (id number(3) not null);
Table created.
SQL> desc user_constraints;
Name Null? Type
----------------------------------------------------------------------------------------------------------------- -------- ----------------------------------------------------------------------------
OWNER VARCHAR2(128)
CONSTRAINT_NAME NOT NULL VARCHAR2(128)
CONSTRAINT_TYPE VARCHAR2(1)
TABLE_NAME NOT NULL VARCHAR2(128)
SEARCH_CONDITION LONG
SEARCH_CONDITION_VC VARCHAR2(4000)
R_OWNER VARCHAR2(128)
R_CONSTRAINT_NAME VARCHAR2(128)
DELETE_RULE VARCHAR2(9)
STATUS VARCHAR2(8)
DEFERRABLE VARCHAR2(14)
DEFERRED VARCHAR2(9)
VALIDATED VARCHAR2(13)
GENERATED VARCHAR2(14)
BAD VARCHAR2(3)
RELY VARCHAR2(4)
LAST_CHANGE DATE
INDEX_OWNER VARCHAR2(128)
INDEX_NAME VARCHAR2(128)
INVALID VARCHAR2(7)
VIEW_RELATED VARCHAR2(14)
ORIGIN_CON_ID NUMBER
SQL>
SQL> select CONSTRAINT_NAME,CONSTRAINT_TYPE,SEARCH_CONDITION from user_constraints where TABLE_NAME='T03';
CONSTRAINT_NAME C
-------------------------------------------------------------------------------------------------------------------------------- -
SEARCH_CONDITION
--------------------------------------------------------------------------------
SYS_C007560 C
"ID" IS NOT NULL
SQL>
列级别启动约束,在创建表的同时创建约束。
SQL>
SQL> create table t03(id number(3) constraint nn_t03_id not null);
Table created.
SQL> select CONSTRAINT_NAME,CONSTRAINT_TYPE,SEARCH_CONDITION from user_constraints where TABLE_NAME='T03';
CONSTRAINT_NAME C
-------------------------------------------------------------------------------------------------------------------------------- -
SEARCH_CONDITION
--------------------------------------------------------------------------------
NN_T03_ID C
"ID" IS NOT NULL
SQL>
在建表之后创建约束
SQL>
SQL>
SQL> alter table t03 add (name varchar2(10));
Table altered.
SQL> desc t03;
Name Null? Type
----------------------------------------------------------------------------------------------------------------- -------- ----------------------------------------------------------------------------
ID NOT NULL NUMBER(3)
NAME VARCHAR2(10)
SQL> alter table t03 add constraint uk_t03_name unique (name);
Table altered.
SQL> select CONSTRAINT_NAME,CONSTRAINT_TYPE,SEARCH_CONDITION from user_constraints where TABLE_NAME='T03';
CONSTRAINT_NAME C
-------------------------------------------------------------------------------------------------------------------------------- -
SEARCH_CONDITION
--------------------------------------------------------------------------------
NN_T03_ID C
"ID" IS NOT NULL
UK_T03_NAME U
SQL> col SEARCH_CONDITION for a30
SQL> /
CONSTRAINT_NAME C SEARCH_CONDITION
-------------------------------------------------------------------------------------------------------------------------------- - ------------------------------
NN_T03_ID C "ID" IS NOT NULL
UK_T03_NAME U
SQL>
主键约束
SQL>
SQL> desc t01;
Name Null? Type
----------------------------------------------------------------------------------------------------------------- -------- ----------------------------------------------------------------------------
ID NUMBER(4)
NAME VARCHAR2(15)
SQL> alter table t01 add constraint pk_t01_id primary key (id);
Table altered.
SQL> select CONSTRAINT_NAME,CONSTRAINT_TYPE,SEARCH_CONDITION from user_constraints where TABLE_NAME='T01';
CONSTRAINT_NAME C SEARCH_CONDITION
-------------------------------------------------------------------------------------------------------------------------------- - ------------------------------
PK_T01_ID P
SQL>
外键约束
SQL> alter table t03 add constraint fk_t03_id foreign key (id) references t01;
Table altered.
SQL> select CONSTRAINT_NAME,CONSTRAINT_TYPE,SEARCH_CONDITION from user_constraints where TABLE_NAME='T03';
CONSTRAINT_NAME C SEARCH_CONDITION
-------------------------------------------------------------------------------------------------------------------------------- - ------------------------------
FK_T03_ID R
NN_T03_ID C "ID" IS NOT NULL
UK_T03_NAME U
SQL>
删除置空 ON DELETE CASCADE :当父表中的行被删除的时候,同时删除在子表中依靠的行。
SQL> select * from t01;
no rows selected
SQL> insert into t01 values(1,'a');
1 row created.
SQL> insert into t03 values(1,'tom');
1 row created.
SQL> insert into t03 values(1,'b');
1 row created.
SQL> commit;
Commit complete.
SQL> select * from t01;
ID NAME
---------- ---------------
1 a
SQL> select * from t03;
ID NAME
---------- ----------
1 tom
1 b
SQL>
SQL>
SQL> delete t01;
delete t01
*
ERROR at line 1:
ORA-02292: integrity constraint (SCOTT.FK_T03_ID) violated - child record found
SQL> alter table t03 drop constraint FK_T03_ID;
Table altered.
SQL> alter table t03 add constraint fk_t03_id foreign key (id) references t01 on delete set null;\
2
SQL> alter table t03 add constraint fk_t03_id foreign key (id) references t01 on delete set null;
Table altered.
SQL> select * from t01;
ID NAME
---------- ---------------
1 a
SQL> select * from t03;
ID NAME
---------- ----------
1 tom
1 b
SQL> delete t01;
delete t01
*
ERROR at line 1:
ORA-01407: cannot update ("SCOTT"."T03"."ID") to NULL
SQL> alter table t03 drop constraint NN_T03_ID;
Table altered.
SQL> select * from t01;
ID NAME
---------- ---------------
1 a
SQL> select * from t03;
ID NAME
---------- ----------
1 tom
1 b
SQL> delete t01;
1 row deleted.
SQL>
SQL> select * from t01;
no rows selected
SQL> select * from t03;
ID NAME
---------- ----------
tom
b
SQL>
ON DELETE SET NULL : 将依靠的外键值转换为空值
SQL> alter table t03 drop constraint FK_T03_ID;
Table altered.
SQL> alter table t03 add constraint FK_T03_ID foreign key (id) references t01 on delete cascade;
Table altered.
SQL> select * from t01;
ID NAME
---------- ---------------
1 a
SQL> select * from t03;
ID NAME
---------- ----------
1 tom
1 b
SQL> delete t01;
1 row deleted.
SQL> select * from t03;
no rows selected
SQL>
check约束
SQL>
SQL>
SQL> alter table emp add constraint ck_emp_sal check (sal>=800);
Table altered.
SQL> insert into emp(empno,ename,sal) values(1,'tom',700);
insert into emp(empno,ename,sal) values(1,'tom',700)
*
ERROR at line 1:
ORA-02290: check constraint (SCOTT.CK_EMP_SAL) violated
SQL>
增加非空约束
SQL> select CONSTRAINT_NAME,CONSTRAINT_TYPE,SEARCH_CONDITION from user_constraints where TABLE_NAME='T03';
CONSTRAINT_NAME C SEARCH_CONDITION
-------------------------------------------------------------------------------------------------------------------------------- - ------------------------------
FK_T03_ID R
UK_T03_NAME U
SQL> alter table t03 modify (id number not null);
Table altered.
SQL> select CONSTRAINT_NAME,CONSTRAINT_TYPE,SEARCH_CONDITION from user_constraints where TABLE_NAME='T03';
CONSTRAINT_NAME C SEARCH_CONDITION
-------------------------------------------------------------------------------------------------------------------------------- - ------------------------------
FK_T03_ID R
SYS_C007568 C "ID" IS NOT NULL
UK_T03_NAME U
SQL>
删除约束
SQL>
SQL> alter table t03 drop constraint SYS_C007568;
Table altered.
SQL>
禁止约束
SQL>
SQL>
SQL> insert into emp (empno,ename,sal) values (1, 'tom', 700);
insert into emp (empno,ename,sal) values (1, 'tom', 700)
*
ERROR at line 1:
ORA-02290: check constraint (SCOTT.CK_EMP_SAL) violated
SQL>
SQL> alter table emp modify constraint CK_EMP_SAL disable;
Table altered.
SQL> insert into emp (empno,ename,sal) values (1, 'tom', 700);
1 row created.
SQL>
启动约束, noavlidate 对新的插入具有约束作用,对表中原有数据不起约束作用。
SQL> alter table emp modify constraint CK_EMP_SAL enable;
alter table emp modify constraint CK_EMP_SAL enable
*
ERROR at line 1:
ORA-02293: cannot validate (SCOTT.CK_EMP_SAL) - check constraint violated
SQL> select * from emp;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7369 SMITH CLERK 7902 17-DEC-80 800 20
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30
7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30
7566 JONES MANAGER 7839 02-APR-81 2975 20
7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30
7698 BLAKE MANAGER 7839 01-MAY-81 2850 30
7782 CLARK MANAGER 7839 09-JUN-81 2450 10
7788 SCOTT ANALYST 7566 24-JAN-87 3000 20
7839 KING PRESIDENT 17-NOV-81 5000 10
7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30
7876 ADAMS CLERK 7788 02-APR-87 1100 20
7900 JAMES CLERK 7698 03-DEC-81 950 30
7902 FORD ANALYST 7566 03-DEC-81 3000 20
7934 MILLER CLERK 7782 23-JAN-82 1300 10
1 tom 700
15 rows selected.
SQL> alter table emp modify constraint CK_EMP_SAL enable novalidate;
Table altered.
SQL> select * from emp;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7369 SMITH CLERK 7902 17-DEC-80 800 20
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30
7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30
7566 JONES MANAGER 7839 02-APR-81 2975 20
7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30
7698 BLAKE MANAGER 7839 01-MAY-81 2850 30
7782 CLARK MANAGER 7839 09-JUN-81 2450 10
7788 SCOTT ANALYST 7566 24-JAN-87 3000 20
7839 KING PRESIDENT 17-NOV-81 5000 10
7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30
7876 ADAMS CLERK 7788 02-APR-87 1100 20
7900 JAMES CLERK 7698 03-DEC-81 950 30
7902 FORD ANALYST 7566 03-DEC-81 3000 20
7934 MILLER CLERK 7782 23-JAN-82 1300 10
1 tom 700
15 rows selected.
SQL> insert into emp (empno,ename,sal) values (2, 'jerry', 750);
insert into emp (empno,ename,sal) values (2, 'jerry', 750)
*
ERROR at line 1:
ORA-02290: check constraint (SCOTT.CK_EMP_SAL) violated
SQL>