SQL 重建约束

转自yangtingkun

 

文章已同步至我的GAE博客 和WordPress博客

 

注: 1) ALTER TABLE 表名 ADD CONSTRAINT 新的约束名 约束

2) ALTER TABLE 表名 DROP CONSTRAINT 旧的约束名

3) ALTER TABLE 表名 RENAME 新的约束名 TO 新的约束名 (RENAME不适合SQL Server 2000,2005及以后的没测试过)

当CHECK约束的条件发生变化时，Oracle没有办法直接修改约束的条件，而只能通过删除约束并重建的方式。

前两天在读TOM的Effective Oracle by Design时候，突然意识到以前自己的操作是有问题的。

SQL> CREATE TABLE T
2 (ID NUMBER,
3 NAME VARCHAR2(30),
4 AGE NUMBER CONSTRAINT CK_AGE CHECK (AGE > 16)
5 );

表已创建。

如果需要修改约束CK_AGE的约束条件，已往我的做法如下：

SQL> ALTER TABLE T DROP CONSTRAINT CK_AGE;

表已更改。

SQL> ALTER TABLE T ADD CONSTRAINT CK_AGE1 CHECK(AGE > 18);

表已更改。

但是看TOM的文章的时候，发现TOM是这么说 的："我会用两条命令：一条增加一个新的约束，另一条删除旧的约束。"开始我还在奇怪，为什么TOM先说增加而后说删除呢。这时我想起TOM在 EXPERT ONE ON ONE ORACLE的EXP那一章似乎介绍过，约束条件完全相同的约束可以重复加在同一张表上。于是，我明白了先增加约束的含义，这样可以使表中的数据自始至终 都处于约束条件的限制之下，而如果先删除约束的话，在两个DDL语句执行之间，对表执行的DML操作则不会收到约束的限制，虽然这个间隔时间可能很短，但 是不能排除出现这种情况的可能性。

因此正确的操作顺序应该是：

SQL> DROP TABLE T;

表已丢弃。

SQL> CREATE TABLE T
2 (ID NUMBER,
3 NAME VARCHAR2(30),
4 AGE NUMBER CONSTRAINT CK_AGE CHECK (AGE > 16)
5 );

表已创建。

SQL> ALTER TABLE T ADD CONSTRAINT CK_AGE1 CHECK(AGE > 18);

表已更改。

SQL> ALTER TABLE T DROP CONSTRAINT CK_AGE;

表已更改。

约束的名称对应用系统来说没有实际意义，因此约束名的改变不会造成什么影响，当然如果追求数据结构和修改前一致，可以通过RENAME CONSTRAINT的方式进行修改。

SQL> ALTER TABLE T RENAME CONSTRAINT CK_AGE1 TO CK_AGE;

表已更改。

采用这种方式，就可以保证表中的数据始终处于约束的限制之下。

大家在读Oracle的官方文档或TOM这种大师级的著作时，尽量把每句话的意思都搞清楚，也许只是随便的一句话，就可以推敲出很多东西，而使你获益匪浅。