CODEWITHSUNDEEP
sqlpostgresqlindexingforeign-keys
David Chan
David Chan

Reputation: 7505

Creating a Foreign Key based on an index name rather than table(columns)

I have a table with a timestamp field onto which I've created a composite index.

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

I have another table which stores a year and a_id which I'd like to have a foreign key relation.

I can't seem to find the syntax to do what I want.

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

produces:

ERROR:  syntax error at or near "("

I've also tried ...

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

Any Ideas?

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')

Upvotes: 1

Views: 276

Answers (1)

Erwin Brandstetter
Erwin Brandstetter

Reputation: 657932

A foreign key constraint cannot reference an index. It has to be a table.
A foreign key constraint cannot reference an expression. It has to point to column name(s) of the referenced table.
And there has to exist a unique index (primary key qualifies, too, implicitly) on the set of referenced columns.

Start by reading the manual about foreign keys here.

The superior design would be to just drop the column b.a_year. It is 100% redundant and can be derived from a.created_at any time.

If you positively need the column (for instance to enforce one row per year for certain criteria in table b), you can achieve your goal like this:

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)
);

Updated after @Catcall's comment: The CHECK constraint in combination with the column DEFAULT and NOT NULL clauses enforces your regime.

Alternatively (less simple, but allowing for NULL values) you could maintain the values in a.a_year with a trigger:

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();

Upvotes: 5

Related Questions