怎样创作网站,查询公司名字是否被注册,seo优化与sem推广有什么关系,网站内容要突出什么原因主键与外键-表的约束(一) 专栏内容#xff1a; postgresql内核源码分析手写数据库toadb并发编程 个人主页#xff1a;我的主页 管理社区#xff1a;开源数据库 座右铭#xff1a;天行健#xff0c;君子以自强不息#xff1b;地势坤#xff0c;君子以厚德载物. 系列文章…主键与外键-表的约束(一) 专栏内容 postgresql内核源码分析手写数据库toadb并发编程 个人主页我的主页 管理社区开源数据库 座右铭天行健君子以自强不息地势坤君子以厚德载物. 系列文章
入门准备postgrersql基础架构快速使用初始化集群数据库服务管理psql客户端使用pgAdmin图形化客户端数据库的使用创建数据库数据库操作表的使用表的创建表的操作数据查询数据查询多表联合查询数据操作插入数据的方式 文章目录 主键与外键-表的约束(一)系列文章一、概述 约束的定义约束的分类 二、主键约束 定义主键约束修改添加主键约束删除主键约束 三、外键约束 定义外键约束修改表添加外键约束删除外键约束自引用外键被引用表的变动影响数据变动表删除 四、总结 五、结尾 一、概述 在数据库中数据类型可以限制数据存储的大小也能在一定程度上限制存储的数据种类但是对于数据库应用来讲它太宽泛了比如有些表示人名的字段就不能为空货物数量的字段不能为负值这与实际生活符合而数据类型并不能做这些约束这就需要数据库提供一套更贴近应用或者说与现实世界更符合的规则来约束数据的有效性。
约束的定义
数据库的约束是一种规则用于限制或规范数据库中的数据确保数据的完整性和一致性。这些约束可以定义在表级别或列级别处理机制是一致的。约束不占用任何数据库空间而是存在于数据字典中并在执行SQL期间使用。用户可以指明约束是启用的还是禁用的当约束启用时它增强了数据的完整性。
约束的分类
postgresql数据库中的约束类型主要包括以下几种
主键约束Primary Key主键约束相当于唯一约束和非空约束的组合。它确保表中的每一行数据都有一个唯一标识符不允许重复也不允许出现空值。每个表最多只允许一个主键。当创建主键约束时系统默认会在所在的列或列组合上建立对应的唯一索引。外键约束Foreign Key外键约束用于保证一个或两个表之间的参照完整性。它构建于一个表的两个字段或是两个表的两个字段之间的参照关系。唯一约束Unique唯一约束要求指定的表列或列组合的值不能重复保证数据的唯一性。同一个表可以有多个唯一约束。在创建唯一约束时如果不给唯一约束名称就默认和列名相同。与主键约束不同唯一约束允许空值的存在但只能出现一个空值。非空约束NOT NULL非空约束用于确保当前列的值不为空值。它只能出现在表对象的列上。检查约束Check检查约束会判断指定的条件是否为true当为true时符合约束。排除约束(Exclude): 在排除约束指定的表达式返回false或null时才符合约束。
这些约束在数据库设计中起着至关重要的作用它们有助于维护数据的准确性和一致性防止无效数据的插入和更新。 二、主键约束 在表的列上指定一列或几列的组合创建主键这一列或列的组合就称为主键列主键列上的所有行的数据不能重复同时主键列的值不能为空。
在每个表上只能最多有一个主键。
建议在每张表上都创建一个主键因为在建立主键的同时数据库会自动在主键列上创建索引叫做主键索引它可以加束通过主键的查找或者修改数据。
下面列举几创建主键的SQL语法有几种形式。
定义主键约束
一是在定义列的时候就指定主键这适合主键列只有一列的情况
CREATE TABLE table_name (column1 data_type PRIMARY KEY, column2 data_type,…
);另一种是约束定义写在表定义的最后这适合于主键列包含多列的情况
CREATE TABLE table_name (column_1 data_type, column_2 data_type,column_3 data_type,…PRIMARY KEY(column_1, column2, ...)
);不管是主键列有一列或者多列两种写法都可以使用。
修改添加主键约束
当我们的表已经创建完成时或者数据已经导入此时发现需要增加一个主键那么可以通过修改表定义的方式来添加主键SQL语法如下
ALTER TABLE table_name
ADD PRIMARY KEY (column_1, column_2, ...);当然这个操作需要有表的alter权限。
删除主键约束
有创建主键就可以删除主键删除主键约束的SQL语法如下
ALTER TABLE table_name
DROP CONSTRAINT primary_key_constraint_name;其中primary_key_constraint_name是主键约束的名称在上面的创建方法中我们并没有指定主键约束的名称数据库会自动为我们增加主键约束的名称在删除之前需要通过\d命令来查看一下表的定义示例如下
postgres# \d productsTable public.productsColumn | Type | Collation | Nullable | Default
--------------------------------------------------------------------product_id | integer | | not null |product_name | character varying(255) | | not null |price | numeric(10,2) | | not null |category | character varying(255) | | |
Indexes:products_pkey PRIMARY KEY, btree (product_id)
Referenced by:TABLE orders CONSTRAINT orders_product_id_fkey FOREIGN KEY (product_id) REFERENCES products(product_id)postgres#
products表在product_id列上有一个主键名称为products_pkey因为它同时会创建索引所以在索引分类中。
注意这里有可能会被别的表以外键方式引用就会删除失败此时的处理方法请看下一节外键约束。 三、外键约束 当定义一张表时期望其中一列的数据与另一张表的某一列的数据一致时就需要用到外键约束比如定单表中的产品ID列就需要与产品表中的产品ID列一致不能出现产品表中没有的产品ID。
外键约束是指一列或几列的值与另一张表的对应的一列或几列出现的值相匹配我们说这两张相关表保持了引用完整性。
注意外键通过引用另一张表的唯一能标识行的一列或一组列的值来建立引用关系所以被引用的列必须是主键的列。当然引用与被引用的列的类型是必须相同的也就是它们有相同的值范围。
外键约束的定义SQL语法同样也有几种。
定义外键约束
CREATE TABLE table_name1 (column11 data_type , column12 data_type REFERENCES table_name2 (column21),…
);在需要定义引用完整性的列定义后面使用references关键字再加被引用的表外与列名即可。
另一种是放在表定义的最后可以指定单列或多列的引用完整性。
CREATE TABLE table_name1 (column11 data_type , column12 data_type ,…FOREIGN KEY (column11, column12) REFERENCES table_name2 (column21, column22)
);放在表定义后部时需要使用foreign key关键字开头来指定本表需要引用的字段。
修改表添加外键约束
当数据表创建完成后可以通过修改表的定义来完成外键的添加
ALTER TABLE table_name1
ADD CONSTRAINT foreign_key_constraint_name
FOREIGN KEY (column11 ...)
REFERENCES table_name2 (column22 ...);这里可以是单列或者多列组的引用。
删除外键约束
当不需要外键约束时可以删除表上某个外键外键约束的名称通过查看表定义来获得在命令行客户端上通过\d tablename命令查看。
ALTER TABLE table_name1
DROP CONSTRAINT foreign_key_constraint_name;自引用外键
除了引用另一张表之外还可以引用自身表的列这也是非常有用的一种写法。
CREATE TABLE tree (node_id integer PRIMARY KEY,parent_id integer REFERENCES tree,name text,...
);被引用表的变动影响
因为外键是引用另一张表当被引用表发生变动时也会影响引用表也就是外键表。
一般会发生两类变动
一是被引用表的数据发生变化当然新增数据不会产生影响当原有数据被delete删除或update修改时引用表中正在引用的数据不见了就会产生错误二是被引用表要被删除时也不能直接删除
数据变动
假如一个订单表的产品ID列引用产品表的ID列那么会存在这样一个情况产品不再有效时会从产品表中删除那么此时订单表中已经产生的此产品的订单将会如何处理呢
可能是不让它删除这在一些应用中也是如此处理另外会加一个是否删除的字段或者把订单表中引用此产口ID的订单一起删除这看起来不是很好一起来看看数据库中提供了那些可选项。
在定义外键时可以指定它的引用数据发生变动时可以执行的动作语法如下
CREATE TABLE table_name1 (column11 data_type , column12 data_type ,…FOREIGN KEY (column11, column12) REFERENCES table_name2 ON DELETE [action] (column21, column22)
);
还可以是
CREATE TABLE table_name1 (column11 data_type , column12 data_type ,…FOREIGN KEY (column11, column12) REFERENCES table_name2 ON UPDATE [action] (column21, column22)
);
因为被引用表的数据有删除或更新两种情况在这两种情况下引用表需要指定动作其中的action可以用以下动作代替
NO ACTION, 在没有指定时默认就是没有动作也就是在修改和删除被引用表的数据时会检查是否有引用如果有引用则会报错也就是阻止被修改和删除RESTRICT, 与on action类似会阻上被引用列的修改或删除但是restrict会将检查延迟处理SET NULL, 将被引用列删除的行在引用列中置为NULLSET DEFAULT当被引用列删除时将引用例置为默认值这里可以指定在此情况下的默认值CASCADE级联处理也就是当被引用列删除时引用列的所有该值的行会自动删除
这部分我们将在单独的一个章节点来分享。
表删除
与数据变动有类似的问题被引用表删除时或者主键删除时引用表如何处理呢
在postgresql中会阻止被引用表的删除必须先解除引用或者先删除引用表这里提供了一个cascade选项可以级联删除引用表。
postgres# \d order_itemsTable test1.order_itemsColumn | Type | Collation | Nullable | Default
---------------------------------------------------product_no | integer | | |order_id | integer | | |quantity | integer | | |
Foreign-key constraints:order_items_order_id_fkey FOREIGN KEY (order_id) REFERENCES orders(order_id)order_items_product_no_fkey FOREIGN KEY (product_no) REFERENCES products(product_no)查看一下外键关系可以看到order_items同时引用了orders和products两张表的列下面选择将products表删除并指定级联删除选项
postgres# drop table products cascade ;
NOTICE: drop cascades to constraint order_items_product_no_fkey on table order_items
DROP TABLE
postgres# \dList of relationsSchema | Name | Type | Owner
------------------------------------test1 | order_items | table | zpzhaotest1 | orders | table | zpzhao
(2 rows)
可以看到删除表的同时会自动将引用关系解除删除了order_items表上对products表的外键引用。
四、总结 本文主要分享了表的约束分类中的主键和外键两类它们对数据的数据的实体完整性和参照完整性都会起到约束作用当然在使用过程中要熟悉它们的SQL语法以及它们的特性。
五、结尾 非常感谢大家的支持在浏览的同时别忘了留下您宝贵的评论如果觉得值得鼓励请点赞收藏我会更加努力 作者邮箱studysenllang.onaliyun.com 如有错误或者疏漏欢迎指出互相学习。
注未经同意不得转载
