Oracle 约束规则（三）

来源：互联网发布：ubuntu 16.04好用吗编辑：程序博客网时间：2024/05/02 00:12

数据完整性

域完整性：单元格里边的值得完整性例如 not null ，unique
实体完整性：一行数据的完整性，用主键约束来完成。
参照完整性：外键约束。
自定义完整性

一、非空约束：

1.创建表时设置非空约束

语法

 CREATE TABLE table_name( column_name datatype  NOT NULL , ) ;

2.修改表设置非空约束

语法

ALTER TABLE table_name  MODIFY column_name datatype NOT NULL ;

注意：在使用修改表设置 NOT NULL 的时候，最好表内没有数据，否则有空值会设置不成功。

3.修改表时去除非空约束

语法

ALTER TABLE table_name  MODIFY column_name datatype  NULL ;

二、主键约束

主键的作用确保表中每一行数据的唯一性，一张表只能设计一个主键约束，主键约束可以有多个字段构成即联合主键或者复合主键。

1.在创建表时设置主键约束

①设置一个主键

CREATE TABLE table_name(column_name  datatype  PRIMARY  KEY , ......) ;

设置主键约束，不必要设置 NOT NULL，自动为 NOT NULL 。

②设置联合主键

语法

CONSTRAINT constraint_name   PRIMARY KEY(column_name1, column_name2,...) ;

constraint_name ：约束的名字
column_name1 与 column_name2 共同构成联合主键。
此语句写在属性后边

实例

创建一个 userinfo 表，有三个字段，id ,username,sex 中，让 id 和 username 做联合主键。如下写法:
create table userinfo(
id number(5,0) ,
username varchar2(20) ,
sex number(1,0) ,
CONSTRAINT pk_id_uname PRIMARY KEY (id,username) );

SQL> create table userinfo()  2SQL> create table userinfo(  2  id number(5,0) ,  3  username varchar2(20) ,  4  sex number(1,0) ,  5  CONSTRAINT pk_id_uname PRIMARY KEY (id,username) );表已创建。SQL> desc userinfo; 名称                                      是否为空? 类型 ----------------------------------------- -------- ---------------------------- ID                                        NOT NULL NUMBER(5) USERNAME                                  NOT NULL VARCHAR2(20) SEX                                                NUMBER(1)SQL>

使用 desc userinfo 查看表结构，发现 id 和 username 都为 NOT NULL 说明已经是联合主键了。

2.修改表设置主键约束

语法

ALTER TABLE table_name  ADD CONSTRAINT   constraint_name  PRIMARY KEY(column_name) ;

实例

alter table userinfo add constraint pk_id primary key(id) ; 将 id 字段修改成主键。

SQL> alter table uinfo add constraint pk_id primary key(id) ;表已更改。

3.如何查看主键约束名称

1.使用数据字典 user_constratints
使用 desc user_constraints 命令先查看这个数字字典如下：

SQL> desc user_constraints; 名称                                      是否为空? 类型 ----------------------------------------- -------- ---------------------------- OWNER                                              VARCHAR2(30) CONSTRAINT_NAME                           NOT NULL VARCHAR2(30) CONSTRAINT_TYPE                                    VARCHAR2(1) TABLE_NAME                                NOT NULL VARCHAR2(30) SEARCH_CONDITION                                   LONG R_OWNER                                            VARCHAR2(30) R_CONSTRAINT_NAME                                  VARCHAR2(30) DELETE_RULE                                        VARCHAR2(9) STATUS                                             VARCHAR2(8) DEFERRABLE                                         VARCHAR2(14) DEFERRED                                           VARCHAR2(9) VALIDATED                                          VARCHAR2(13) GENERATED                                          VARCHAR2(14) BAD                                                VARCHAR2(3) RELY                                               VARCHAR2(4) LAST_CHANGE                                        DATE INDEX_OWNER                                        VARCHAR2(30) INDEX_NAME                                         VARCHAR2(30) INVALID                                            VARCHAR2(7) VIEW_RELATED                                       VARCHAR2(14)SQL>

查看了这个数据字典，可知其中有constraint_name 字段所以
使用 select 语句通过已经知道的表名来查找该表的 约束主键
语句如下：

select constraint_name from user_constraints where table_name =’UINFO’ ;

执行如下：

SQL> select constraint_name  from user_constraints where table_name ='UINFO' ;CONSTRAINT_NAME------------------------------PK_IDSQL>

于是就找到了 uinfo 表的主键的名字。 PK_ID

4.更改主键约束名称

语法

ALTER TABLE table_name RENAME CONSTRAINT old_name TO new_name ;

5.删除主键约束

语法

删除停用主键约束有三种方式：

1. ALTER TABLE table_name  DISABLE | ENABLE  CONSTRAINT constraint_name ;

通过约束名来禁用启用主键约束。

2. ALTER TABLE table_name DROP CONSTRAINT constraint_name  ;

删除主键约束。

3. ALTER TABLE table_name DROP  PRIMARY  KEY [CASCADE] ;   例如删除 uinfo表主键  :  alter table uinfo drop primary key ;

如何查看主键约束是否禁用

user_constraints数据字典中有个 STATUS 字段，查看它就知道是否禁用了，如何查看语句如下：

select constraint_name ,status from user_constraints where table_name = ‘UINFO’ ；

执行语句后

fortranSQL> select constraint_name，status  from user_constraints where table_name='UINFO';CONSTRAINT_NAME                STATUS------------------------------ --------PK_ID                          ENABLEDSQL>

注意： table_name 后边的表名一定要大写，否则会出现错误。

三、外键约束

(1) 创建表时设置外键约束

1.列级约束

列级约束: 从形式上看，在每列定义完后马上定义的约束，在逗号之前就定义好了。
carete table parent(c1 number primary key );
create table child (c number primary key , c2 number references parent(c1));

语法

CREATE TABLE table1
(column_name datatype REFERENCES table2(column_name) ,
…..
) ;

其中 table2 是主表， table1 是从表。从表参照主表。设置外键约束时需要注意的是：
1. 被参照的主表的字段必须是主键。
2. 主从表中相应的字段必须是同一数据类型。
3. 从表（table1）外键字段的值必须来自主表中的相应字段的值，或者为null值其他的值是不允许的。

实例

①先创建一个主表：
create table typeinfo(
typeid varchar2(10) primary key,
typename varchar2(20)
) ;

②创建一个从表：
create table userinfo_f(
id varchar2(10) primary key ,
username varchar2(20) ,
typeid_new varchar2(10) REFERENCES typeinfo(typeid)
) ;

③验证
先向主表里边插入数据，再向从表里插入数据。从表里参照主表的那个字段，要不是跟主表一样，要不就为null。否则会插入不成功。

insert into typeinfo values('001','abcd') ;select * from userinfo_f  ;

④执行结果
我们尝试着先建立两个表，向主表插入一个id 为’001’ 的数据，然后在向从表插入 id 为’001’的数据，能成功。当再向从表插入’00001’的id的时候会出现违反唯一约束条件的错误，所以说，从表是参照主表的。

SQL> create table typeinfo(  2  typeid varchar2(10)  primary key ,  3  typename varchar2(20）） ;表已创建。SQL> create table userinfo_f(  2  id varchar2(10) primary key ,  3  username varchar2(20) ,  4  typeid_new varchar2(10)  REFERENCES typeinfo(typeid) );表已创建。SQL> insert into typeinfo values('001','shaoshaoshao');SQL> insert into userinfo_f(id,username,typeid_new) values('1','abcd','001');已创建 1 行。SQL> insert into userinfo_f(id,username,typeid_new) values('1','abcd','00001');insert into userinfo_f(id,username,typeid_new) values('1','abcd','00001')*第 1 行出现错误:ORA-00001: 违反唯一约束条件 (SYSTEM.SYS_C0010832)SQL>

(2) 表级约束

表级约束: 从形式上可以看出与列级约束的区别了吧。
create table child( c number , c2 number , primary key (c2), foreign key(c2) references parent(c1));
有些时候，列级约束无法实现某种约束的定义，比如联合主键的定义，就要用到表级约束:
create table test(id1 number , id2 number, primary key(id1, id2));

2.修改表时添加外键约束

语法

ADD CONSTRAINT constraint_name FOREIGN KEY(column_name) REFERENCES table_name(column_name) [ ON DELETE CASCADE]

实例

创建一个不带外键的表。
create table userinfo_f(
id varchar2(10) primary key ,
username varchar2(20) ,
typeid_new varchar2(10)
) ;

修改表
alter table userinfo_f and constraint fk_typeid_alter foreign key(typeid_new) refferences typeinfo (typeid) ；
如果想要带级列关联需要在最后加上 ON DELETE CASCADE

3.删除外键约束

①启用关闭外键约束

语法

DISABLE|ENABLE CONSTRAIN  constraint_name

通过约束名字来查看是否启用外键的状态。如果忘记了约束名字可以使用
select constraint_name , constraint_type, status from user_constraints where table_name =’USERINFO_F’ ；
从 user_constraints 数据字典中找到表名为userinfo_f 的外键名和外键类型。

实例

alter table userinfo_f disable constraint FK_TYPEID_ALTER ;
其中的 FK_TYPEID_ALTER 是外键约束名，可以通过查看来查询出来。

②彻底删除外键约束

语法

DROP CONSTRAINT constraint_name ;

实例

alter table userinfo_f drop constraint FK_TYPEID_ALTER ;

删除叫FK_TYPEID_ALTER外键约束。

版权声明
author ：shaoduo
原文来自：http://blog.csdn.net/shaoduo/article/details/68957312
其他出处均为转载，原创作品欢迎读者批评指正

0 0