数据完整性
域完整性 : 单元格里边的值得完整性 例如 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()
2
SQL> 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_ID
SQL>
于是就找到了 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’ ;
执行语句后
fortran
SQL> select constraint_name,status from user_constraints where table_name='UINFO';
CONSTRAINT_NAME STATUS
------------------------------ --------
PK_ID ENABLED
SQL>
注意: 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
其他出处均为转载,原创作品欢迎读者批评指正