CODEWITHSUNDEEP
postgresqlconstraints
Lucas Kumara
Lucas Kumara

Reputation: 19

How can I restrict the insert values of certain columns based on another in postgres

I have a table here and I want to ensure that certain inserts are blocked.

CREATE TABLE Creature (
  species TEXT,
  name TEXT,
  arms INTEGER,
  legs INTEGER
);

When inserting into this table, every species should have the same number of arms and legs as any other entry with the same species.

INSERT INTO Creatures VALUES ('Human', 'John', 2, 2);
INSERT INTO Creatures VALUES ('Human', 'Steve', 2, 2);
INSERT INTO Creatures VALUES ('Human', 'Mark', 0, 4); -- should not work as humans have 2 arms and 2 legs

I have tried using check constraints but I am not sure how to check existing tables values if they already exist.

Upvotes: 0

Views: 305

Answers (2)

jjanes
jjanes

Reputation: 44373

While this seems like a pretty lousy design for more than one reason, you can still do it. You would use an EXCLUDE constraint.

create extension btree_gist;

alter table creature add constraint consistent_limbs 
    exclude using gist (species with =, arms with <>, legs with <>);

Upvotes: 0

sticky bit
sticky bit

Reputation: 37487

Normalize. First create another table species in which you store the number of arms and legs per species.

CREATE TABLE species
             (id serial,
              name text,
              arms integer,
              legs integer,
              PRIMARY KEY (id));

Then only have a foreign key referencing the species in creatures.

CREATE TABLE creature
             (id serial,
              species integer,
              name text,
              PRIMARY KEY (id),
              FOREIGN KEY (species)
                          REFERENCES species
                                     (id));

Upvotes: 1

Related Questions