oracle 表约束的添加、修改以及约束的禁用启用(转)

 

--禁用所有外键约束的Sql代码
select 'alter table '||table_name||' disable constraint '||constraint_name||';' from user_constraints where constraint_type='R'

--启用所有外键约束的Sql代码
select 'alter table '||table_name||' enable constraint '||constraint_name||';' from user_constraints where constraint_type='R'  

执行查询出外键，然后执行即可

 

以下提到的内容都很简单，所以不详细说明，仅罗列一些语法：

一、主外键的添加、删除

1、向表中添加外键约束，把emp表的deptno字段设置为emp表的外键，引用自dept表

      ALTER TABLE emp  ADD CONSTRAINT FK_test FOREIGN KEY(deptno) REFERENCES dept(deptno);

2、向表中添加主键约束 alter table emp add constraint pk_emp primary key(empno);

3、创建表的同时创建主键约束

（1）无命名 create table EMP( empno int  primary key not null, ename varchar(20), deptno int);

（2）有命名 create table EMP( empno int , ename varchar(20), deptno int , constraint PK_EMP primary key(empno));

4、删除表中已有的主键约束

（1）无命名可用 SELECT * from user_cons_columns;

      如：SELECT * from user_cons_columns where table_name='EMP';

        查找表中主键名称得emp表中的主键名为PK_EMP

        alter table student drop constraint  PK_EMP;

（2）有命名 alter table emp drop constraint PK_EMP;

 

二、更改表的结构

　　1.编辑表的字段

　　修改一个列的数据类型(一般限于修改长度，修改为一个不同类型时有诸多限制):

　　语法:

　　ALTER TABLE 表名 MODIFY(列名,数据类型);

　　eg1:

　　alter table skate_test modify (author number(10,0) )

　　在修改列的长度时候,只能编辑比现有字段实际存的长度还要大,否则提示下面的错误:

　　ORA-01441: 无法减小列长度, 因为一些值过大

　　eg2:

　　alter table skate_test modify (author varchar2(10) )

　　在修改列的数据类型的时候,所修改的列必须为空,否则提示下面的错误:

　　ORA-01439: 要更改数据类型, 则要修改的列必须为空

　　2.增加一个列

　　语法:

　　ALTER TABLE 表名 ADD(列名,数据类型);

　　eg1：

　　ALTER TABLE skate_test ADD(author NUMBER(38,0) not null);

　　3.给列改名：

　　语法:

　　ALTER TABLE 表名 RENAME COLUMN 当前列名 TO 新列名;

　　eg1：

　　ALTER TABLE skate_test RENAME COLUMN author TO authorer_new

　　4.删除一个列

　　语法:

　　ALTER TABLE 表名 DROP COLUMN 列名;

　　eg1：

　　alter table skate_test drop column author

　　5.将一个表改名

　　语法:

　　ALTER TABLE 当前表名 RENAME TO 新表名;

　　eg1：

　　alter table skate_test rename to test_sakte

　　5.给表加注释

　　comment column on 表名.列名 is '注释内容';   //修改表的列的注释

　　COMMENT ON TABLE MOVO_NEW.TEST_SAKTE  IS '注释内容';  //修改表的注释

 

三、主键、外键等约束的启用与禁用

这里以外键为例来说明

执行下面的查询语句，我们会得到当前用户下所有可以被查询到的表的外键，并生相应的修改语句。我们指需要把生成的结果copy出来执行就OK了。

不过对于alter语句的使用还是要谨慎，使用之前需要考虑清楚。

--删除所有外键约束 的Sql代码
select 'alter table '||table_name||' drop constraint '||constraint_name||';' from user_constraints where constraint_type='R'
--alter table EMP drop constraint FK_TEST;
--禁用所有外键约束的Sql代码
select 'alter table '||table_name||' disable constraint '||constraint_name||';' from user_constraints where constraint_type='R'
--alter table EMP disable constraint FK_TEST;
--启用所有外键约束的Sql代码
select 'alter table '||table_name||' enable constraint '||constraint_name||';' from user_constraints where constraint_type='R'  
--alter table EMP enable constraint FK_TEST;

这里有一点需要注意，在上面的查询语句中where条件后带的约束类型 constraint_type='R' ，我们这里指定的是'R',可以猜想到，出来R之外还有其他的约束类型。

下面列出constraint_type的其他几种类型及相应的含义：

Type Code	Type Description	Acts On Level
C	Check on a table	Column
O	Read Only on a view	Object
P	Primary Key	Object
R	Referential AKA Foreign Key	Column
U	Unique Key	Column
V	Check Option on a view	Object

所以，我们需要禁用哪类约束，就将查询条件做相应的调整就好。