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