CODEWITHSUNDEEP
sqlpostgresqltriggersforeign-keysconstraints
user2779450
user2779450

Reputation: 775

PostgreSQL - Constraint Based on Column in Another Table

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?

Upvotes: 7

Views: 4275

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269673

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.

Upvotes: 2

Akhilesh Mishra
Akhilesh Mishra

Reputation: 6130

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

Upvotes: 3

Related Questions