‘stud_batch’ and ‘stud_reg_no’ of the ‘student22’ table.ĬREATE UNIQUE INDEX idx_student_batch_regno To ensure the same criteria, we have to define the UNIQUE index on the columns. #Create a unique index postico registration#The Batch of the multiple students can be the same, but each student’s registration can not be the same. Now we will add two new columns of name ‘stud_batch’ and ‘stud_reg_no’ to the ‘student22’ table by using the following statement: Here we got an error which is because we are trying to insert the duplicate contact number in the stud_contact column. Illustrate the result of the above statement by using the following snapshot and SQL statement.Īgain, consider the following INSERT INTO statement for inserting a new row into the student22 table, but this time we will add the same contact number as we have added earlier. Now we will insert some data into the student table to validate the uniqueness of the stud_contact column.Ĭonsider the following INSERT INTO statement for inserting a new row into the student22 table: So we will add a UNIQUE index for the stud_contact column to make sure that the contact numbers are distinct for all students.Ĭonsider the following SQL statement, which will add a UNIQUE index for the stud_contact column.ĬREATE UNIQUE INDEX idx_student_stud_contact The contact number for each should be unique. Now we will add a column of name ‘stud_contact’ to the ‘student22’ table by using the following statement:ĪLTER TABLE student22 ADD stud_contact VARCHAR(20) We can get the indexes of the student22 table by using the following SQL statement: Also, the column stud_email is defined with the UNIQUE constraint applied to it, which will cause to create of two UNIQUE indexes for stud_id and stud_email columns. In the above statement, we have added the stud_id column as a primary key column. Consider the following CREATE TABLE statement, which will create a ‘student22’ table. We will create a table named ‘student22’ for understanding the UNIQUE INDEX in detail. Examples to Implement PostgreSQL UNIQUE Indexīelow are the examples of the PostgreSQL UNIQUE Index: If we have added a UNIQUE constraint or primary key for the table while creating a table, which automatically constructs a UNIQUE INDEX for a column which we have marked as a primary key of the table or the column on which we have added a UNIQUE constraint. Similar is applied to the multiple columns for which we have applied the UNIQUE INDEX.Ĥ. The NULL value is considered as the DISTINCT value, so the column with a UNIQUE INDEX applied can have multiple NULL values stored in it. Also, if have added a UNIQUE INDEX on more than one column, then the combined value of all these columns should be unique and can not have the same value in multiple rowsģ. We can add UNIQUE INDEX on one or more columns if we have added UNIQUE INDEX on a single column, then that column can not have the same value in multiple rows.Ģ. How Does UNIQUE Index Work in PostgreSQL?ġ. column_name: This defines the column or list of columns that will be covered under the UNIQUE INDEX to have unique values.table_name: This defines on which table we are adding a UNIQUE INDEX.index_name: This defines the name given to the UNIQUE INDEX.Hadoop, Data Science, Statistics & othersĬonsider the following syntax for creating the PostgreSQL UNIQUE INDEX:ĬREATE UNIQUE INDEX index_name ON table_name(column_name, )
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |