pg学习_基本表定义_约束关系

约束关系

1、非空约束
highgo=# create table test1(
highgo(# num int unique,
highgo(# name varchar(10)
highgo(# );
注意:  CREATE TABLE / UNIQUE 将要为表 "test1" 创建隐含索引 "test1_num_key"
CREATE TABLE
插入两条合法记录
highgo=# insert into test1 values(1,'adam');
INSERT 0 1
highgo=# insert into test1 values(2,'eva');
INSERT 0 1
highgo=# select * from test1;
 num | name 
-----+------
   1 | adam
   2 | eva
(2 rows)
插入一条非法记录，违反了非空约束
highgo=# insert into test(no) values(002);
错误:  在字段 "name" 中空值违反了非空约束
DETAIL:  失败, 行包含(2, null).

2、唯一约束
highgo=# create table test1(
highgo(# num int unique,
highgo(# name varchar(10)
highgo(# );
注意:  CREATE TABLE / UNIQUE 将要为表 "test1" 创建隐含索引 "test1_num_key"
CREATE TABLE
插入合法记录
highgo=# insert into test1 values(1,'adam');
INSERT 0 1
highgo=# insert into test1 values(2,'eva');
INSERT 0 1
highgo=# select * from test1;
 num | name 
-----+------
   1 | adam
   2 | eva
(2 rows)
插入非法记录
highgo=# insert into test1 values(2,'lilith');
错误:  重复键违反唯一约束"test1_num_key"
DETAIL:  键值"(num)=(2)" 已经存在
注意：
如果该唯一值字段，插入空值的时候，可以插入多条记录
highgo=# insert into test1(name) values('SACHIEL');
INSERT 0 1
highgo=# insert into test1(name) values('SHAMSHIEL');
INSERT 0 1
highgo=# select * from test1;
 num |   name    
-----+-----------
   1 | adam
   2 | eva
     | SACHIEL
     | SHAMSHIEL
(4 rows)

3、主键约束
主键约束相当于非空约束和唯一约束的组合。这种约束不仅能保证字段只能取非空值， 而且保证字段的值在表中唯一。
highgo=# create table test3(
highgo(# num int primary key,
highgo(# name varchar(10)
highgo(# );
注意:  CREATE TABLE / PRIMARY KEY 将要为表 "test3" 创建隐含索引 "test3_pkey"
CREATE TABLE

4、外键约束
外键约束主要用于保证数据库的参照完整性。
highgo=# create table test4(
highgo(# snum int references test3,
highgo(# score int);
CREATE TABLE
highgo=# insert into test4 values(3,88);
错误:  插入或更新表 "test4" 违反外键约束 "test4_snum_fkey"
DETAIL:  键值对(snum)=(3)没有在表"test3"中出现.
highgo=# insert into test4 values(2,88);
INSERT 0 1
注意:
on update cascade (父表修改，子表也修改)
on delete cascade (父表删除，子表也删除)
on delete set null (父表删除，子表设置为 null)
highgo=# create table test5(
highgo(# snum int references test3
highgo(# on update cascade
highgo(# on delete set null,score int);
CREATE TABLE
highgo=# insert into test5 values(1,99);
INSERT 0 1
highgo=# insert into test5 values(2,88);
INSERT 0 1
highgo=# select * from test5;
 snum | score 
------+-------
    1 |    99
    2 |    88
(2 rows)

highgo=# update test3 set num=8 where name='lilith';
错误:  在 "test3" 上的更新或删除操作违反了在 "test4" 上的外键约束 "test4_snum_fkey"
DETAIL:  键值对(num)=(2)仍然是从表"test4"引用的.
highgo=# insert into test3 values(3,'SACHIEL');
INSERT 0 1
highgo=# update test3 set num=8 where name='SACHIEL';
UPDATE 1
highgo=# select * from test3;
 num |  name   
-----+---------
   1 | adam
   2 | lilith
   8 | SACHIEL
(3 rows)

highgo=# update test3 set num=4 where name='adam';
UPDATE 1
highgo=# select * from test3;
 num |  name   
-----+---------
   2 | lilith
   8 | SACHIEL
   4 | adam
(3 rows)

highgo=# delete from test3 where num=4;
DELETE 1
highgo=# select * from test5;
 snum | score 
------+-------
    2 |    88
      |    99
(2 rows)

5、数据检验
highgo=# create table test6
highgo-# (num int,name varchar(10),sex varchar(1) check(sex in('y','n')));
CREATE TABLE
highgo=# insert into test6 values(1,'aaa','y');
INSERT 0 1
highgo=# insert into test6 values(2,'bbb','n');
INSERT 0 1
highgo=# select * from test6;
 num | name | sex 
-----+------+-----
   1 | aaa  | y
   2 | bbb  | n
(2 rows)

highgo=# insert into test6 values(2,'bbb','a');
错误:  关系 "test6" 的新列违反了检查约束 "test6_sex_check"
DETAIL:  失败, 行包含(2, bbb, a).