This article is half-done without your Comment! *** Please share your thoughts via Comment ***
The Data validation is always a major concern of any Database System.
We all know about the UNIQUE constraint which prevents the duplicate records.
One of the Previous article, I have already shared one small note about the UNIQUE Index of PostgreSQL.
PostgreSQL: Composite UNIQUE Constraint does not consider NULL Values
In this post, I am sharing a similar post for Partial Unique Index of PostgreSQL.
For example, I want to stop duplicate Student Grages for particular Student Division only.
Create a sample table:
|
1 2 3 4 5 6 7 |
CREATE TABLE tbl_Students ( StudID INT ,StudName CHARACTER VARYING ,StudDivision CHARACTER VARYING ,StudGrade CHAR(1) ); |
Create a Partial UNIQUE Index:
|
1 2 |
CREATE UNIQUE INDEX tbl_Students_StudGrade ON tbl_Students (COALESCE(StudDivision,''),COALESCE(StudGrade,'')) WHERE StudDivision IN ('First','Second','Third'); |
Insert few sample records:
As we have creted Partial index so we can insert only unique combination of StudDivision (First,Second,Third) and StudGrade.
|
1 2 3 4 5 6 7 8 |
INSERT INTO tbl_Students VALUES (1,'Anvesh','First','A') ,(2,'Jeeny','Second','B') ,(3,'Roy','Third','B') ,(4,'Mukesh','First','C') ,(5,'Martin','Second','C') ,(6,'Kavita','Third','A'); |
Try to insert few Invalid records:
|
1 2 3 4 |
INSERT INTO tbl_Students VALUES (7,'Rajesh','First','A') ,(8,'Nile','Second','B'); |
Try to insert few records which are not validated by Partition Index:
We can insert duplicate records StudGrade + StudDivision for other StudDivisions except Partial StudDivision.
|
1 2 3 4 |
INSERT INTO tbl_Students VALUES (7,'Dimpi','Fourth','C') ,(8,'Bony','Fourth','C'); |
