PK FK 命令小记

PK:

–增加

alter table AAA  add constraint aaa primary key (USERNAME);

ALTER TABLE AAA ADD CONSTRAINT aaa PRIMARY KEY(username) USING INDEX

 ALTER TABLE AAA ADD CONSTRAINT aaa PRIMARY KEY(username) USING INDEX aaa;

–删除

 ALTER TABLE AAA DROP PRIMARY KEY CASCADE; 

 alter table AAA  drop constraint bbb 

 alter table AAA  drop constraint bbb cascade

 alter table AAA  drop constraint bbb cascade drop index

 alter table AAA  drop constraint bbb cascade keep index

–rename

ALTER TABLE aaa RENAME CONSTRAINT aaa   TO bbb;

–disable

alter table t1 disable constraint t1_pk;

 

 

FK:

–增加

alter table AAA
  add constraint bbb foreign key (USERNAME)
  references ccc (AGENT_NAME);

 

alter table empx add constraint fk_deptx foreign key (deptno) references deptx(deptno) novalidate;

alter table AAA
  add constraint bbb foreign key (USERNAME)
  references ccc (AGENT_NAME) on delete cascade;

alter table AAA
  add constraint bbb foreign key (USERNAME)
  references ccc (AGENT_NAME) on delete set null;

alter table AAA
  add constraint bbb foreign key (USERNAME)
  references ccc (AGENT_NAME) on delete set null  deferrable initially deferred;

–删除

 alter table empx drop constraint fk_deptx;

 

–约束范围
disable novalidate 原数据和新数据都不做验证
disable validate已经存在的数据要满足条件，新数据可以不满足条件
enable novalidate新数据不允许违背规则，原有数据可以不满足
enable validate新数据要验证，原有数据也要验证，缺省情况