CODEWITHSUNDEEP
sqlpostgresql
mooncow
mooncow

Reputation: 413

Restrict the number of entries in a relation based on conditions across several relations

I am using PostgreSQL and am trying to restrict the number of concurrent loans that a student can have. To do this, I have created a CTE that selects all unreturned loans grouped by StudentID, and counts the number of unreturned loans for each StudentID. Then, I am attempting to create a check constraint that uses that CTE to restrict the number of concurrent loans that a student can have to 7 at most.

The below code does not work because it is syntactically invalid, but hopefully it can communicate what I am trying to achieve. Does anyone know how I could implement my desired restriction on loans?

CREATE TABLE loan (
   id SERIAL PRIMARY KEY,
   copy_id INTEGER REFERENCES media_copies (copy_id),
   account_id INT REFERENCES account (id),
   loan_date DATE NOT NULL,
   expiry_date DATE NOT NULL,
   return_date DATE,

   WITH currentStudentLoans (student_id, current_loans) AS
   (
      SELECT account_id, COUNT(*)
      FROM loan
      WHERE account_id IN (SELECT id FROM student)
         AND return_date IS NULL
      GROUP BY account_id
   )

   CONSTRAINT max_student_concurrent_loans CHECK(
      (SELECT current_loans FROM currentStudentLoans) BETWEEN 0 AND 7 
   )
);

For additional (and optional) context, I include an ER diagram of my database schema. enter image description here

Upvotes: 0

Views: 38

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269563

You cannot do this using an in-line CTE like this. You have several choices.

The first is a UDF and check constraint. Essentially, the logic in the CTE is put in a UDF and then a check constraint validates the data.

The second is a trigger to do the check on this table. However, that is tricky because the counts are on the same table.

The third is storing the total number in another table -- probably accounts -- and keeping it up-to-date for inserts, updates, and deletes on this table. Keeping that value up-to-date requires triggers on loans. You can then put the check constraint on accounts.

I'm not sure which solution fits best in your overall schema. The first is closest to what you are doing now. The third "publishes" the count, so it is a bit clearer what is going on.

Upvotes: 1

Related Questions