约束的基本操作_李波

http://blog.sina.com.cn/u/5438469990

首页博文目录关于我

个人资料

微博

加好友发纸条

写留言加关注

博客等级：
博客积分：

博客访问：
关注人气：
获赠金笔：0支
赠出金笔：0支
荣誉徽章：

正文字体大小：大中小

约束的基本操作

(2016-03-30 11:20:50)

分类： OracleBasicknowledge

以下操作均用scott用户完成。

---创建测试表test，表创建完成后添加not null约束。

SQL> create table test (id number);

Table created.

SQL> alter table test modify(id number constraints notnull not null);

Table altered.

SQL> drop table test purge;

Table dropped.

--创建测试表，添加唯一约束，约束名uniquetest ，添加的约束均可以在数据字典user_constraints中查看到。

SQL> create table test (name char(20) constraints uniquetest unique);

Table created.

SQL> select CONSTRAINT_NAME ,TABLE_NAME ,CONSTRAINT_TYPE , STATUS from user_constraints;

CONSTRAINT_NAME TABLE_NAME C STATUS

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

UNIQUETEST TEST U ENABLED

---删除约束uniquetest

SQL> alter table test drop constraints uniquetest;

Table altered.

SQL> select CONSTRAINT_NAME ,TABLE_NAME ,CONSTRAINT_TYPE , STATUS from user_constraints;

CONSTRAINT_NAME TABLE_NAME C STATUS

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

SQL> drop table test purge;

Table dropped.

创建一张表，没有添加任何约束，在表创建完成后，分别添加主键、非空、唯一、外键等约束

SQL> create table test(id number,name char(20),score number(3),class char(20));

Table created.

---添加主键约束

SQL> alter table test add constraint pk_id primary key(id);

Table altered.

--添加非空约束失败，scott用户有同名约束，最后发现在测试表teacher中有nn_name约束，将之删除之后创建成功

SQL> alter table test modify(name char(20) constraint nn_name not null);

alter table test modify(name char(20) constraint nn_name not null)

ERROR at line 1:

ORA-02264: name already used by an existing constraint

SQL> select CONSTRAINT_NAME ,TABLE_NAME ,CONSTRAINT_TYPE , STATUS from user_constraints

2 ;

CONSTRAINT_NAME TABLE_NAME C STATUS

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

NN_NAME TEACHER C ENABLED

FK_DEPTNO EMP R ENABLED

PK_DEPT DEPT P ENABLED

PK_EMP EMP P ENABLED

SYS_C005426 TEACHER P ENABLED

PK_ID TEST P ENABLED

6 rows selected.

SQL> alter table test drop constraint nn_name;

alter table test drop constraint nn_name

ERROR at line 1:

ORA-02443: Cannot drop constraint - nonexistent constraint

SQL> alter table test modify(name char(20) not null);

Table altered.

SQL> alter table test add constraint unique_score unique(score);

Table altered.

SQL> select CONSTRAINT_NAME ,TABLE_NAME ,CONSTRAINT_TYPE , STATUS from user_constraints;

CONSTRAINT_NAME TABLE_NAME C STATUS

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

SYS_C005455 TEST C ENABLED

NN_NAME TEACHER C ENABLED

FK_DEPTNO EMP R ENABLED

PK_DEPT DEPT P ENABLED

PK_EMP EMP P ENABLED

SYS_C005426 TEACHER P ENABLED

PK_ID TEST P ENABLED

UNIQUE_SCORE TEST U ENABLED

8 rows selected.

SQL> alter table teacher drop constraint nn_name;

Table altered.

SQL> alter table test drop constraint SYS_C005455;

Table altered.

SQL> alter table test modify(name char(20) constraint nn_name not null);

Table altered.

SQL> select CONSTRAINT_NAME ,TABLE_NAME ,CONSTRAINT_TYPE , STATUS from user_constraints;

CONSTRAINT_NAME TABLE_NAME C STATUS

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

NN_NAME TEST C ENABLED

FK_DEPTNO EMP R ENABLED

PK_DEPT DEPT P ENABLED

PK_EMP EMP P ENABLED

SYS_C005426 TEACHER P ENABLED

PK_ID TEST P ENABLED

UNIQUE_SCORE TEST U ENABLED

7 rows selected.

SQL> desc test;

Name Null? Type

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

ID NOT NULL NUMBER

NAME NOT NULL CHAR(20)

SCORE NUMBER(3)

CLASS CHAR(20)

SQL> create table school(class char(10),constraint pk_class primary key(class));

Table created.

SQL> desc school

Name Null? Type

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

CLASS NOT NULL CHAR(10)

SQL> select CONSTRAINT_NAME ,TABLE_NAME ,CONSTRAINT_TYPE , STATUS from user_constraints;

CONSTRAINT_NAME TABLE_NAME C STATUS

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

NN_NAME TEST C ENABLED

FK_DEPTNO EMP R ENABLED

PK_DEPT DEPT P ENABLED

PK_EMP EMP P ENABLED

SYS_C005426 TEACHER P ENABLED

PK_ID TEST P ENABLED

UNIQUE_SCORE TEST U ENABLED

PK_CLASS SCHOOL P ENABLED

8 rows selected.

SQL> alter table test add constraint fk_class foreign key(class) references school(class);

Table altered.

SQL> select CONSTRAINT_NAME ,TABLE_NAME ,CONSTRAINT_TYPE , STATUS from user_constraints;

CONSTRAINT_NAME TABLE_NAME C STATUS

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

FK_CLASS TEST R ENABLED

NN_NAME TEST C ENABLED

FK_DEPTNO EMP R ENABLED

PK_DEPT DEPT P ENABLED

PK_EMP EMP P ENABLED

SYS_C005426 TEACHER P ENABLED

PK_ID TEST P ENABLED

UNIQUE_SCORE TEST U ENABLED

PK_CLASS SCHOOL P ENABLED

9 rows selected.

---以下是在test表中创建的4种约束。

SQL> select CONSTRAINT_NAME ,TABLE_NAME ,CONSTRAINT_TYPE , STATUS from user_constraints order by table_name;

CONSTRAINT_NAME TABLE_NAME C STATUS

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

PK_DEPT DEPT P ENABLED

PK_EMP EMP P ENABLED

FK_DEPTNO EMP R ENABLED

PK_CLASS SCHOOL P ENABLED

SYS_C005426 TEACHER P ENABLED

FK_CLASS TEST R ENABLED

PK_ID TEST P ENABLED

UNIQUE_SCORE TEST U ENABLED

NN_NAME TEST C ENABLED

9 rows selected.

SQL> spool off;

关于约束，有几点要说明的：

foreign key可以定义在同一张表上，同一个字段可以加fk同时加非空

fk外键可以随便删除，但是删除主表，必须要加条件

ON DELETE CASCADE 当主键表删除，自动把参照该字段的外键记录全部删除

ON DELETE SET NULL: 当主键表删除，自动把参照该字段的外键记录全部设置为空

例如：

要求;当删除部门，属于该部门的所有员工被删除

alter table e drop constraint FK_E_D_DEPTNO;

alter table e add constraint fk_e_d_deptno foreign key(deptno)

references d(deptno) on delete cascade ;

要求;当删除部门，属于该部门的所有员工的部门被设置为空

alter table e drop constraint FK_E_D_DEPTNO;

alter table e add constraint fk_e_d_deptno foreign key(deptno)

references d(deptno) on delete set null ;

阅读┊ 收藏 ┊ 喜欢 ▼ ┊打印┊举报/Report

前一篇：主键约束和唯一性约束，唯一索引

后一篇：11.2.0.1 ADG ORA-600 的一个BUG 解决方法

新浪BLOG意见反馈留言板　欢迎批评指正