On Tue, Jun 25, 2019 at 2:58 PM David Gauthier <davegauthie...@gmail.com> 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... > This requires a trigger > > 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 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. > IMO this is a poorly chosen model. If you think this is a good idea you should go ahead and represent the subjects as columns and have a single row. > > There are no primary keys in this scenario so I don't think I can set up a > traditional primary-foreign key relationship. > Correct > I could do this with a check constraint. > No, a check constraint will not work. > But I want the ER view in the DBeaver tool to recognize the constraint > and depict it. > Which requires PK/FK semantics > > 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. > Store (student, subject, grade) ... Any ideas ? > You seem to understand the options that are open to you just fine. If neither are agreeable then maybe the problem is a poor choice of model. > psql (9.6.7, server 9.5.2) on linux > You may wish to consider upgrading your client and server software David J.
