CODEWITHSUNDEEP
sqlpostgresqlconstraints
user3227389
user3227389

Reputation: 11

SQL constraints on update problems

I am currently studying SQL and I had a question about a constraint that I must create for an exercise.

Given 2 table:

CREATE TABLE A (
a1 integer PRIMARY KEY,
a2 integer CHECK (a2>10))

CREATE TABLE B (
b1 integer PRIMARY KEY, b2 integer))

and a many to many relationship table between A and B

CREATE TABLE R (
rID integer PRIMARY KEY,
a1ID integer REFERENCES A (a1),
b1ID integer REFERENCES B (b1))

I want to create a constrain on table B such that: when a new instance of B is added, there should be at least 5 and at most 10 instances of A Being related to B.

This is how I did it:

1) a trigger on insert into B:

CREATE triggerA BEFORE INSERT ON B 
EXECUTE PROCEDURE upCons();

2) create a function that check the number of instance of A related to B:

here is my problem, can I just count the number of instance of R by doing this?

CREATE FUNCTION upCons() RETURN trigger AS $$
DECLARE x integer;
BEGIN
SELECT count(rID) INTO x FROM R;
IF (x<5 OR x>10) THEN RAISE EXCEPTION 'insert condition not met';
END IF;
END; LANGUAGE 'plpgsql';

OR I do the same by counting the instances of A in the relationship.

Is this a good approach to the problem? Should I do it differently and is this correct to begin with

Upvotes: 1

Views: 70

Answers (2)

positivew
positivew

Reputation: 750

I want to create a constrain on table B such that: when a new instance of B is added, there should be at least 5 and at most 10 instances of A Being related to B.

This constraint seems unreasonable (or perhaps even impossible), because at the time of inserting an entry into B (or A), there cannot be any relations already referencing the new entry. Referencing an entry (using a foreign key constraint as you do) requires the entry itself to already exist in the correct table. And according to your trigger, if you don't have enough relations in R, you cannot insert anything into B. Therefore you have effectively made your tables B and R read-only, since table B insertions are denied by your trigger and table R insertions by the foreign key constraint that requires any reference to table B to already exist in table B.

Upvotes: 1

drw85
drw85

Reputation: 184

IF (x<5 AND x>10) THEN RAISE EXCEPTION 'insert condition not met';

This is supposed to be OR instead of AND, right?

Upvotes: 0

Related Questions