Oracle中如何创建约束,查询约束和删除约束(check约束,外键和主键约束) |
您所在的位置:网站首页 › oracle怎么添加主键约束 › Oracle中如何创建约束,查询约束和删除约束(check约束,外键和主键约束) |
Oracle 创建主键,外键, check约束的几种方法:(write by RFH) 1. 在创建表时创建约束: //添加主键约束 Create table userinfo (userid number(20)constraint pk_user primary key ,uname varchar(20)); Create table userinfo(userid number(20),unamevarchar(20),constraint pk_user primary key(userid); //添加外键约束 create table userinfo(useridnumber(20),deptid number(20) referencesdept(deptid)); create table userinfo(useridnumber(20),deptid number(20), constraint fk_dept foreign key(deptid)references dept(deptid)); //添加check约束 create table userinfo(useridnumber(20),age number(3) constraint ck_age check(age10)); create table userinfo(useridnumber(20),age number(3), constraint ck_age check(age10))
2. 假如你已经建表完成,而忘记添加约束,没关系,可以单独添加: //添加主键约束: alter table userinfo add constraintpk_user primary key(userid); //添加外键约束: alter table userinfo add constraint fk_dept foreign key(deptid)references dept(deptid); //添加check约束 alter table userinfo add constraint ck_age check(age10);
3对表中约束的查询: Desc userinfo; all_constraints 包含了表的约束信息,但是会被刷屏 我们用user_constrains表来获取当前用户的约束: select constraint_name,table_name,constraint_type from user_constraints; 如果我们要想看某个表的约束: select constraint_name,table_name,constraint_type from user_constraints where table_name=’TABLENAME’;//注意表名要大写 类似的,索引表USER_INDEXES,序列表USER_SEQUENCES,触发器表USER_TRIGGERS,存储过程表USER_PROCEDURES,当然还有USER_TABLES,VIEW_TABLES 等。有个惊人的发现,之前建的外键会被添加到索引表中。他们中一般都有对应字段table_name ,trigger_name等等。 4.我们已经可以自己创建和查询约束,那么如果想要删掉无用的约束呢?//删除主键 alter table dept drop primary key; 或者 alter table userinfo drop constraint pk_user; //删除check约束 alter table userinfo drop constraint ck_age; //删除外键约束 alter table userinfo drop constraint fk_dept;
用alter table语句:(仅供参考) 完整用法: ALTER [IGNORE] TABLE tbl_name alter_specification [,alter_specification] ... alter_specification: table_option ... | ADD [COLUMN]column_definition [FIRST | AFTER col_name ] | ADD [COLUMN](column_definition,...) | ADD {INDEX|KEY} [index_name] [index_type](index_col_name,...) | ADD [CONSTRAINT[symbol]] PRIMARY KEY [index_type] (index_col_name,...) | ADD [CONSTRAINT[symbol]] UNIQUE [INDEX|KEY] [index_name] [index_type] (index_col_name,...) | ADD [FULLTEXT|SPATIAL] [INDEX|KEY] [index_name] (index_col_name,...) | ADD [CONSTRAINT[symbol]] FOREIGN KEY [index_name] (index_col_name,...) [reference_definition] | ALTER [COLUMN]col_name {SET DEFAULT literal | DROP DEFAULT} | CHANGE [COLUMN] old_col_name column_definition [FIRST|AFTERcol_name] | MODIFY [COLUMN]column_definition [FIRST | AFTER col_name] | DROP [COLUMN] col_name | DROP PRIMARY KEY | DROP {INDEX|KEY} index_name | DROP FOREIGN KEYfk_symbol | DISABLE KEYS | ENABLE KEYS | RENAME [TO] new_tbl_name | ORDER BY col_name [, col_name] ... | CONVERT TO CHARACTERSET charset_name [COLLATE collation_name] | [DEFAULT] CHARACTERSET charset_name [COLLATE collation_name] | DISCARD TABLESPACE | IMPORT TABLESPACE |
今日新闻 |
推荐新闻 |
CopyRight 2018-2019 办公设备维修网 版权所有 豫ICP备15022753号-3 |