如何创建存储主键的数组？多表存储用户ID与链接ID数组报错解决

你遇到的问题其实是PostgreSQL的外键约束不直接支持数组类型导致的——外键要求单个值关联到另一表的主键，但你把整个integer数组当成关联对象，数据库自然会报类型不兼容的错误。下面给你两种可行的解决方案，分别适合不同的场景：

这是关系数据库中处理多对多关系的标准做法，虽然看起来行数多，但扩展性和维护性更好，还能轻松添加额外字段（比如链接的排序顺序、添加时间等）。

创建关联表 #

CREATE TABLE Users ( 
    id serial primary key not null, 
    email varchar(64) unique not null 
);

CREATE TABLE Links ( 
    id serial primary key not null, 
    name varchar(64) not null 
);

-- 关联表：每个用户-链接组合占一行
CREATE TABLE LinkLists(
    user_id integer NOT NULL REFERENCES Users(id),
    link_id integer NOT NULL REFERENCES Links(id),
    -- 组合主键，避免重复的用户-链接对
    PRIMARY KEY(user_id, link_id)
);

插入示例数据 #

-- 插入用户
INSERT INTO Users(email) VALUES ('example@gmail.com');
-- 插入链接
INSERT INTO Links(name) VALUES ('google.com'), ('twitter.com');
-- 关联用户1和链接1、2
INSERT INTO LinkLists(user_id, link_id) VALUES (1, 1), (1, 2);

查询用户的所有链接（数组形式） #

如果需要返回数组格式的结果，可以用ARRAY_AGG聚合函数：

SELECT 
    u.id AS user_id,
    ARRAY_AGG(l.id) AS links,
    ARRAY_AGG(l.name) AS link_names
FROM Users u
JOIN LinkLists ll ON u.id = ll.user_id
JOIN Links l ON ll.link_id = l.id
WHERE u.id = 1
GROUP BY u.id;

如果你坚持要在表中存储数组，可以通过自定义函数+CHECK约束来确保数组中的每个链接ID都存在于Links表中。

第一步：创建验证函数 #

这个函数会检查数组中的所有元素是否都在Links.id中存在：

CREATE OR REPLACE FUNCTION validate_link_array(links integer[]) 
RETURNS boolean AS $$
BEGIN
    -- 如果数组为空，直接返回合法（根据你的需求调整）
    IF links IS NULL OR array_length(links, 1) = 0 THEN
        RETURN TRUE;
    END IF;
    -- 检查是否有数组元素不在Links.id中
    RETURN NOT EXISTS (
        SELECT 1 
        FROM unnest(links) AS link_id
        WHERE link_id NOT IN (SELECT id FROM Links)
    );
END;
$$ LANGUAGE plpgsql STABLE;

第二步：创建带CHECK约束的表 #

CREATE TABLE Users ( 
    id serial primary key not null, 
    email varchar(64) unique not null 
);

CREATE TABLE Links ( 
    id serial primary key not null, 
    name varchar(64) not null 
);

CREATE TABLE LinkLists(
    user_id integer unique not null REFERENCES Users(id),
    links integer [],
    -- 添加约束，调用验证函数
    CONSTRAINT check_links_valid CHECK (validate_link_array(links))
);

插入示例数据 #

INSERT INTO Users(email) VALUES ('example@gmail.com');
INSERT INTO Links(name) VALUES ('google.com'), ('twitter.com');
-- 合法插入：数组中的ID都存在
INSERT INTO LinkLists(user_id, links) VALUES (1, ARRAY[1,2]);
-- 非法插入：ID3不存在，会触发约束报错
-- INSERT INTO LinkLists(user_id, links) VALUES (1, ARRAY[1,3]);

注意事项 #

• 这种方式需要注意：如果Links表中删除了某个ID，LinkLists中包含该ID的数组不会自动更新，可能导致数据不一致。你需要额外添加触发器来处理这种情况，或者手动维护数组。
• 相比关联表，这种方式的查询灵活性较差，比如要统计某个链接被多少用户使用会更麻烦。

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