0

A student is linked to single program. I have the unique constraint checking single student single program and single semester. but Eventually the student's semester will go up.

Column names are student_id, program_id and semester.

How can i insert data when student goes to higher semester. Do i add semester start and end date then check that all 5 columns have unique constraint.

Any suggestions is appreciated.

Rudra
  • 223
  • 2
  • 13
  • 2
    It would be defenitely easier if you paste here your model schema. – jorzel Dec 22 '21 at 14:30
  • @jorzel i have no concrete model schema at the moment . I am seeking suggestions . if you could help me build one, i could get some insight. – Rudra Dec 24 '21 at 10:24

1 Answers1

1

If you have a unique constraint on semesters as well, you cannot insert data when student goes to higher semester. The operation you are looking for is UPSERT. So, you either UPDATE the existing data or DELETE and INSERT. But I'd prefer UPSERT.

If you need to store the history data of a student passing all the semesters, you better store it in another table. Here, you must not declare a constraint on semester column.

Check this How to UPSERT (MERGE, INSERT ... ON DUPLICATE UPDATE) in PostgreSQL?

highlytrainedbadger
  • 146
  • 10