This article is half-done without your Comment! *** Please share your thoughts via Comment ***
PostgreSQL has one of the very good data type to store all the optional constraints and business rule of the application.
The DOMAIN is a type of DATA TYPE to abstract the common constraint and other business rule of Database system.
For example, we have several tables with Email column and we require to apply CHECK CONSTRAINT for validation of those Email columns.
For this kind of requirement, we can create one DOMAIN DATA TYPE, Instead of creating different CHECK CONSTRAINT for all the columns.
The DOMAIN DATA TYPE is also very easy to manage at one single place, without modifying each individual CHECK CONSTRAINT.
Below is a small demonstration of this:
CREATE one DOMAIN data type to validate simple email expression:
Doesn’t allow numbers in the domain name and doesn’t allow for top level domains that are less than 2 or more than 3 letters.
|
1 2 3 4 |
CREATE DOMAIN domain_email AS TEXT CHECK( VALUE ~ '^\w+@[a-zA-Z_]+?\.[a-zA-Z]{2,3}$' ); |
CREATE a Sample table using domain_email data type:
|
1 2 3 4 5 |
CREATE TABLE tbl_TestEmail ( ID INT ,Email domain_email ); |
Insert record with valid email address:
|
1 2 3 |
INSERT INTO tbl_TestEmail VALUES (1,'anvesh@gmail.com'); INSERT INTO tbl_TestEmail VALUES (2,'anvesh_08@dbrnd.org'); INSERT INTO tbl_TestEmail VALUES (3,'dba@aol.in'); |
Try to insert invalid email and test the CHECK CONSTRAINT of DOMAIN DATA TYPE:
|
1 2 3 |
INSERT INTO tbl_TestEmail VALUES (4,'anvesh@dbrnd.co.in'); INSERT INTO tbl_TestEmail VALUES (5,'anvesh_08@08dbrnd.org'); INSERT INTO tbl_TestEmail VALUES (6,'dba@aol.info'); |
