时间戳

首页 > 解决方案 > 转换为范式

sql - 转换为范式

问题描述

有区域、化身和区域实例。Avatar 必须属于每个区域的零个或一个实例。

CREATE TABLE zones (
    id SERIAL NOT NULL PRIMARY KEY,
    name VARCHAR NOT NULL,
    ...
);

CREATE TABLE avatars (
    id SERIAL NOT NULL PRIMARY KEY,
    ...
);

CREATE TABLE instances (
    id SERIAL NOT NULL PRIMARY KEY,
    zone_id INTEGER REFERENCES zones NOT NULL,
    ...
);

CREATE TABLE avatar_instances (
    avatar_id INTEGER REFERENCES avatars NOT NULL,
    zone_id INTEGER REFERENCES zones NOT NULL,
    instance_id INTEGER REFERENCES instances NOT NULL,
    PRIMARY KEY(avatar_id, zone_id)
);

我对上面的模式不满意，因为zone_id在每条记录中avatar_instances都必须与zone_id相应instances行的内部一致。

理想情况下，我希望 avatar_instances 上的唯一索引“到达”instances表的内部以查看instances.zone_id.

例如

CREATE TABLE avatar_instances (
    avatar_id INTEGER REFERENCES avatars NOT NULL,
    instance_id INTEGER REFERENCES instances NOT NULL,
    PRIMARY KEY(avatar_id, instance.zone_id)
);

如何将此模式转换为第 N 范式，同时保留“每个化身必须属于每个区域的零个或一个实例”的限制？

标签： sqlpostgresqlconstraints

解决方案

创建一个唯一并添加一个引用唯一的复合 FK。

CREATE TABLE instances (
    id SERIAL NOT NULL PRIMARY KEY,
    zone_id INTEGER REFERENCES zones NOT NULL,
    UNIQUE (zone_id, id)
);

CREATE TABLE avatar_instances (
    avatar_id INTEGER REFERENCES avatars NOT NULL,
    zone_id INTEGER NOT NULL,
    instance_id INTEGER NOT NULL,
    CONSTRAINT fk_ai2i FOREIGN KEY (zone_id, instance_id) REFERENCES instances (zone_id, id),
    PRIMARY KEY(avatar_id, zone_id)
);

avatar_instances.instance_id如果 Avatar 必须属于每个区域的零个或一个实例，则允许输入 null 。

推荐阅读