当许多表与同一个表具有1:1关系时,如何保证唯一性? [英] How to guarantee uniqueness when many tables has a 1:1 relationship with the same table?
问题描述
假设我有一个具有以下模型的场景: Animal
表表示任何动物, Dog
表格和一个 Bird
表格,每个表格与 Animal
表格之间的关系为1:1。
动物
INTEGER ID(PK)
STRING名称
Bird
INTEGER id(PK FK引用`Animal.id`)
狗
INTEGER id(PK FK引用`Animal.id`)
$ b
如何保证给定的行在 Dog
表中将有 JUST 一行引用的 Dog
code> Bird 表?模型本身允许...
动物不能是狗
和 Bird
(不是神话,但不是这样:P )
任何提示都会感激:)
这可能是什么@Pranay的意思,但答案是不完整的。向所有表添加一个TYPE列,然后按如下所示进行约束:
创建表Animal(id整数,
类型字符串,
名称字符串,
主键(id),
唯一(id,类型)
);
create table Bird(id integer,
type string default'BIRD'check(type ='BIRD'),
主键(id),
外键(id,type)引用Animal(id,type)
);
create table Dog(id integer,
type string default'DOG'check(type ='DOG'),
主键(id),
外键(id,type)引用Animal(id,type)
);
请参阅 David Portas的博客对此有很好的解释。
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.
Animal
INTEGER id (PK)
STRING name
Bird
INTEGER id (PK FK referencing `Animal.id`)
Dog
INTEGER id (PK FK referencing `Animal.id`)
(I'm giving only the keys just to be clear)
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...
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 :)
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)
);
See David Portas's blog fora good explanation of this.
这篇关于当许多表与同一个表具有1:1关系时,如何保证唯一性?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!