Use function on foreign key constraint

Question 1

Can I have a foreign key constraint that takes a timestamp column but uses it as date only?

I'm trying something like this but it doesn't work:

alter table   table1 add   constraint my_constraint foreign key (    date(created_date)  ) references table2(    date(transaction_timestamp)  );

Question 2

You can't create a foreign key constraint on expressdate(created_date). foreign key constraint must have reference columns.

Question 3

You can use triggers instead of an FK.

Question 4

Please add examples showing what you'd expect this to do.Vérace'snot-taking-a-no-for-an-answer take, while commendable, limits this to 1 timezone and 1 transaction a day, mainly because FK's need aunique index. My guess is you're trying to stoptable1 rows without at least 1 existing transaction on the same date intable2 - in that case @PanagiotisKanavos' comment above is closer to what you'd realistically want, suggesting aconstraint trigger that runs aselect exists. Unless you clarify, there's too much room for interpretation

Question 5

Despite what everyone else is saying, you can do this! But you have to "cheat" (a little! :-) )!

I did the following - this is the "cheating" bit!. All of the code below can be found on the fiddlehere.

CREATE OR REPLACE FUNCTION Get_Date(tz TIMESTAMPTZ)RETURNS DATEAS $$   SELECT $1::DATE $$LANGUAGE SQL IMMUTABLE;

Youhave to be sure of the consequences here - the mutability is to do with timezones &c. You could potentially have issues with time/date errors here - but if you're prepared to take this risk or you only ever work in one timezone, thencaveat emptor! :-)

and then the following:

CREATE TABLE t (  ttz TIMESTAMPTZ PRIMARY KEY,  ttzd_gen DATE UNIQUE GENERATED ALWAYS AS (Get_Date(ttz)) STORED);

and the child table:

CREATE TABLE u(  utz TIMESTAMPTZ,  utzd_gen DATE GENERATED ALWAYS AS (Get_Date(utz)) STORED,  CONSTRAINT utztz_gen_fk FOREIGN KEY (utzd_gen) REFERENCES t(ttzd_gen));

and now we insert a record in the parent:

INSERT INTO t VALUES (NOW());

and everything is fine and dandy! Insert into the child:

INSERT INTO u VALUES (NOW());

again, fine and dandy!

But:

INSERT INTO u VALUES (NOW() + INTERVAL '1 DAY');

and wallop!

ERROR:  insert or update on table "u" violates foreign key constraint "utztz_gen_fk"DETAIL:  Key (utzd_gen)=(2025-08-09) is not present in table "t".

Our desired result.

I'm not quite surewhy you want to do this, but it can be done! You'll only be able to have one day per year? You can check out posts by Erwin Brandstetter on this topic!

Question 6

+1 but it's good to call out risks of non-immutability. For dates, they can also be affected bydatestyle. See my example here:stackoverflow.com/a/79699962/20860

Question 7

@BillKarwin Forgot aboutdatestyle - thanks for pointing that out! I did mention the possibility ofould potentially have issues with time/date errors here - but it's good to coverall the bases!

Question 8

You didn't "do this". What you actually did is pretty fragile. What's posted isn't a foreign key constraint with a function at all. It's apersisted computed column. Only after the value is calculated and stored, is the FK created using thestored value. Yes, you can create constraints and indexes over persisted computed columns and that's shown in other SO questions too. The downside is that the function maynot be deterministic (as you also describe) and a seemingly identical call can result in different values. You pay the storage cost as a normal column, so there's no benefit

Question 9

@PanagiotisKanavos I concede that it's an awful hack, bizarre and sh1t! I did ask the OPwhy they wanted to do it, but... itwill only work for 1 day at a time - also mentioned, so, what can you do?

Question 10

No — you can’t directly create a foreign key constraint on an expression like DATE(created_date) in standard SQL (including MySQL, PostgreSQL, SQL Server, Oracle, etc.).

Foreign key constraints must be defined between actual columns, not computed expressions. Both columns must also match in data type and precision.

Question 11

Check out my answer!

Question 12

Not true, you'd better remove this answer. From the manual:

A foreign key must reference columns that either are a primary key or form a unique constraint, or are columns from a non-partial unique index.

If your expression points to a unique value of the same data type, you're fine. Seepostgresql.org/docs/current/…

Vérace 1,00412 silver badges47 bronze badges · Accepted Answer · 2025-08-08 14:26:28Z

Despite what everyone else is saying, you can do this! But you have to "cheat" (a little! :-) )!

I did the following - this is the "cheating" bit!. All of the code below can be found on the fiddlehere.

CREATE OR REPLACE FUNCTION Get_Date(tz TIMESTAMPTZ)RETURNS DATEAS $$   SELECT $1::DATE $$LANGUAGE SQL IMMUTABLE;

Youhave to be sure of the consequences here - the mutability is to do with timezones &c. You could potentially have issues with time/date errors here - but if you're prepared to take this risk or you only ever work in one timezone, thencaveat emptor! :-)

and then the following:

CREATE TABLE t (  ttz TIMESTAMPTZ PRIMARY KEY,  ttzd_gen DATE UNIQUE GENERATED ALWAYS AS (Get_Date(ttz)) STORED);

and the child table:

CREATE TABLE u(  utz TIMESTAMPTZ,  utzd_gen DATE GENERATED ALWAYS AS (Get_Date(utz)) STORED,  CONSTRAINT utztz_gen_fk FOREIGN KEY (utzd_gen) REFERENCES t(ttzd_gen));

and now we insert a record in the parent:

INSERT INTO t VALUES (NOW());

and everything is fine and dandy! Insert into the child:

INSERT INTO u VALUES (NOW());

again, fine and dandy!

But:

INSERT INTO u VALUES (NOW() + INTERVAL '1 DAY');

and wallop!

ERROR:  insert or update on table "u" violates foreign key constraint "utztz_gen_fk"DETAIL:  Key (utzd_gen)=(2025-08-09) is not present in table "t".

Our desired result.

I'm not quite surewhy you want to do this, but it can be done! You'll only be able to have one day per year? You can check out posts by Erwin Brandstetter on this topic!

+1 but it's good to call out risks of non-immutability. For dates, they can also be affected bydatestyle. See my example here:stackoverflow.com/a/79699962/20860
@BillKarwin Forgot aboutdatestyle - thanks for pointing that out! I did mention the possibility ofould potentially have issues with time/date errors here - but it's good to coverall the bases!
You didn't "do this". What you actually did is pretty fragile. What's posted isn't a foreign key constraint with a function at all. It's apersisted computed column. Only after the value is calculated and stored, is the FK created using thestored value. Yes, you can create constraints and indexes over persisted computed columns and that's shown in other SO questions too. The downside is that the function maynot be deterministic (as you also describe) and a seemingly identical call can result in different values. You pay the storage cost as a normal column, so there's no benefit
@PanagiotisKanavos I concede that it's an awful hack, bizarre and sh1t! I did ask the OPwhy they wanted to do it, but... itwill only work for 1 day at a time - also mentioned, so, what can you do?

Movatterモバイル変換

Collectives™ on Stack Overflow

Use function on foreign key constraint

2 Answers2

4 Comments

2 Comments

Your Answer

Sign up orlog in

Post as a guest

Linked

Related

Hot Network Questions

Subscribe to RSS