Need a referential constraint to a non-unique record

From: David Gauthier <davegauthierpg(at)gmail(dot)com>
To: pgsql-general(at)postgresql(dot)org
Subject: Need a referential constraint to a non-unique record
Date: 2019-06-25 21:58:16
Message-ID: CAMBRECCAS-Ka3zPTNuy6oM=+vfeH3kPqeibuDahY5P73z4zPjA@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

I need to create a constraint on a column of a table such that it's value
is found in another table but may not be unique in that other table.
Example...

Let's say the DB is about students and the grades they got for 4
subjects... Math, English, Science, History. But instead of creating 4
records in the "grades" table for every record in the "students" table, I
storing each unique combination of grades in the "grades" table, those
records tied together with a common "id" field...

grade_id subject grade
1 math A
1 english A
1 science A
1 history A
2 math B
2 english A
2 science C
2 history B

etc... Each unique combination of the 4 subject/grades gets a new "id" and
those 4 records are written to the grates table.

Now, in the "students" table I have a "grad_id" column which points to the
set of grades for that student. The "grade_id" value in the "students"
table must also exist in the "grades" table. But the grade_id value is
pointing to 4, not 1 record in the "grades" table. And "grade_id" in the
"grades" table can't (obviously) be a PK.

There are no primary keys in this scenario so I don't think I can set up a
traditional primary-foreign key relationship.

I could do this with a check constraint. But I want the ER view in the
DBeaver tool to recognize the constraint and depict it.

I suppose I could create a bridge table between the "students" and "grades"
table which has only the "grades_id" column as a primary key, and then set
up 2 traditional primary/foreign key constraints (one between this new
table and "grades", and the other between this new table and "students").
But it's kinda unnecessary and am looking for something more direct,
without the bridge.

Any ideas ?
psql (9.6.7, server 9.5.2) on linux

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Peter Geoghegan 2019-06-25 22:06:44 Re: REINDEX : new parameter to preserve current average leaf density as new implicit FILLFACTOR
Previous Message Tom Lane 2019-06-25 21:56:24 Re: REINDEX : new parameter to preserve current average leaf density as new implicit FILLFACTOR