时间戳

首页 > 解决方案 > 对相关表的约束

sql - 对相关表的约束

问题描述

我有 2 个表（ItemType 和 Item）。ItemType 有一个 boolean hasSize，并且 Item 表有一个size列。

我想强制size仅在hasSizeboolean = true 时设置。

我已经看到讨论使用触发器进行验证的解决方案。但是，这并不能提供与约束相同的鲁棒性。

这是非理想模式设计的问题，还是有合适的解决方案？

标签： sqlpostgresql

解决方案

检查约束不能引用数据库中的其他表，触发器是解决此限制的有效策略。我不认为您的架构设计不好。某些类型的项目是相当大的，并且该信息属于item_type表中。

这是不使用触发器的替代解决方案，其中我使用修饰符对UPDATABLE VIEW施加检查约束以防止基础表中的数据修改WITH CHECK OPTION。请注意，这只会验证数据是否通过视图插入或更新。您必须授予适当的权限，以便应用程序角色不能item直接修改表，但可以通过UPDATABLE VIEW

这是一个独立的脚本，其中包含示例数据和示例插入失败和插入成功

-- set up dummy `item_type` table
CREATE TABLE item_type (
id int primary key generated by default as identity,
has_size bool,
name text not null unique
);
INSERT INTO item_type 
  (has_size, name) 
VALUES 
  (true, 'sizable type')
, (false, 'unisize');

-- set up dummy `item` table
CREATE TABLE item (
id int primary key generated by default as identity,
type_id int references item_type(id),
name text NOT NULL,
size text
);

INSERT INTO item
  (type_id, name, size)
VALUES
  (1, 'worlds best dad tee shirt', 'M')
, (2, 'usb key', NULL);

-- create updatable check view.
CREATE VIEW item_view AS
SELECT item.*
FROM item 
WHERE EXISTS (
SELECT 
FROM item_type 
WHERE item.type_id = item_type.id
  AND (item.size IS NOT NULL) = item_type.has_size
) 
WITH CASCADED CHECK OPTION; 
--    ^^^
-- i could have alternately used local 
-- instead of cascaded

此插入将失败

INSERT INTO item_view 
  (type_id, name, size) 
VALUES
  (2, 'dad socks', 'M');

它会产生以下错误：

ERROR:  new row violates check option for view "item_view"
DETAIL:  Failing row contains (3, 2, dad socks, M).

此插入将成功

INSERT INTO item_view 
  (type_id, name, size) 
VALUES
  (2, 'dad socks', NULL);

SELECT * FROM item按预期返回以下结果

 id | type_id |           name            | size
----+---------+---------------------------+------
  1 |       1 | worlds best dad tee shirt | M
  2 |       2 | usb key                   |
  4 |       2 | dad socks                 |

推荐阅读