SQL | UNIQUE Constraint

SQL Constraints

Unique constraint in SQL is used to check whether the sub query has duplicate tuples in it’s result. It returns a boolean value indicating the presence/absence of duplicate tuples. Unique construct returns true only if the sub query has no duplicate tuples, else it return false.

Important Points:

  • Evaluates to true on an empty sub query.
  • Returns true only if there are unique tuples present as the output of the sub query (two tuples are unique if the value of any attribute of the two tuples differ).
  • Returns true if the sub query has two duplicate rows with at least one attribute as NULL.

Syntax:

SELECT table.ID
FROM  table
WHERE UNIQUE (SELECT table2.ID
              FROM table2
              WHERE table.ID = table2.ID);

Note: During the execution, first the outer query is evaluated to obtain table.ID. Following this, the inner sub query is processed which produces a new relation that contains the output of inner query such that table.ID == table2.ID. If every row in the new relation is unique then unique returns true and the corresponding table.ID is added as a tuple in the output relation produced. However, if every row in the new relation is not unique then unique evaluates to false and the corresponding table.ID is not add to the output relation.

Unique applied to a sub query return false if and only if there are two tuples t1 and t2 such that t1 = t2. It considers t1 and t2 to be two different tuple, when unique is applied to a sub query that contains t1 and t2 such that t1 = t2 and at least one of the attribute of these tuples contains a NULL value. Unique predicate in this case evaluates to true. This is because, a NULL value in SQL is treated as an unknown value therefore, two NULL values are considered to be distinct.

Note: The SQL statement without UNIQUE clause can also be written as:

SELECT table.ID
FROM  table
WHERE 1 <= (SELECT count(table2.ID)
              FROM table2
              WHERE table.ID = table2.ID);

Queries

Example 1: Find all the instructors that taught at most one course in the year 2017.
Instructor relation:

EmployeeID Name CourseID Year
77505 Alan SC110 2017
77815 Will CSE774 2017
85019 Smith EE457 2017
92701 Sam PYS504 2017
60215 Harold HSS103 2016
77505 Alan BIO775 2017
92701 Sam ECO980 2017
    SQL 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);

Output:

EmployeeID Name
77815 Will
85019 Smith

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 Computer Science department that has only a single instructor allotted to that course.
Course relation:

CourseID Name Department InstructorID
CSE505 Computer Network Computer Science 11071
CSE245 Operating System Computer Science 74505
CSE101 Programming Computer Science 12715
HSS505 Psychology Social Science 85017
EE475 Signals & Systems Electrical 22150
CSE314 DBMS Computer Science 44704
CSE505 Computer Network Computer Science 11747
CSE314 DBMS Computer Science 44715
    SQL 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');

Output:

COURSEID Name
CSE245 Operating System
CSE101 Programming

Explanation: In the course relation, the only courses in computer science department that has a single instructor allotted are Operating System 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 computer science department and therefore, those courses aren’t displayed in the output relation.

This article is contributed by Mayank Kumar. If you like GeeksforGeeks and would like to contribute, you can also write an article using contribute.geeksforgeeks.org or mail your article to contribute@geeksforgeeks.org. See your article appearing on the GeeksforGeeks main page and help other Geeks.

Please write comments if you find anything incorrect, or you want to share more information about the topic discussed above.



My Personal Notes arrow_drop_up

Improved By : lonewolf_ab



Article Tags :
Practice Tags :


2


Please write to us at contribute@geeksforgeeks.org to report any issue with the above content.