Need a referential constraint to a non-unique record

On Tue, Jun 25, 2019 at 2:58 PM David Gauthier <davegauthierpg@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.

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

On 6/26/19 7:28 AM, David Gauthier wrote:
Ccing list.

Actually, I'm not storing 4 records in the 'grades' table for each
record in the 'students' table.  For example, if student Joe gets
straight A's, he points to grades.grade_id = 1.  If student Sue also
gets straight A's, she points to grades.grade_id = 1.  I'm reusing the
set of records in "grades" for >1 student.  It's not a situation where
there's a "student_id" field in the "grades" table which points to a
"student_id" (PK) field in the "students" table with a FK relationship.

This simplistic example is not what's going on in my real application
where the equivalent of the "grades" table has hundreds of records and
there are thousands of "students".  I don't want to have to duplicate
the set of "grades" records over and over again if an existing set can
be "reused".

Hmm.

1) So each 'student' has to take all the 'subjects'?
If not then what happens?

2) You have to pre-plan all the possible combinations.
What happens if a 'subject' is added/deleted?

3) Your app has to 'know' what id points to what combination.

I see the above as resistant to change and fragile and as you already
have determined hard to model. I'm thinking a more traditional layout
will be better over the long run.

--
Adrian Klaver
adrian.klaver@aklaver.com