如何实现SQL数据库跨表全局唯一序列ID且无输入冗余？

方案一：使用全局序列+联合视图触发器（无需主表） #

这种方式直接用全局序列生成跨表唯一ID，通过联合视图和触发器让用户一次性完成数据插入，完全避免主表带来的分步操作冗余。

1. 创建全局序列，用于生成所有表共享的唯一ID：

CREATE SEQUENCE global_item_id_seq;

2. 为每种媒体类型创建独立表，使用全局序列作为ID默认值，并包含公共字段（标题、贡献者、媒体类型）：

-- 书籍表
CREATE TABLE books (
    item_id INT PRIMARY KEY DEFAULT nextval('global_item_id_seq'),
    entrytitle VARCHAR(100) NOT NULL,
    primarycontributer VARCHAR(100) NOT NULL,
    mediatype VARCHAR(50) NOT NULL DEFAULT 'book',
    -- 书籍特有字段
    isbn VARCHAR(13),
    pages INT
);

-- CD表
CREATE TABLE cds (
    item_id INT PRIMARY KEY DEFAULT nextval('global_item_id_seq'),
    entrytitle VARCHAR(100) NOT NULL,
    primarycontributer VARCHAR(100) NOT NULL,
    mediatype VARCHAR(50) NOT NULL DEFAULT 'cd',
    -- CD特有字段
    total_tracks INT,
    runtime INTERVAL
);

-- DVD表同理扩展

3. 创建联合视图和插入触发器，让用户只需向视图插入一次数据，自动分发到对应媒体表：

-- 创建包含所有媒体类型数据的联合视图
CREATE VIEW all_items AS
SELECT item_id, entrytitle, primarycontributer, mediatype, isbn, pages, NULL AS total_tracks, NULL AS runtime FROM books
UNION ALL
SELECT item_id, entrytitle, primarycontributer, mediatype, NULL, NULL, total_tracks, runtime FROM cds;

-- 编写插入触发器函数
CREATE OR REPLACE FUNCTION insert_item()
RETURNS TRIGGER AS $$
BEGIN
    CASE NEW.mediatype
        WHEN 'book' THEN
            INSERT INTO books (entrytitle, primarycontributer, isbn, pages)
            VALUES (NEW.entrytitle, NEW.primarycontributer, NEW.isbn, NEW.pages);
        WHEN 'cd' THEN
            INSERT INTO cds (entrytitle, primarycontributer, total_tracks, runtime)
            VALUES (NEW.entrytitle, NEW.primarycontributer, NEW.total_tracks, NEW.runtime);
        -- 可扩展其他媒体类型
    END CASE;
    RETURN NULL;
END;
$$ LANGUAGE plpgsql;

-- 绑定触发器到视图
CREATE TRIGGER trigger_insert_item
INSTEAD OF INSERT ON all_items
FOR EACH ROW EXECUTE FUNCTION insert_item();

使用时，用户只需执行一次插入语句即可完成数据录入：

INSERT INTO all_items (entrytitle, primarycontributer, mediatype, isbn, pages)
VALUES ('SQL权威指南', 'Joe Celko', 'book', '9787111544937', 1200);

方案二：改进主表模式（通过视图+触发器减少输入冗余） #

如果倾向于保留主表结构，可以通过视图和触发器让用户一次性完成主表+子表的数据插入，避免分步操作的冗余。

1. 创建主表和媒体子表：

CREATE TABLE entrymasterlist (
    masterid SERIAL UNIQUE PRIMARY KEY,
    entrytitle VARCHAR(100) NOT NULL,
    primarycontributer VARCHAR(100) NOT NULL,
    mediatype VARCHAR(50) NOT NULL
);

CREATE TABLE books (
    masterid INT PRIMARY KEY REFERENCES entrymasterlist(masterid),
    isbn VARCHAR(13),
    pages INT
);

CREATE TABLE cds (
    masterid INT PRIMARY KEY REFERENCES entrymasterlist(masterid),
    total_tracks INT,
    runtime INTERVAL
);

2. 创建包含主表和子表所有字段的视图，并编写插入触发器：

CREATE VIEW item_details AS
SELECT em.masterid, em.entrytitle, em.primarycontributer, em.mediatype, b.isbn, b.pages, c.total_tracks, c.runtime
FROM entrymasterlist em
LEFT JOIN books b ON em.masterid = b.masterid
LEFT JOIN cds c ON em.masterid = c.masterid;

CREATE OR REPLACE FUNCTION insert_item_detail()
RETURNS TRIGGER AS $$
DECLARE
    new_masterid INT;
BEGIN
    -- 先插入主表获取ID
    INSERT INTO entrymasterlist (entrytitle, primarycontributer, mediatype)
    VALUES (NEW.entrytitle, NEW.primarycontributer, NEW.mediatype)
    RETURNING masterid INTO new_masterid;
    
    -- 根据媒体类型插入对应子表
    CASE NEW.mediatype
        WHEN 'book' THEN
            INSERT INTO books (masterid, isbn, pages)
            VALUES (new_masterid, NEW.isbn, NEW.pages);
        WHEN 'cd' THEN
            INSERT INTO cds (masterid, total_tracks, runtime)
            VALUES (new_masterid, NEW.total_tracks, NEW.runtime);
    END CASE;
    
    RETURN NULL;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trigger_insert_item_detail
INSTEAD OF INSERT ON item_details
FOR EACH ROW EXECUTE FUNCTION insert_item_detail();

用户只需向视图插入一次数据，即可自动完成主表和子表的创建：

INSERT INTO item_details (entrytitle, primarycontributer, mediatype, total_tracks, runtime)
VALUES ('经典摇滚合集', 'Various Artists', 'cd', 15, '01:05:30');

方案对比 #

• 方案一无需维护主表，结构更简洁，适合媒体类型字段差异较大的场景；
• 方案二保留了主表的统一管理能力，适合需要集中维护公共字段的场景。

内容的提问来源于stack exchange，提问作者MossyQuill