oracle 之 约束(第三章)
来源:互联网 发布:食品经营许可网络平台 编辑:程序博客网 时间:2024/05/16 14:09
oracle 之 约束
oracle的constraint有五种:
1:检查约束
2:非空约束
3:唯一约束
4:主键约束
5:外键约束
一) 下面通过操作说明约束的创建方法:
SQL> create table bookinfo( 2 bookid number(13), 3 bookname varchar2(20), 4 publish varchar2(30), 5 pubdate date, 6 price number(9,3), 7 author varchar2(20), 8 store number not null, 9 reader number(20), 10 remarks varchar2(25), 11 constraint chk2_key check(price>10 and price<=200), 12 constraint unique_key unique(bookname), 13 constraint prima_key primary key (bookid), 14 constraint foreign_key foreign key(publish) references PUBLISHINFO (PUBLISHID) 15 );Table created.SQL>
二)查询该用户下创建的约束信息:
SQL> r 1* select owner,constraint_name,constraint_type,table_name from user_constraints where table_name='BOOKINFO'OWNER CONSTRAINT_NAME C TABLE_NAME------------------------------ ------------------------------ - ------------------------------RHYS SYS_C0011171 C BOOKINFORHYS CHK2_KEY C BOOKINFORHYS PRIMA_KEY P BOOKINFORHYS UNIQUE_KEY U BOOKINFORHYS FOREIGN_KEY R BOOKINFOSQL>
在user_constraints视图中C列表示:C代表检查约束,P代表主键约束,U代表唯一索引约束,R代表外键约束
通过user_cons_columns这个视图可以看到该用户下所有的每个约束对应的表列的名字关系情况。
eg:
SQL> select * from user_cons_columns; OWNER CONSTRAINT_NAME TABLE_NAME COLUMN_NAME POSITION------------------------------ ------------------------------ ------------------------------ -------------------------------------------------------------------------------- ----------RHYS BIN$2V5HaJApQoLgQyfgEwp/yg==$0 BIN$2V5HaJAsQoLgQyfgEwp/yg==$0 BOOKNAME 1RHYS BIN$2V5HaJAoQoLgQyfgEwp/yg==$0 BIN$2V5HaJAsQoLgQyfgEwp/yg==$0 BOOKID 1RHYS BIN$2V5HaJAnQoLgQyfgEwp/yg==$0 BIN$2V5HaJAsQoLgQyfgEwp/yg==$0 PRICE RHYS BIN$2V5HaJAmQoLgQyfgEwp/yg==$0 BIN$2V5HaJAsQoLgQyfgEwp/yg==$0 STORE RHYS FOREIGN_KEY BOOKINFO PUBLISH 1RHYS UNIQUE_KEY BOOKINFO BOOKNAME 1RHYS PRIMA_KEY BOOKINFO BOOKID 1RHYS BIN$2V5HaJAiQoLgQyfgEwp/yg==$1 BIN$2V5HaJAlQoLgQyfgEwp/yg==$0 BOOKNAME 1RHYS BIN$2V5HaJASQoLgQyfgEwp/yg==$0 BIN$2V5HaJATQoLgQyfgEwp/yg==$0 STORE RHYS BIN$2V5HaJARQoLgQyfgEwp/yg==$0 BIN$2V5HaJATQoLgQyfgEwp/yg==$0 BOOKNAME RHYS BIN$2V5HaJAQQoLgQyfgEwp/yg==$0 BIN$2V5HaJATQoLgQyfgEwp/yg==$0 BOOKID RHYS BIN$2V5HaJAhQoLgQyfgEwp/yg==$1 BIN$2V5HaJAlQoLgQyfgEwp/yg==$0 BOOKID 1RHYS BIN$2V5HaJAgQoLgQyfgEwp/yg==$1 BIN$2V5HaJAlQoLgQyfgEwp/yg==$0 PRICE RHYS BIN$2V5HaJAfQoLgQyfgEwp/yg==$1 BIN$2V5HaJAlQoLgQyfgEwp/yg==$0 STORE RHYS BIN$2V5HaJAOQoLgQyfgEwp/yg==$0 BIN$2V5HaJAPQoLgQyfgEwp/yg==$0 STORE RHYS PRI_KEY PUBLISHINFO PUBLISHID 1RHYS UNIQUE_CHK_KEY BOOKINFO_03 BOOKID 1RHYS SYS_C0011147 BOOKINFO_03 BOOKID RHYS BIN$2V5HaJAeQoLgQyfgEwp/yg==$1 BIN$2V5HaJAlQoLgQyfgEwp/yg==$0 PUBLISH RHYS SYS_C0011145 BOOKINFO_02 STORE OWNER CONSTRAINT_NAME TABLE_NAME COLUMN_NAME POSITION------------------------------ ------------------------------ ------------------------------ -------------------------------------------------------------------------------- ----------RHYS BIN$2V5HaJAdQoLgQyfgEwp/yg==$1 BIN$2V5HaJAlQoLgQyfgEwp/yg==$0 BOOKID RHYS FK_DEPTNO EMP DEPTNO 1RHYS PK_DEPT DEPT DEPTNO 1RHYS SYS_C0011142 TAIAN SUBJECT RHYS PK_EMP EMP EMPNO 1 25 rows selected SQL>
注意:带BIN开头的约束名都是已经放入回收站的,可以不用管它,如果想清空的话,可以purge该用户的回收站。如删除当前用户下回收站信息:purge recyclebin;
三)修改已创建的约束情况:
1)添加约束命令格式
alter table table_name add constraint primariy_name primary key (column_name);alter table table_name add constraint foreign_name foreign key (column_name) references table_name(column_name;alter table table_name add constraint unique_name unique(column);alter table table_name add constraint check_name check(value);
alter table table_name modify column not null;
2)删除表的约束条件
alter table table_name drop constraint constraint_name;
eg:
SQL> select owner,constraint_name,constraint_type,table_name from user_constraints where table_name='BOOKINFO';OWNER CONSTRAINT_NAME C TABLE_NAME------------------------------ ------------------------------ - ------------------------------RHYS CHK2_KEY C BOOKINFORHYS PRIMA_KEY P BOOKINFORHYS UNIQUE_KEY U BOOKINFORHYS FOREIGN_KEY R BOOKINFOSQL> alter table bookinfo drop constraint CHK2_KEY;Table altered.SQL> select owner,constraint_name,constraint_type,table_name from user_constraints where table_name='BOOKINFO';OWNER CONSTRAINT_NAME C TABLE_NAME------------------------------ ------------------------------ - ------------------------------RHYS PRIMA_KEY P BOOKINFORHYS UNIQUE_KEY U BOOKINFORHYS FOREIGN_KEY R BOOKINFOSQL>
注意:如果想把表中某一列的非空约束去掉,那么可以使用:alter table table_name modify column_name NUll;
eg:
SQL> desc bookinfo; Name Null? Type ----------------------------------------------------------------------------- -------- ---------------------------------------------------- BOOKID NOT NULL NUMBER(13) BOOKNAME VARCHAR2(20) PUBLISH VARCHAR2(30) PUBDATE DATE PRICE NUMBER(9,3) AUTHOR VARCHAR2(20) STORE NOT NULL NUMBER READER NUMBER(20) REMARKS VARCHAR2(25)SQL> alter table bookinfo modify STORE NULL;Table altered.SQL> DESC BOOKINFO; Name Null? Type ----------------------------------------------------------------------------- -------- ---------------------------------------------------- BOOKID NOT NULL NUMBER(13) BOOKNAME VARCHAR2(20) PUBLISH VARCHAR2(30) PUBDATE DATE PRICE NUMBER(9,3) AUTHOR VARCHAR2(20) STORE NUMBER READER NUMBER(20) REMARKS VARCHAR2(25)SQL>
注意:数据库没有修改约束这种情景。
- oracle 之 约束(第三章)
- Oracle之表约束
- oracle之约束
- oracle之约束
- oracle之约束
- oracle之约束
- Oracle之约束
- oracle之约束
- 【转】oracle之约束
- oracle之约束
- oracle之约束
- oracle之约束
- Oracle之约束总结
- Oracle之约束
- oracle之约束解析
- 【Oracle学习】之 约束
- Oracle数据库之约束
- oracle数据库之约束
- Tips for Zend Studio 8
- Android上的bug定位(troubleshooting)
- ios推送相关
- STL中的find_if函数
- IOS 6.0 下 在输入中文时 按下锁屏键 导致的闪退问题
- oracle 之 约束(第三章)
- js控制页面显示大小百分比脚本
- Android NDK中sample导入Eclipse
- Android中如何使用 address2line
- Google Analytics in IOS(六)—— 社交
- Annotation注解 含义
- VirtualBox 4安装CentOS 6.3
- oracle修改字段类型,从number(2)改为number(2,1)
- 【Three20】【2011-2-25】【Xcode4.2中将Three20开源库导入到工程项目中】