To DROP a Unique Constraint

Suppose we have to DROP that column, particularly in the table.

Syntax: 

ALTER TABLE Instructor

DROP INDEX ID;

Queries:

Find all the instructors that taught at most one course in the year 2017.

Instructor relation:

Without Using Unique Constraint

Query:

SELECT I.EMPLOYEEID, I.NAME
FROM Instructor as I
WHERE UNIQUE (SELECT Inst.EMPLOYEEID
              FROM Instructor as Inst
              WHERE I.EMPLOYEEID = Inst.EMPLOYEEID
                          and Inst.YEAR = 2017);

By Using Unique Constraint

Query:

SELECT Name
FROM Instructor
WHERE Year = 2017
GROUP BY Name
HAVING COUNT(DISTINCT CourseID) = 1;

Output:

Explanation: In the Instructor relation, only instructors Will and Smith teach a single course during the year 2017. The sub-query corresponding to these instructors contains only a single tuple and therefore the unique clause corresponding to these instructors evaluates to true thereby producing these two instructors in the output relation. 

Example 2

Find all the courses in the Computer Science department that has only a single instructor allotted to that course. 

Course Relation

Without Unique Constraint

Query:

SELECT C.COURSEID, C.NAME
FROM Course as C
WHERE UNIQUE (SELECT T.INSTRUCTORID
              FROM Course as T
              WHERE T.COURSEID = C.COURSEID 
                          and C.DEPARTMENT = 'Computer Science');

By Using UNIQUE Constraint

Query:

SELECT CourseID
FROM Instructor
WHERE CourseID LIKE 'CSE%'
GROUP BY CourseID
HAVING COUNT(DISTINCT Name) = 1;

Output:

Explanation: In the course relation, the only courses in the computer science department that have a single instructor allotted are Operating Systems and Programming. The unique constraint corresponding to these two courses has only a single tuple consisting of the corresponding instructors. So, the unique clause for these two courses evaluates to true and these courses are displayed in output relation. Other courses in the Course relation either have two or more instructors or they do not belong to the computer science department and therefore, those courses aren’t displayed in the output relation. 

SQL Constraints Unique constraints in SQL is used to check whether the sub-query has duplicate tuples in its result. It returns a boolean value indicating the presence/absence of duplicate tuples. Unique constraint returns true only if the subquery has no duplicate tuples, else it returns false.