CODEWITHSUNDEEP
mysqlsql
nfnmy
nfnmy

Reputation: 157

How do I enforce uniqueness in a table?

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?

Upvotes: 2

Views: 2849

Answers (4)

David
David

Reputation: 73554

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)

Upvotes: 1

gbn
gbn

Reputation: 432200

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

Upvotes: 3

Maximilian Mayerl
Maximilian Mayerl

Reputation: 11359

Add a unique constraint to your table:

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

Upvotes: 14

Marcus Recck
Marcus Recck

Reputation: 5065

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.

Upvotes: 0

Related Questions