You can read more about the unique index to improve your knowledge.In this section, we are going to understand the working of the UNIQUE Index, which is used to make sure the individuality of data in various columns. The unique index helps the query run faster when finding a particular row. Note that, the unique index is created automatically under the hood as soon as you create the unique constraint. In this tutorial, we have learned how to create the unique constraint on the table using multiple ways along with how to delete an existing unique constraint using ALTER command. Read More: PostgreSQL DROP TABLE Conclusion ALTER TABLE employeesĭROP CONSTRAINT IF EXISTS employees_empid_deptid_key Code language: PostgreSQL SQL dialect and PL/pgSQL ( pgsql ) Drop Unique ConstraintĪs you can see here, the unique constraint that we had created in the previous example has been deleted. To delete the constraint, specify the constraint name after the DROP CONSTRAINT clause in the below query. In this case, you can delete the unique constraint using the ALTER command. Let’s say you have created the unique constraint on the table and you later think it is unnecessary. Drop the Unique Constraint from the Table ALTER TABLE employeesĪDD UNIQUE(empId,deptId) Code language: PostgreSQL SQL dialect and PL/pgSQL ( pgsql ) Create Unique Constraint On Existing TableĪs you can see, the unique constraint has been added to the table. ) Code language: PostgreSQL SQL dialect and PL/pgSQL ( pgsql ) No Unique Constraint PresentĪs you can see, there is no constraint present in the table. If you forgot to create the unique constraint while creating the table, no worries, you can still create the unique constraint on your existing table. Create a UNIQUE Constraint on Existing Table Here, the combination of the empId and deptId is used as a unique key. ) Code language: PostgreSQL SQL dialect and PL/pgSQL ( pgsql ) Multi-Column Unique Constraint We can create a unique constraint on the group of columns. ) Code language: PostgreSQL SQL dialect and PL/pgSQL ( pgsql ) Custom Named Unique ConstraintĪs you can see in the highlighted area, PostgreSQL has created the unique constraint of having the name assigned by us. Create Custom Named UNIQUE Constraint DROP TABLE IF EXISTS students Įmail VARCHAR( 100) CONSTRAINT uniqueEmail UNIQUE You can also assign any name to the constraint as shown below. Here, PostgreSQL automatically generates the name for the unique constraint. ) Code language: PostgreSQL SQL dialect and PL/pgSQL ( pgsql ) Single Column Unique Constraint We can create the single-column unique constraint using the UNIQUE keyword inline. Create a UNIQUE Constraint on a Single-Column Now let’s see examples of each syntax one by one. However, this behaviour can be changed by using the UNIQUE NULLS NOT DISTINCT clause. The UNIQUE constraint is ignored for the null values, and because two null values are not equal, we can have multiple null values in the table. We will see examples of each syntax further in this tutorial. Here, each syntax creates a unique constraint but is used for different circumstances. UNIQUE( ) Ĭode language: PostgreSQL SQL dialect and PL/pgSQL ( pgsql ) syntax 2 - creates default / custom named unique constraint on multiple columns CREATE TABLE tableName( syntax 1 - creates default / custom named unique constraint on single column CREATE TABLE tableName(ĬolName datatype UNIQUE Check the syntaxes below to understand when to use which syntax. You can create the unique constraint in PostgreSQL in multiple ways. Moreover, you can have as many unique constraints as you want on a single table. The UNIQUE constraint is similar to the primary key except that the UNIQUE constraint accepts the null values.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |