PostgreSQL - Constraint Based on Column in Another Table

Question

I have two tables, one called ballots and one called votes. Ballots stores a list of strings representing options that people can vote for:

CREATE TABLE IF NOT EXISTS Polls (
  id SERIAL PRIMARY KEY,
  options text[]
);

Votes stores votes that users have made (where a vote is an integer representing the index of the option they voted for):

CREATE TABLE IF NOT EXISTS Votes (
  id SERIAL PRIMARY KEY,
  poll_id integer references Polls(id),
  value integer NOT NULL ,
  cast_by integer NOT NULL
);

I want to ensure that whenever a row is created in the Votes table, the value of 'value' is in the range [0,length(options)) for the corresponding row in Polls (by corresponding, I mean the row where the poll_id's match).

Is there any kind of check or foreign key constraint I can implement to accomplish this? Or do I need some kind of trigger? If so, what would that trigger look like and would there be performance concerns? Would it be just as performant to just manually query for the corresponding poll using a SELECT statement and then assert that 'value' is valid before inserting into Votes table?

Something like https://stackoverflow.com/questions/23237471/postgresql-check-constraint-for-foreign-key-condition — Sujitmohanty30, Aug 14 '20 at 04:18
Don't use arrays. Normalize your table, then you can use a proper foreign key. — , Aug 14 '20 at 06:08

Akhilesh Mishra · Answer 1 · 2020-08-14T04:41:54.043

In your requirement you can not use Check Constraint because it can refer the column of the same table.

You can refer the Official Manual for the same.

So, here you should use Trigger on BEFORE INSERT event of your Votes Table or you can use function/procedure(depend upon your version of PostgreSQL) for your insert operation where you can check the value before insert and raise exception if the condition not satisfied.

USING Trigger:

create or replace function id_exist() returns trigger as
$$
begin
if new.value<0 or new.value>=(select array_length(options,1) from polls where id=new.poll_id) then
raise exception 'value is not in range';
end if;
return new;
end;

$$
language plpgsql

CREATE TRIGGER check_value BEFORE INSERT  ON votes
    FOR EACH ROW EXECUTE PROCEDURE id_exist();

DEMO

score 2 · Accepted Answer · answered Aug 14 '20 at 12:39

I would suggest that you modify your data model to have a table, PollOptions:

CREATE TABLE IF NOT EXISTS PollOptions (
  PollOptionsId SERIAL PRIMARY KEY,  -- should use generated always as identity
  PollId INT NOT NULL, REFERENCES Polls(id),
  OptionNumber int,
  Option text,
  UNIQUE (PollId, Option)
);

Then your Votes table should have a foreign key reference to PollOptions. You can use either PollOptionId or (PollId, Option).

No triggers or special functions are needed if you set up the data correctly.

PostgreSQL - Constraint Based on Column in Another Table

2 Answers2