多个表中的唯一键 [英] Unique key across multiple tables

问题描述

重写问题：

我有三个表，对象，所有者和objectOwnerRelation

I have three tables, objects, owners and objectOwnerRelation

对象有三列：id，name，type

objects has three columns: id, name, type

所有者有两列：id，owner

owners has two columns: id, owner

objectOwnerRelation有三列：id，objectId，ownerId

objectOwnerRelation has three columns: id, objectId, ownerId

objectId =对象中的id

objectId = id in objects

ownerId = id在所有者中

ownerId = id in owners

两个对象可以具有相同的名称和类型，但只有所有者不同。这意味着具有相同名称和类型的两个对象不能出现在具有相同ownerId的objectOwnerRelation中。

Two objects can have the same name and type but only if the owner is different. That means that two objects with the same name and type can't appear in the objectOwnerRelation with the same ownerId

对象可以有多个所有者，所有者可以拥有多个对象

An object can have multiple owners and a owner can have multiple objects.

推荐答案

无法强制执行约束：

两个对象可以具有相同的名称和类型，但只有所有者不同。

Two objects can have the same name and type but only if the owner is different.

仅使用声明性引用完整性（DRI）。只需使用DRI，就可以创建一个包含构建唯一键所需的所有列的表。您可以将 object.name 和 object.type 反向规范化到您的 objectOwnerRelation 表。这将确保在 objectOwnerRelation 中没有记录，具有相同的名称​​和类型和所有者，但只在交集中表格本身。

using only declarative referential integrity (DRI). Using just DRI you could create a table that contains all of the columns that you need to build a unique key. You could denormalize object.name and object.type to your objectOwnerRelation table. This would ensure that there are no records in objectOwnerRelation with the same name and type and owner but only in the intersection table itself.

在一天结束时， DRI不允许子表对父项施加约束。

您正在查找的约束需要使用触发器或其他代码在程序上强加。

The constraint that you are looking for needs to be imposed procedurally, using a trigger or some other code.

这篇关于多个表中的唯一键的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！