sql - 基于索引名称而不是表(列)创建外键

Question

我有一个带有时间戳字段的表，我在上面创建了一个复合索引。

CREATE INDEX "IDX_NAME_A" ON TABLE "A" (a_id, extract(year FROM created_at))

我有另一个存储年份和 a_id 的表，我希望它们具有外键关系。

我似乎找不到语法来做我想做的事。

ALTER TABLE "B" 
  ADD FOREIGN KEY(a_id, a_year) 
  REFERENCES A(a_id, extract(YEAR FROM created_at));

产生:

ERROR:  syntax error at or near "("

我也试过...

ALTER TABLE "B"
  ADD FOREIGN KEY(a_id, a_year) 
  USING INDEX "IDX_NAME_A";

有什么想法吗？

Table A
--------
a_id serial,
created_at timestamp default now()

Table B
-------
b_id serial
a_id integer not null,
a_year date_part('year')

Answer 1

外键约束不能引用索引。它必须是一张 table 。
外键约束不能引用表达式。它必须指向引用表的列名。
并且在引用列的集合上必须存在唯一索引(主键也隐含地限定)。

从阅读开始the manual about foreign keys here .

---

更好的设计是只删除 b.a_year 列。它是 100% 冗余的，可以随时从 a.created_at 派生。

如果您确实需要该列(例如，针对表 b 中的某些条件每年强制执行一行)，您可以像这样实现您的目标:

CREATE TABLE a (
  a_id serial
 ,created_at timestamp NOT NULL DEFAULT now()
 ,a_year NOT NULL DEFAULT extract(year FROM now())::int -- redundant, for fk
 ,CHECK (a_year = extract(year FROM created_at)::int)
);

CREATE UNIQUE INDEX a_id_a_year_idx ON TABLE a (a_id, a_year); -- needed for fk

CREATE TABLE b (
  b_id serial
 ,a_id integer NOT NULL
 ,a_year int -- doesn't have to be NOT NULL, but might
 ,CONSTRAINT id_year FOREIGN KEY (a_id, a_year) REFERENCES a(a_id, a_year)
);

在@Catcall 的评论后更新:CHECK 约束结合列 DEFAULT 和 NOT NULL 子句强制执行您的制度。

或者(不那么简单，但允许 NULL 值)您可以使用 trigger 维护 a.a_year 中的值:

CREATE OR REPLACE FUNCTION trg_a_insupbef()
  RETURNS trigger AS
$BODY$
BEGIN

NEW.a_year := extract(year FROM NEW.created_at)::int;
RETURN NEW;

END;
$BODY$
  LANGUAGE plpgsql VOLATILE;

CREATE TRIGGER insupbef
  BEFORE INSERT OR UPDATE ON a
  FOR EACH ROW EXECUTE PROCEDURE trg_a_insupbef();