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

On 6/25/19 2:58 PM, David Gauthier 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...
> 
> 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, 

But you are.

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

Why not?:

grade_id student_id   subject      grade
1       1          math         A
2          1          english      A
3          1          science      A
4          1          history      A
5          2          math         B
6          2          english      A
7          2          science      C
8          2          history      B

Where grade_id is the PK and student_id is FK to students

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


-- 
Adrian Klaver
adrian.klaver@aklaver.com