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