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(dot)klaver(at)aklaver(dot)com