oracle 9i sql约束

sql有以下几个约束：

1.primary key

1)create table newtable (tablename varchar(10) primary key);

2)create table newtable (tablename varchar(10) CONSTRAINT newtable_tablename_pk primary key);

3)alter table newtable add constraint newtable_tablename_pk primary key(tablename));

2.foreign key

1)create table newtable (tablename varchar(10) primary key, table_foreign varchar(10) foreign key references foreigntable (tablename2));//foreigntable 是外键所在的表，而tablename2是其中的列

2)create table newtable(table_foreign varchar(10), CONSTRAINT newtable_tf_fk foreign key(table_foreign) REFERENCES anothertable(col1));

3)alter table newtable add constraint newtable_tf_fk foreign key(table_foreign) REFERENCES anothertable(col1);

3.unique

1)create table newtable( tablename varchar(10) UNIQUE);

2)create table newtable( tablename varchar(10), CONSTRAINT tn_uk UNIQUE(tablename));

3)alter table newtable add constraint tn_uk UNIQUE(tablename);

4.check

1)create table newtable( firstcol NUMBER CHECK(BETWEEN 20 AND 30));

2)create table newtable( firstcol NUMBER, constraint f_ck CHECK (firstcol BETWEEN 20 AND 30));

3)ALTER TABLE newtable ADD CONSTRAINT f_ck CHECK (firstcol BETWEEN 20 AND 30);

5.not null

1)CREATE TABLE newtable (firstcol number not null);

2)CREATE TABLE newtable (firstcol number constraint fc_nn not null);

3)ALTER TABLE newtable modify ( firstcol constraint fc_nn not null);

6.约束一经定义就不能更改，要更改只能先把约束删除再重新创建

7.约束可以用DISABLE和ENABLE来使之无效和有效

ALTER TABLE newtable DISABLE CONSTRAINT fc_nn; ALTER TABLE newtable ENABLE CONSTRAINT fc_nn;

8.删除约束:

ALTER TABLE XX 

DROP PRIMARY KEY |UNIQUE(columnname)| CONSTRAINT constraintname;