PostgreSQL UNIQUE Constraint -1

• UNIQUE constraint same as primary key different is UNIQUE allow single NULL value.
• UNIQUE constraint, every time you insert a new row, PostgreSQL checks if the value is already in the table. If it found that the new value is already there, it would give back an error message and reject the changes. The same process is carried out for the update existing data.
• When you add a UNIQUE constraint to a column or a group of columns, PostgreSQL will create a btree index on the respective column or a group of columns automatically.
• The Value of the column must be unique across the whole table. However, the column can have many NULL values because PostgreSQL treats each NULL value to be unique. Notice that SQL standard only allows one NULL value in the column that has the UNIQUE constraint.

YOU CAN CREATE UNIQUE CONSTRAINT THREE METHOD THEY ARE

SYNTAX:
1.COLUMN METHOD UNIQUE CONSTRAINT
CREATE TABLE table_name (
    Column_1 integer UNIQUE,
    Column_2 text,
    Column_3 numeric
);


2.TABLE METHOD UNIQUE CONSTRAIN:
CREATE TABLE table_name (
    Column_1 integer,
    Column_2 text,
    Column_3 numeric,
    UNIQUE (column_1)
);

3.ALTER METHOD UNIQUE CONSTRAINT:
ALTER TABLE table_name ADD CONSTRAINT constraint_name UNIQUE (column_1,column_2,.,.,....etc);

PRACTICAL 1.CREATION OF UNIQUE CONSRAINTS:

The following CREATE TABLE statement creates a new table named student with a UNIQUE constraint applied to the student_id column.

postgres=# CREATE TABLE STUDENTS (
    student_id integer UNIQUE,
    name text,
    class integer
);

postgres=# \d students
     Table "public.students"
   Column   |  Type   | Modifiers 
------------+---------+-----------
 student_id | integer | 
 name       | text    | 
 class      | integer | 
Indexes:
    "students_student_id_key" UNIQUE CONSTRAINT, btree (student_id)                      

Note:"students_student_id_key" are the system generated unique key.

PRACTICAL 2.CREATION OF UNIQUE CONSRAINTS WITH CONSTRAINT NAME:

The UNIQUE constraint can be written as the table constraint with constraint name as following exaple

postgres=# CREATE TABLE INDIA(  
STATE_no integer,   
STATE_name character(35),  
STATE_grade character(1),
CONSTRAINT uni_key UNIQUE(STATE_no)
);

postgres=# \d india
          Table "public.india"
   Column    |     Type      | Modifiers 
-------------+---------------+-----------
 state_no    | integer       | 
 state_name  | character(35) | 
 state_grade | character(1)  | 
Indexes:
    "uni_key" UNIQUE CONSTRAINT, btree (state_no)

Note:Here "uni_key" is the unique constraint name.

PRACTICAL 3.INSERTING OPERATION ON UNIQUE KEY TABLE(STUDENTS):

• when you inserting the New value PostgreSQL checks if the value is already in the table. If it found that the new value is already there, it would give back an error message and reject the changes. The same process is carried out for the update existing data.
• the table  column can have many NULL values because PostgreSQL treats each NULL value to be unique. Notice that SQL standard only allows one NULL value in the column that has the UNIQUE constraint.

postgres=# insert into students values(1,'nijam',8),(2,'junaith',10),(3,'benz',12);
INSERT 0 3
postgres=# insert into students values(4,'nijam',8),(5,'junaith',10),(null,'benz',12);
INSERT 0 3
postgres=# insert into students values(8,'nijam',8),(9,'junaith',10),(null,'benz',12);
INSERT 0 3
postgres=# insert into students values(11,'nijam',8),(10,'junaith',10),(null,'benz',12);
INSERT 0 3

postgres=# select * from students;                                              
 student_id |  name   | class 
------------+---------+-------
          1 | nijam   |     8
          2 | junaith |    10
          3 | benz    |    12
          4 | nijam   |     8
          5 | junaith |    10
            | benz    |    12
          8 | nijam   |     8
          9 | junaith |    10
            | benz    |    12
         11 | nijam   |     8
         10 | junaith |    10
            | benz    |    12
(12 rows)

when you inserting duplicate values Postgresql will throw error, see following exaple you will understood unique constraints

postgres=# insert into students values(11,'nijam',8),(10,'junaith',10),(12,'benz',12);  
ERROR:  duplicate key value violates unique constraint "students_student_id_key"
DETAIL:  Key (student_id)=(11) already exists.

--why becouse means value 11 and 12 is already exist in students table

PRACTICAL 4.UNIQUE CONSTRAINT ON MULTIPLE COLUMN:

postgres=# CREATE TABLE batch(  
batch_id integer,   
batch_name character(35),  
members integer,
CONSTRAINT mul_uni_key UNIQUE(batch_id,members)
);
CREATE TABLE

postgres=# \d batch
          Table "public.batch"
   Column   |     Type      | Modifiers 
------------+---------------+-----------
 batch_id   | integer       | 
 batch_name | character(35) | 
 members    | integer       | 
Indexes:
    "mul_uni_key" UNIQUE CONSTRAINT, btree (batch_id, members)