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

["David G. Johnston" <david.g.johnston@xxxxxxxxx>]

On Tue, Jun 25, 2019 at 2:58 PM David Gauthier <davegauthierpg@xxxxxxxxx> 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.