Re: Need a referential constraint to a non-unique record

On Tue, Jun 25, 2019 at 2:58 PM David Gauthier <davegauthierpg(at)gmail(dot)com>
wrote:

> 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...
>

This requires a trigger

>
> 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.
>

IMO this is a poorly chosen model. If you think this is a good idea you
should go ahead and represent the subjects as columns and have a single row.

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

Correct

> I could do this with a check constraint.
>

No, a check constraint will not work.

> But I want the ER view in the DBeaver tool to recognize the constraint
> and depict it.
>

Which requires PK/FK semantics

>
> 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.
>

Store (student, subject, grade) ...

Any ideas ?
>

You seem to understand the options that are open to you just fine. If
neither are agreeable then maybe the problem is a poor choice of model.

> psql (9.6.7, server 9.5.2) on linux
>

You may wish to consider upgrading your client and server software

David J.