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