Oracle 约束的重大作用
Oracle 作用 约束 重大
2023-09-14 09:13:29 时间
- --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>
相关文章
- oracle 判断正负号,Oracle Number型的深入理解
- 字段Oracle 快速拆分字段：以逗号为界限（oracle按逗号拆分）
- Oracle定时任务实现自动化管理（oracle定时执行）
- 探索Oracle的主目录：了解其结构、作用和重要性（oracle的主目录）
- 来自Oracle的力量：序列的用武之地（oracle序列的作用）
- Oracle查询中的约束现象研究（oracle查约束）
- 与口令Oracle登录：正确的用户名和口令（oracle登录名）
- 查询Oracle数据库外键约束（oracle查外键）
- Oracle主键约束：保证数据完整性的重要措施（oracle主键约束）
- 优化Oracle连接查询，提升数据库性能（oracle连接查询优化）
- 使用Oracle约束加强查询安全性（oracle 约束 查询）
- 的使用Oracle中间键的作用及其应用（oracle中间键）
- Oracle数据库的默认端口号码分析（oracle的默认端口号）
- Oracle新一代虚拟化产品：提升企业效率和灵活性（oracle虚拟化产品）
- Oracle数据库中关闭外键的方法（oracle关闭外键作用）
- Oracle共享池提升数据库性能的利器（oracle共享池的作用）
- ITL等待Oracle的路上拐弯抹角（itl等待 oracle）
- ELK代替Oracle一种新的解决方案（elk替代oracle）
- Oracle遇上缺失逗号的挣扎（oracle 中缺少逗号）
- Oracle事务控制约束，保障数据安全（oracle事务、约束）
- Oracle数据库中的主键外键约束研究（oracle主键外键约束）
- Oracle中同义词提高语句查询的效率（oracle中同义词作用）
- 的作用Oracle 数据库管理PGA的作用（oracle 中pga）
- Oracle数据库中var文件的作用（oracle var文件）
- Oracle RCU安装指引实现一步到位（oracle rcu安装）
- Oracle Exce在数据库管理中的重要作用（oracle exce）