当N个表与一个公共表具有1：1关系时，如何保证唯一性？ [英] How to guarantee uniqueness when N tables have 1:1 relationship with a common table?

问题描述

假设我有一个使用以下模型的场景：一个 Animal 表，它代表任何动物，一个 Dog 表和 Bird 表，每个表与 Animal 表具有1：1关系。

Lets suppose I have a scenario with the following model: An Animal table which represents any animal, a Dog table and a Bird table, each one with a 1:1 relationship with the Animal table.

动物

    INTEGER id (PK)
    STRING name

Bird

    INTEGER id (PK FK referencing `Animal.id`)

Dog

    INTEGER id (PK FK referencing `Animal.id`)                

（我只给出要清楚的键）

如何保证给定的行动物表中的只需在 Dog 或 Bird 表？该模型本身允许...

How can I guarantee that a given row in the Animal table will have JUST one referenced row in either the Dog or the Bird table? The model itself allows it...

动物不能是狗和 Bird 同时出现（不是神话，但事实并非如此：P ）

An animal can't be a Dog and a Bird at the same time (not in mythology but that's not the case :P)

如果可以仅通过使用模型而无需触发器就可以做到这一点，那就更好了。

It would be better if this could be done just by playing with the model, without triggers...

任何提示将不胜感激：）

Any tip would be appreciated :)

推荐答案

这可能就是@Pranay的意思，但答案不完整。将列类型添加到所有表中，然后像这样对其进行约束：

This may be what @Pranay meant, but the answer was incomplete. Add a column TYPE to all tables and then constrain it like this:

create table Animal (id integer, 
                     type string,
                     name string,
                     primary key (id),
                     unique (id, type)
                    );

create table Bird (id integer,
                   type string default 'BIRD' check (type='BIRD'),
                   primary key (id),
                   foreign key (id, type) references Animal (id, type)
                  );

create table Dog (id integer,
                  type string default 'DOG' check (type='DOG'),
                  primary key (id),
                  foreign key (id, type) references Animal (id, type)
                 );

请参见 David Portas的博客对此做了很好的解释。

See David Portas's blog fora good explanation of this.

这篇关于当N个表与一个公共表具有1：1关系时，如何保证唯一性？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！