How do I enforce uniqueness in a table?

Question

For example, I have to program an enrolment table which has:

1. enrolment_id
2. academic_period
3. results
4. student_id
5. subject

I want to enforce to make sure that a student can't enroll in a subject more than once in a specific academic period. So, the constraint shall involve student_id, subject, and academic period columns.

I'm still unsure how do I make this happen. Any help?

Answer 1

Add a unique constraint to your table:

ALTER TABLE yourtable ADD CONSTRAINT constraintname UNIQUE (student_id, subject, academic_period)

Answer 2

Create a unique constraint on academic_period, student_id, subject
I'll let you work out the actual SQL

Note: this is correctly your natural key: enrolment_id is a surrogate added at implementation time because of the database architecture. It isn't mandated in any design or modelling process

Answer 3

Add a unique constraint. From here:

To allow naming of a UNIQUE constraint, and for defining a UNIQUE constraint on multiple columns, use the following SQL syntax:

MySQL / SQL Server / Oracle / MS Access:

CREATE TABLE Persons
 (
 P_Id int NOT NULL,
 LastName varchar(255) NOT NULL,
 FirstName varchar(255),
 Address varchar(255),
 City varchar(255),
 CONSTRAINT uc_PersonID UNIQUE (P_Id,LastName)
 )

(Modify the above for the column(s) you need to set as unique)

Answer 4

You would just get the amount of rows returned from this example query:

SELECT COUNT(*) FROM enrollment WHERE student_id = 'XYZ' AND subject = 'ABC' AND academic_period = 'DEF'

If the amount of rows returned is greater than 0, they have already been enrolled in that subject in the academic period.