约束 | 非空 | 主键 | 外键 | 唯一 | 检查 |
创建表设置 | create table userinfo (id number(6,0),username varchar2(20) not null); | create table userinfo (id number(6,0) ,username varchar2(20),constraint pk_id_username primary key(id,username)); | create table userinfo (id number(6,0) primary key,typeid_new varchar2(20),constraint fk_typeid foreign key(typeid_new) references typeinfo(typeid)); | create table userinfo (id number(6,0) ,username varchar2(20),constraint un_username unique(username)); | create table userinfo (id number(6,0) ,username varchar2(20),salary number(6,2),constraint ck_salary check(salary>0)); |
create table userinfo (id number(6,0) primary key,username varchar2(20)); | create table userinfo (id number(6,0) primary key,username varchar2(20) references typeinfo(typeid)); | create table userinfo (id number(6,0) ,username varchar2(20) unique); | create table userinfo (id number(6,0) primary key,username varchar2(20),salary number(6,2) check(salary>0)); | ||
修改表添加 | alter table userinfo modify username varchar2(20) not null; | alter table userinfo add constraint pk_id_username primary key(id,username); | alter table userinfo add constraint fk_typeid foreign key(typeid_new) references typeinfo(typeid) | alter table userinfo add constraint un_username unique(username); | alter table userinfo add constraint ck_salary check(salary>0); |
更改约束名字 | —— | alter table userinfo rename pk_id to pk_id_new; | |||
修改表去除 | alter table userinfo modify username varchar2(20) null; | alter table userinfo disable constraint pk_id ; | alter table userinfo disable constraint fk_typeid; | alter table userinfo disable constraint un_username; | 同左 |
alter table userinfo drop constraint pk_id;或者alter table userinfo drop primary key[cascade] | alter table userinfo drop constraint fk_typeid; | alter table userinfo drop constraint un_username; | 同左 |
oracle约束知识
最新推荐文章于 2021-04-12 18:27:41 发布