与主义具体遗产的外键关系 [英] foreign-key relationship with Doctrine concrete inheritance
问题描述
在我的架构中,我有一个泛型表 Animal
和一个继承的表 Dog
。
在使用doctrine之前,我用一个继承的id来引用这个模式,引用通用的id作为外键。
我不能用Doctrine复制相同的东西,我觉得有些东西丢失了。
我以前生成的模式如下:
CREATE TABLE`animal`(
`id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
`color` VARCHAR(20 )NOT NULL,
PRIMARY KEY(`id`)
);
CREATE TABLE`dog`(
`id` INT UNSIGNED NOT NULL,
`breed` VARCHAR(40)NOT NULL,
KEY`id`(` id`)
);
ALTER TABLE`dog` ADD CONSTRAINT FOREIGN KEY(`id`)参考`animal`(`id`);
我首先尝试使用Doctrine具体的继承,因为它似乎是这个问题的逻辑回答: p>
这是YAML文件:
动物:
/ pre>
列:
id:{primary:true,type:integer,autoincrement:true}
color:{type:string(20),notnull:true}
Dog:
columns:
种:{type:string(20),notnull:true}
继承:
extends:Animal
type:concrete
生成的SQL是:
CREATE TABLE`动物(
`id` BIGINT NOT NULL AUTO_INCREMENT,
`color` VARCHAR(20)NOT NULL,
PRIMARY KEY(`id`)
);
CREATE TABLE IF NOT EXISTS`dog`(
`id` BIGINT NOT NULL AUTO_INCREMENT,
`color` VARCHAR(20)NOT NULL,
` VARCHAR(20)NOT NULL,
PRIMARY KEY(`id`)
);
重复的
颜色
列可以,但外键在哪里?如何使用我的animal.id
确保我的dog.id
的完整性?如果我删除动物
的行会发生什么?
所以我试图使用一个简单的一对一一个关联:
动物:
列:
id:{primary:true,type:integer 10),autoincrement:true}
color:{type:string(20),notnull:true}
狗:
列:
animal_id:{primary:true,type: integer(10)}
breed:{type:string(20),notnull:true}
关系:
AnimalRecord:
class:Animal
foreignAlias:DogRecord
type:one
foreignType:one
local:animal_id
foreign:id
结果与上述相同(除了
颜色
列不重复,这是正常的,因为继承不被解释),仍然没有外键。
如果我只是从
PRIMARY $ c中更改
animal_id
$ c>到UNIQUE
,外键创建mdog.animal_id
toanimal.id
,但是一个新的自动递增的id
出现。
它的行为就像是
PRIMARY
或FOREIGN KEY
是专属列,我不明白为什么。此外,在我看来似乎是一个危险的缺陷。解决方案你可能想重新阅读有关具体遗产的文档:动物表将永远是空的,因为当您使用此继承策略类型时,所有关于狗medor的数据将被存储在狗表中。这就是为什么没有必要建立动物和狗类之间的关系。
如果你愿意,动物就像一个抽象类。
对我来说,您的架构应如下所示:
动物:
列:
id:{primary:true,type:integer(10),autoincrement:true}
color:{type:string(20),notnull:true}
Dog:
columns:
breed:{type:string(20),notnull:true}
In my schema, I have a generic table
Animal
and an inherited tableDog
. Before using doctrine, I used to implement this pattern with an inherited id referencing the generic id as foreign key. I can't reproduce the same with Doctrine, and I feel like something is missing.The schema I used to produce is the following :
CREATE TABLE `animal` ( `id` INT UNSIGNED NOT NULL AUTO_INCREMENT, `color` VARCHAR(20) NOT NULL, PRIMARY KEY (`id`) ); CREATE TABLE `dog` ( `id` INT UNSIGNED NOT NULL, `breed` VARCHAR(40) NOT NULL, KEY `id` (`id`) ); ALTER TABLE `dog` ADD CONSTRAINT FOREIGN KEY (`id`) REFERENCES `animal`(`id`);
I first tried to use Doctrine concrete inheritance, as it seemed the logical answer to this problem :
Here is the YAML file :
Animal: columns: id: { primary: true , type: integer , autoincrement: true } color: { type: string(20) , notnull: true } Dog: columns: breed: { type: string(20) , notnull: true } inheritance: extends: Animal type: concrete
And the resulting SQL is :
CREATE TABLE `animal` ( `id` BIGINT NOT NULL AUTO_INCREMENT, `color` VARCHAR(20) NOT NULL, PRIMARY KEY (`id`) ); CREATE TABLE IF NOT EXISTS `dog` ( `id` BIGINT NOT NULL AUTO_INCREMENT, `color` VARCHAR(20) NOT NULL, `breed` VARCHAR(20) NOT NULL, PRIMARY KEY (`id`) );
The duplication of the
color
column is OK, but where is the foreign key ? How do I ensure the integrity of mydog.id
with myanimal.id
? And what happens if I deleteanimal
's rows ?So I tried to use a simple one-to-one association :
Animal: columns: id: { primary: true , type: integer(10) , autoincrement: true } color: { type: string(20) , notnull: true } Dog: columns: animal_id: { primary: true , type: integer(10) } breed: { type: string(20) , notnull: true } relations: AnimalRecord: class: Animal foreignAlias: DogRecord type: one foreignType: one local: animal_id foreign: id
The result is the same as above (except the
color
column isn't duplicated, which is normal since the inheritance isn't explicited anymore), still no foreign key.If I just change the
animal_id
fromPRIMARY
toUNIQUE
, the foreign key is created fromdog.animal_id
toanimal.id
, but a new autoincrement'edid
appears.It all behaves like being
PRIMARY
orFOREIGN KEY
are exclusive for a column, and I can't understand why. Furthermore, it seems to me like a dangerous flaw.解决方案you may want to re-read the documentation on concrete inheritance : the animal table will always be empty, because all the data you will give about the dog "medor" will be stored in the dog table when you use this inheritance strategy type. That's why there is no point to create a relation between animal and dog classes.
Animal is like an abstract class if you will.
To me, your schema should look like this:
Animal: columns: id: { primary: true , type: integer(10) , autoincrement: true } color: { type: string(20) , notnull: true } Dog: columns: breed: { type: string(20) , notnull: true }
这篇关于与主义具体遗产的外键关系的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!