“多态"对于外键约束 [英] "polymorphism" for FOREIGN KEY constraints

问题描述

表格中有此字段:

room_id INT NOT NULL CONSTRAINT room_id_ref_room REFERENCES room

我有两个房间的三个2张桌子:standard_room和family_room

I have three 2 tables for two kinds of rooms: standard_room and family_room

如何做这样的事情:

room_id INT NOT NULL CONSTRAINT room_id_ref_room REFERENCES standard_room or family_room

我的意思是，room_id应该引用 standard_room 或 family_room.
有可能这样做吗?

I mean, room_id should reference either standard_room or family_room.
Is it possible to do so?

推荐答案

这是我一直在使用的模式.

Here is the pattern I've been using.

CREATE TABLE room (
    room_id serial primary key,
    room_type VARCHAR not null,

    CHECK CONSTRAINT room_type in ("standard_room","family_room"),
    UNIQUE (room_id, room_type)
);

CREATE_TABLE standard_room (
    room_id integer primary key,
    room_type VARCHAR not null default "standard_room",

    FOREIGN KEY (room_id, room_type) REFERENCES room (room_id, room_type),
    CHECK CONSTRAINT room_type  = "standard_room"
);
CREATE_TABLE family_room (
    room_id integer primary key,
    room_type VARCHAR not null default "family_room",

    FOREIGN KEY (room_id, room_type) REFERENCES room (room_id, room_type),
    CHECK CONSTRAINT room_type  = "family_room"
);

也就是说，通过类型描述符列，超类的子类"点(使得指向的基类是正确的类型，并且超类的主键与子类.

That is, the 'subclasses' point at the super-class, by way of a type descriminator column (such that the pointed to base class is of the correct type, and that primary key of the super class is the same as the child classes.

这篇关于“多态"对于外键约束的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！