Two composite foreign keys have identical columns which must be equal value

database-designforeign keyprimary-key

Let's say I have a relation: CourseRegistrations with composite primary key: (course_id, sem_id, student_id)

I have another relation: CourseEvaluations with composite primary key: (course_id, sem_id, section, type, serial)
.The CourseEvaluations relation will contain records of the different evaluations done in a course.

Now I want to make a third relation: CourseStudentEvaluations with two composite foreign keys from both the above tables. Both the above tables' primary keys contain course_id and sem_id. How do I enforce that they are equal in a row/record so that I can ensure only those students' evaluations are entered against only those courses in which they're registered.

The CourseStudentEvaluations relation will contain records of all students' performance in each evaluation entered in CourseEvaluations.

Best Answer

If your database supports it, you can use a table level CHECK constraint to compare two columns to make sure that they're equal. You could have a single CHECK constraint that confirms the course_id and sem_id pointing to each parent are the same.

Alternatively, you could just have one course_id and one sem_id in your new table and use these columns in both of the foreign keys (one to each parent).

I think the second approach is a little cleaner, but if you're using some kind of ORM that has trouble understanding a child table column pointing at two different parents then you may not find the second approach to be practical.

Disclaimer

This is experimental and only tested rudimentarily. Proceed at your own risk. I would not use it myself and just drop / recreate constraints with standard DDL commands. If you break entries in the catalog tables you could easily mess up your database.

For all I know, there are only two differences between a PRIMARY KEY and a UNIQUE constraint in the catalog tables (the index itself is identical):

pg_index.indisprimary:
For PRIMARY KEY constraint ... TRUE
For UNIQUE constraint ... FALSE

pg_constraint.contype:
PRIMARY KEY constraint ... 'p'
UNIQUE constraint ... 'u'

You could convert constraint and index in place, from PRIMARY KEY constraint to UNIQUE constraint, my_idx being the (optionally schema-qualified) index name:

UPDATE pg_index SET indisprimary = FALSE WHERE indexrelid = 'my_idx'::regclass
UPDATE pg_constraint SET contype = 'u' WHERE conindid = 'my_idx'::regclass;

Or upgrade from UNIQUE to PRIMARY KEY:

UPDATE pg_index SET indisprimary = TRUE WHERE indexrelid = 'my_idx'::regclass;
UPDATE pg_constraint SET contype = 'p' WHERE conindid = 'my_idx'::regclass;

Modify legacy table schemas that have no primary key

I would add a BIGINT Identity column as the non-clustered primary key for any table that does not have a primary key. This will cause minimal disruption and use minimal storage space.

It will often improve performance especially for updates and deletes.

Best Answer

Related Solutions

Postgresql – What happens to the index of a primary key after a DROP CONSTRAINT

Disclaimer

Modify legacy table schemas that have no primary key

Related Question