如何用其他表列约束当前表列？PostgreSQL语法问题求助

问题背景 #

正在学习SQL，想要通过CHECK约束限制某表列的取值范围为另一张表指定列的有效值。尝试创建了两张表，但执行时出现语法错误，想知道可行的实现方法。

尝试的建表语句 #

首先创建table1：

Create table table1(
  col1 numeric pk,
  col2 varchar,
  col3 varchar,
  ...);

然后创建table2：

create table table2(
      col1 numeric pk,
      col2 numeric reference table1(col1),
      col3 varchar check (col3 in table1(col2))
     );

报错信息 #

在PostgreSQL中执行上述代码时收到报错：

org.jkiss.dbeaver.model.sql.DBSQLException: SQL Error [42601]: ERROR: 语法错误在或接近 «table1»
Position: 93

at org.jkiss.dbeaver.model.impl.jdbc.exec.JDBCStatementImpl.executeStatement(JDBCStatementImpl.java:133)

at org.jkiss.dbeaver.ui.editors.sql.execute.SQLQueryJob.executeStatement(SQLQueryJob.java:577)

at org.jkiss.dbeaver.ui.editors.sql.execute.SQLQueryJob.lambda$1(SQLQueryJob.java:486)

at org.jkiss.dbeaver.model.exec.DBExecUtils.tryExecuteRecover(DBExecUtils.java:172)

at org.jkiss.dbeaver.ui.editors.sql.execute.SQLQueryJob.executeSingleQuery(SQLQueryJob.java:493)

at org.jkiss.dbeaver.ui.editors.sql.execute.SQLQueryJob.extractData(SQLQueryJob.java:894)

at org.jkiss.dbeaver.ui.editors.sql.SQLEditor$QueryResultsContainer.readData(SQLEditor.java:3643)

at org.jkiss.dbeaver.ui.controls.resultset.ResultSetJobDataRead.lambda$0(ResultSetJobDataRead.java:118)

at org.jkiss.dbeaver.model.exec.DBExecUtils.tryExecuteRecover(DBExecUtils.java:172)

at org.jkiss.dbeaver.ui.controls.resultset.ResultSetJobDataRead.run(ResultSetJobDataRead.java:116)

at org.jkiss.dbeaver.ui.controls.resultset.ResultSetViewer$ResultSetDataPumpJob.run(ResultSetViewer.java:4945)

at org.jkiss.dbeaver.model.runtime.AbstractJob.run(AbstractJob.java:105)

at org.eclipse.core.internal.jobs.Worker.run(Worker.java:63)

可行实现方法 #

1. 语法错误原因 #

你写的check (col3 in table1(col2))是错误语法，IN后面必须跟子查询（比如IN (SELECT col2 FROM table1)），但PostgreSQL的普通CHECK约束不允许引用其他表——这是核心限制，标准SQL的CHECK约束仅支持引用当前表的列，跨表CHECK约束多数数据库都不支持。

2. 替代方案：外键约束（推荐） #

如果table1.col2是唯一值集合，优先用外键约束：

-- 先给table1.col2添加唯一约束（外键要求被引用列唯一或主键）
ALTER TABLE table1 ADD CONSTRAINT table1_col2_unique UNIQUE (col2);

-- 创建table2时，col3设为外键指向table1.col2
CREATE TABLE table2(
    col1 numeric PRIMARY KEY,
    col2 numeric REFERENCES table1(col1),
    col3 varchar REFERENCES table1(col2)
);

这样table2.col3的取值必须存在于table1.col2中，数据库会自动维护数据一致性，性能和规范性都最优。

3. 替代方案：触发器实现 #

如果table1.col2不需要唯一约束（允许重复值），可以用触发器实现校验：

-- 创建触发器函数
CREATE OR REPLACE FUNCTION check_table2_col3()
RETURNS TRIGGER AS $$
BEGIN
    -- 检查新值是否存在于table1.col2中
    IF NOT EXISTS (SELECT 1 FROM table1 WHERE col2 = NEW.col3) THEN
        RAISE EXCEPTION 'table2.col3的值必须存在于table1.col2中';
    END IF;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

-- 给table2添加触发规则：插入/更新前校验
CREATE TRIGGER trigger_check_table2_col3
BEFORE INSERT OR UPDATE ON table2
FOR EACH ROW EXECUTE FUNCTION check_table2_col3();

每次插入或更新table2时，触发器会自动校验col3的值，不符合要求就抛出错误。

4. 注意事项 #

• 外键方案优先，更高效且符合SQL设计规范；
• 触发器方案灵活，但性能略低，后续需要维护触发器逻辑；
• 若要删除或修改table1.col2的值，需注意table2的关联数据：外键可通过ON DELETE CASCADE等规则自动处理，触发器则需要额外添加逻辑。

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