0

I'm creating a SQL table to record information on subjects taken by students. I have a constraint which is the students are allowed to take multiple subjects in the same year, but are not allowed to same subjects over different years. For example, one can take Calculus I, Calculus II, and Calculus III in year 2021, but he/she cannot take Calculus I in the year 2019,2020, and 2021.

how do I put such a constraint in my SQL table?

I'm thinking of :

CREATE TABLE STUDENT(
    STUDENT_ID NUMBER PRIMARY KEY,
    COURSES VARCHAR (1000) NOT NULL,
    YEAR NUMBER NOT NULL,
    UNIQUE(COURSES,YEAR)
);

But I realized that the unique just prevents the same tuple of (courses, year) from appearing, but if the year changes, it will still be allowed, which is not what I want.

Since I've only learnt how to code SQL this week, is there other keywords that I can use to address my questions ? Thank you very much.

RofyRafy
  • 73
  • 1
  • 7
  • please tag with db platform – OldProgrammer May 10 '21 at 19:24
  • First tag your RDBMS. Then its really not clear to me the "constraint" you want from your explanation. And finally, its probably a bad idea to do this on database side, and certainly with a CONSTRAINT or TRIGGER. This kind of logic should be in the application itself. But maybe I just really dont understand anything to what you want. – Thomas G May 10 '21 at 19:24
  • I don't think there is any other option than the trigger. – Ankit Bajpai May 10 '21 at 19:28
  • It sounds like you're saying a student can only take each course a single time. If that is true, your unique constraint would be Student, Course, not Course, Year. In addition, you'll want a table to contain the courses a student takes, separate from the student data. – Wes H May 10 '21 at 19:33

1 Answers1

0

I think you want a unique constraint on student_id/subject:

alter table student add constraint unq_student_id_course_year
    unique (student_id, courses);
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786