南宁比较有好的网站制作公司,怎样把域名和做的网站连接,wordpress 跳转 文章,南京小程序制作开发知识背景
表的约束#xff0c;就是在表中的数据上加上约束#xff0c;也被称为数据完整性约束。数据完整性约束的目的是为了不被规定的、不符合规范的数据进入数据库
在录入数据库或数据发生变化时#xff0c;DBMS(数据库管理系统)会按照一定的约束条件对数据进行监测就是在表中的数据上加上约束也被称为数据完整性约束。数据完整性约束的目的是为了不被规定的、不符合规范的数据进入数据库
在录入数据库或数据发生变化时DBMS(数据库管理系统)会按照一定的约束条件对数据进行监测是不符合规范的数据不能进入数据库使数据库中存储的数据正确、有效、相容确保存储的数据是正确的。
在表中有三大类完整性约束实体完整性(entity integrity)、参照完整性(referential integrity)和用户定义的完整性(user-defined integrity)
单列约束可以定义在列级或者表级多列约束则必须定义在表级这是什么意思呢
单列约束指定是设计涉及字段(列)的约束。这类约束可以在定义字段时直接指定(即列级)例如在创建表时直接为某个字段指定NOT NULL 或 UNIQUE约束也可以在表级别定义即在创建表之后单独为某个字段添加约束
多列约束涉及两个或更多字段(列)的约束这类约束必须在表级别定义因为他们关联了多个字段不能仅仅在单个字段级别进行定义如最常见的复合主键(由多个列共同作为主键)和复合外键
实体完整性约束
实体完整性的定义
1、关系型数据库中每个元组应该是可区分的是唯一的使用这样的约束条件来保证实体完整性
2、实体完整性约束所对应的属性被称为表的主键(primary key)或者称为关系的主码
3、实体完整性约束可以定义在表中的一列或多列上
实体完整性的规则
1、实体完整性的规则基本都是针对于表而言的一个基本表通常对应于现实世界的一个实体集
2、主键对应的列值在表中是非空的、唯一的
3、每个表中最多只能拥有一个主键
实体完整性约束创建语法
1、定义主键约束
CREATE TABLE table_name (column1 INT,column2 VARCHAR(255),...PRIMARY KEY (column1) //使得column1被定义为主键
);2、定义主键约束并且给主键约束命名
CREATE TABLE table_name (column1 INT,column2 VARCHAR(255),...CONSTRAINT constraint_name PRIMARY KEY (column1) //定义column为主键并使这个主键被命名为constraint_name
);3、如果表已经存在还可以手动增加主键约束
ALTER TABLE table_name
ADD CONSTRAINT constraint_name PRIMARY KEY (column1);参照完整性约束
参照完整性约束的定义
1、参照完整性就好像现实世界中实体与实体的依赖关系比如鱼与水水就是鱼的生存环境这样的关系在数据中表与表之间也是存在的
2、一个表的属性需要依赖于另一个表的属性在数据库中被映射为一个表的字段与另一个表的某个字段相关联使用这样的约束条件来保证参照完整性例如员工表和部门表因为每一个入职的员工都必须要又对应的部门当员工表中有一条员工数据是没有部门的那么就说明这一条员工的数据是不合理的
3、 参照完整性约束对应的属性被称为表的外键(foreign key)或者被称为关系的外码
4、参照完整性约束可定义在表中的一列或者多列上
5、外键约束需要有主表和从表如上述所说的例子员工表就是从表部门表就是主表(被参照的表)
参照完整性约束的规则
1、被参照的相对应列上必须被定义为主键约束或唯一约束也就是说在主表上的被参照的字段必须被定义为主键约束或唯一约束
2、表的外键列可以取空值
参照完整性语法举例
设有两个表分别为classes表和student表其中classes表为主表student表为从表并且classes表的主键字段id被student_id字段给引用
CREATE TABLE classes (id INT PRIMARY KEY, //被引用的字段必须被设置为主键约束或唯一约束name VARCHAR(255)
);
CREATE TABLE students (id INT PRIMARY KEY,name VARCHAR(255),class_id INT,CONSTRAINT ID FOREIGN KEY (class_id) REFERENCES classes(id) //是class表中di字段被当前正在创建的表的class_id字段所引用并且被命名为ID
);如果表已经存在还可添加外键约束
ALTER TABLE students
ADD CONSTRAINT ID
FOREIGN KEY (class_id)
REFERENCES classes(id);在进行参照完整性的过程当中还可进行级联操作有更新级联和删除级联以下这个例子就是删除级联(以DELETE为关键字更新的关键字即为UPDATE)
CREATE TABLE classes (id INT PRIMARY KEY,name VARCHAR(255)
);
CREATE TABLE students (id INT PRIMARY KEY REFERENCES classes ON DELETE RESTRICT/CASCADE/SETNULL //进行删除级联name VARCHAR(255),class_id INT,CONSTRAINT ID FOREIGN KEY (class_id) REFERENCES classes(id)
);或是
CREATE TABLE classes (id INT PRIMARY KEY,name VARCHAR(255)
);
CREATE TABLE students (id INT PRIMARY KEY,name VARCHAR(255),class_id INT,CONSTRAINT ID FOREIGN KEY (class_id) REFERENCES classes(id) ON DELETE RESTRICT/CASCADE/SETNULL
);参数介绍ON DELETE RESTRICT当在父表/主表(即外键的来源表)中删除对应记录时首先检查该记录是否有对应的外键如果有则不允许删除ON DELETE CASCADE当在父表/主表(即外键的来源表)中删除对应记录时首先检查该记录是否有对应外键如果有则也级联地删除外键在子表(即包含外键的表)中的记录ON DELETE SETNULL当在父表/主表(即外键的来源表)中删除对应记录时首先检查该记录是否有对应外键如果有则将外键列设置为空值
用户定义的完整性约束
任何的关系型数据库都应该支持实体完整性和参照完整性这是关系模型所要求的。除此之外不同的关系型数据库系统根据其应用环境的不同还需要一些特殊的约束条件。而用户定义的完整性约束就是针对某一具体关系数据库的约束条件
非空约束
概述非空约束就是要求设置非空约束的列值不同为空值使用NOT NULL关键字来设置
语法
在下述的创建表的SQL语句中id integer NOT NULL就是指定了t01表中id字段为非空约束
CREATE TABLE t01 (id integer NOT NULL,name text.score numeric);唯一约束
概述唯一约束需要保证设置唯一约束的列值是在整个列中是唯一的但可以为空
语法
“id integer UNQIUE”就是将id字段设置为唯一约束
CREATE TABLE t01(id integer UNQIUE,name text,score numeric);check约束
概述限制一个特定列中的值必须要满足一定的条件表达式当插入数据是DBMS将检查该数据是否满足check约束条件当不满足时将会拒绝这种操作
语法
下述SQL语句中score numeric CHECK (score 0)的check约束为在插入或更新score的数据值必须大于0
CREATE TABLE t01(id integer,name text,score numeric CHECK (score 0));约束的控制状态
概述
①enable/disable 对新增的数据进行检查/对新增的数据不进行检查
②validate/novalidate 对现有数据的约束检查/对现有数据的约束不检查
约束控制方法
约束的控制方法都是对现有的数据和对新增的数据各自执行一个策略其中外严内峻是默认状态如下图 参数简介enablevalidate默认状态对新插入的数据执行完整性约束检查对表中现有的数据执行完整性约束检查disablenovalidate对新插入的数据不执行完整性约束检查对表中现有的数据不执行完整性约束检查disablevalidate对新插入的数据不执行完整性约束检查对表中现有的数据执行完整性约束检查不允许对表执行DML操作即对数据进行增删查改等操作enablenovalidate对新插入的数据执行完整性约束检查对表中现有的数据不执行完整性约束检查
这里可能会问道为什么在disablevalidate的约束控制状态下不能执行DML操作也就是不能执行增删改等操作呢
个人理解我想是因为disablevalidate这两个概念刚好起了冲突①首先disable是针对新增的数据进行检查。②其次validate是针对表中已有的数据进行检查③但是当数据新增进来的时候新增的数据在某一个时刻有两种身份即作为新增的数据又是表中现有的数据那么DBMS到底应不应该检查呢所以这时就产生了矛盾才会导致不允许执行DML操作
kimi解释当DISABLE和VALIDATE组合在一起时由于DISABLE允许插入不符合约束条件的数据而VALIDATE在数据变化时会检查约束条件这会导致在插入数据时不会检查约束但在后续的检查中发现数据不符合约束条件从而产生矛盾和数据不一致的问题
延迟约束
1、概述
KES支持延迟检测约束的功能便于在数据迁移和应用数据批量导入时保证成功率和操作效率
2、deferrable(可延迟的)和not deferrable(不可延迟的)这两个选项控制了该约束是否能被延迟。一个不可延迟的约束将在每一次数据插入或修改后立刻被检查可延迟的检查将被推迟到事务结束时进行
3、initially immediate(立即检查)和initially deferred(延迟检查)延迟判断规则如果该定义约束时指定为initially immediate那么它在每一次数据插入或修改后被检查这是默认值。如果指定为intially immediate那么它会在每一次数据插入或修改后被检查这是默认值。如果指定为initially deferred那么它只会在事务结束之后被检查
4、用户可以在事务中设置延迟约束的延迟属性
SET CONSTRAINTS {ALL | constraint_name[,...]} {DEFERRED | IMMEDIATE}实验1主键约束
t01表的结构如下图 t02表的结构如下图 创建表时定义主键约束
1、在列级定义主键约束
创建t01表并在id列上创建主键约束
test# create table t01(id integer primary key,name text,score numeric);2、在表级定义主键约束
将刚刚创建的t01表删除(无论是否存在的方法)创建t01表并在id列上创建主键约束
test# drop table if exists t01;
test# create table t01(id integer,name text,score numeric,constraint ID primary key(id));修改表时增加主键约束
1、将刚刚创建的t01表删除(无论是否存在的方法)
test# drop table if exists t01;2、创建t01表
test# create table t01(id integer,name text,socre numeric);3、修改t01为id列创建主键约束
test# alter table t01 add constraint ID primary key(id);验证主键约束
1、往t01表插入2条id相同的数据将会产生报错
test# insert INTO t01(id) values (100);
INSERT 0 1
test# insert INTO t01(id) values (100); 实验2外键约束
创建外键约束
1、在被参照表中创建主键约束
创建t01表并在id列上定义主键约束因为在外键约束的形成中被参照表中的被引用字段需要是主键约束或唯一约束
test# create table t01(id integer primary key,name text,score numeric);2、在参照表中创建外键约束
创建t02表并在no列上定义外键约束引用t01表的id主键列
test# create table t02(no integer,name text,constraint t02_fk foreign key (no) REFERENCES t01(id));3、验证外键约束
往t01表插入1行数据
往t02表插入2行数据其中1行数据违反外键约束
test# insert INTO t01(id) values (100);
test# insert INTO t02(no) values (100);
test# insert INTO t02(no) values (200);外键约束级联控制
注
①存在外键依赖时不可直接对父表的主键列执行更新操作
②使用ON DELETE CASCADE 选项可以在删除父表元组时实现级联删除子表元组
③使用ON DELETE SET NULL 选项可以在删除父表元组时实现级联更新子表元组的外键列为空值
1、创建t02表在no列上创建主键约束
test# create table t02(no integer primary key,name text); 2、创建t01表在id列上创建外键约束参照t02表的no列并设置为级联控制
test# create table t01 (id integer,name text,score numeric,constraint t02_fk foreign key (id) references t02(no) on delete cascade);3、往t02表中插入1条记录往t01表中插入1条满足外键约束的记录
test# insert INTO t02(no) values (100);
test# insert INTO t01(id) values (100);4、更新t02表的no字段该操作会因存在外键关联导致更新主键列失败
test# update t02 SET no 200 where no 100;5、删除t02表中的记录(因为子表中参照的外键设置为级联删除。所以可成功删除父表元组)
test# delete from t02 where no100;6、查看t01表的记录
test# select * from t01;id | name | score
-----------------
(0 行记录)实验3非空约束
创建表时定义非空约束
1、创建t01表并在id列上创建非空约束
test# create table t01(id integer not null,name text,score numeric);修改表时增加非空约束
1、修改t01表时并在name列上创建非空约束
test# alter table t01 alter COLUMN name set not null;2、使用元命令查看t01表的结构信息
test# \d t01数据表 public.t01栏位 | 类型 | 校对规则 | 可空的 | 预设
------------------------------------------id | integer | | not null | name | text | | not null | score | numeric | | | 验证非空约束
1、往t01表中插入2条记录其中1行数据违反非空约束
test# insert INTO t01 values(100,aaa,1);
test# insert INTO t01 values(100,null,2);实验4唯一约束
创建表时定义唯一约束
1、创建t01表并在id列上创建唯一约束
test# create table t01 (id integer unique,name text,score numeric);修改表时增加唯一约束
1、修改t01表并在name列上增加唯一约束
test# alter table t01 add constraint un_t01 unique(name);验证唯一约束
1、往t01表中插入2条记录其中1行数据违反唯一约束
test# insert INTO t01 values (100,aaa,1);
test# insert into t01 values (100.aaa,2);实验5check约束
创建表时定义check约束
1、创建t01表并在score列上创建check约束要求score取值必须大于0
test# create table t01(id integer,name text,score numeric check(score0));修改表时增加check约束
1、修改t01表并在socre列上增加check约束要求score取值必须小于100
test# alter table t01 add constraint check_score_t01 check(id100);验证check约束
1、往t01表中插入2条记录其中1行数据违反check约束
test# insert INTO t01(id) values (99);
test# insert INTO t01(id) values (100);实验6约束的状态控制
enablevalidate
1、创建测试表t01并为id列定义check约束要求id列值必须大于10
test# create table t01(id integer,name text,score numeric,constraint T01_CK check(id10));2、插入一条无效的数据到t01表中(执行报错)
test# insert INTO t01(id) values (9);
错误: 关系 t01 的新列违反了检查约束 t01_ck
描述: 失败, 行包含(9, null, null).3、通过数据字典表(dba_constraints)查看约束的信息(constraint_name,status,validated)数据字典表示t01表时需要使用大写即针对T01表进行查询
test# select constraint_name,status,validated from dba_constraints where table_nameT01;constraint_name | status | validated
------------------------------------T01_CK | ENABLE | VALIDATED
(1 行记录)disablenovalidate
1、修改t01表的t01_ck约束为disable novalidate
test# alter table t01 disable novalidate constraint t01_ck ;2、插入一条无效的数据到t01表中(执行成功)
test# insert INTO t01(id) values (9);
INSERT 0 13、插入一条有效的数据到t01表中(执行成功)
test# insert INTO t01 (id) values (11);
INSERT 0 14、通过数据字典表查看约束的信息
test# select constraint_name,status,validated from dba_constraints where table_nameT01;constraint_name | status | validated
---------------------------------------T01_CK | DISABLE | NOVALIDATED
(1 行记录)disablevalidate
1、调整约束t01_ck为disable validate状态会产生报错(因为在上面的实验中我们插入了违规数据)
test# alter table t01 disable validate constraint t01_ck ;
错误: 一些行违反了检查约束 t01_ck2、删除违规数据
test# delete from t01 where id 9;3、调整约束t01_ck为disable validate状态成功
test# alter table t01 disable validate constraint t01_ck ;
ALTER TABLE4、对t01表执行DML操作应该是执行失败的
test# insert INTO t01(id) values(12);
错误: 不对表 t01 进行插入/更新/删除在禁用并验证约束 t01_ck 的情况下
test# update t01 SET id12 where id11;
错误: 不对表 t01 进行插入/更新/删除在禁用并验证约束 t01_ck 的情况下
test# delete from t01 where id11;
错误: 不对表 t01 进行插入/更新/删除在禁用并验证约束 t01_ck 的情况下5、通过数据字典查看约束的信息
test# select constraint_name,status,validated from dba_constraints where table_nameT01;constraint_name | status | validated
-------------------------------------T01_CK | DISABLE | VALIDATED
(1 行记录)enablenovalidate
1、禁用约束t01_ck(即disablenovalidate)插入一条违反约束的记录到t01表中
test# alter table t01 disable novalidate constraint t01_ck ;
ALTER TABLE
test# insert INTO t01(id) values (9);
INSERT 0 12、调整约束t01_ck为enable novalidate状态可以发现这是修改成功的因为只会对新增的数据进行检查而不会对已有的违规数据进行检查
test# alter table t01 enable novalidate constraint t01_ck ;3、插入一条违反约束的记录到t01表中如果不出意外的话应该是插入失败的因为DBMS对新增的数据进行了检查发现新增的数据是一条违规数据随后就被拒绝了
test# insert INTO t01(id) values (8);
错误: 关系 t01 的新列违反了检查约束 t01_ck
描述: 失败, 行包含(8, null, null).4、插入一条正常的记录到t01表中当然这是会成功的
test# insert INTO t01(id) values(12);
INSERT 0 1实验7延迟约束
创建延迟约束
1、创建测试表t01并在id列上创建主键约束
test# create table t01(id integer primary key,name text,score numeric); 2、创建测试表t02并未id列定义延迟外键约束参照t01表的id列
test# create table t02 (id int,name text,constraint t02_fk foreign key (id) references t01(id) initially deferred);3、显式开启事务
test# begin ;
BEGIN4、往子表t02中插入一条数据执行成功因为当前开启的事务而当前的延迟状态时deferrable(可延迟的)可延迟的检查将被推迟到事务结束时进行
test# insert INTO t02 values (20,a);5、往父表t01中插入一条数据
test# insert INTO t01 values (20,a,1);6、结束事务自动提交成功
test# end;7、通过数据字典表查看约束的信息
test# select constraint_name,deferred from user_constraints where table_nameT02;constraint_name | deferred
---------------------------T02_FK | DEFERRED
(1 行记录)在事务中控制延迟约束
在事务中执行约束可以使得我们原本在非延迟的环境下进入事务后改为延迟的环境在进行数据的增删改从而达到退出事务后环境依然是而非延迟的环境。总之在事务中控制延迟约束可以达到在非延迟的环境下做到延迟的环境下事情这一切都要归功于事务的强大性能
注①、延迟约束只能在事务中使用。②提交事务时总会验证表中的数据是否满足约束不满足时导致事务失败
1、修改t02表的t02_fk约束为非延迟
test# alter table t02 alter CONSTRAINT t02_fk initially immediate;2、开启事务在事务中临时修改t02_fk约束为延迟约束
test# begin ;
BEGIN
test# alter table t02 alter CONSTRAINT t02_fk initially deferred;3、往子表t02中插入一条数据。执行成功因为在事务的影响下我们处在延迟约束的环境下的
test# insert INTO t02 values (30,abc); 4、往父表t02中插入一条数据
test# insert INTO t01 values (30,acac,8);5、结束事务自动提交成功
test# end;
COMMIT使用约束时的注意事项
1、一张表只能有一个主键约束其它非主键列可以结合使用唯一约束非空约束来实现类似的功能
2、check约束的定义必须是一个条件表达式表达式要有确定的结果要么为true要么为flase
3、我们将列的默认值定义也可以看作是一种定义在表上的约束用于列的自动填充的缺省值。这句话的意思是列的默认值其实也是一种约束这个约束的作用是帮助列自动填充缺省值
4、定义外键约束的列可以与被参照的父表中的列拥有不同的名称但列的数据类型必须要匹配。就是外键约束参照表的参照字段与被参照表的被参照字段的数据类型必须要匹配
