SQLite - UNIQUE Constraint


Advertisements


The UNIQUE Constraint prevents two records from having identical values in a particular column. In the CUSTOMERS table, for example, you might want to prevent two or more people from having identical age.

Example

For example, the following SQL creates a new table called CUSTOMERS and adds five columns. Here, AGE column is set to UNIQUE, so that you cannot have two records with same age −

CREATE TABLE COMPANY(
   ID   INT       PRIMARY KEY        NOT NULL,
   NAME          TEXT     NOT NULL,
   AGE  INT              NOT NULL UNIQUE,
   ADDRESS  CHAR (25) ,
   SALARY   REAL  ,       
   
);

If COMPANY table has already been created, then to add a UNIQUE constraint to AGE column, you would write a statement similar to the following −

ALTER TABLE COMPANY
   MODIFY AGE INT NOT NULL UNIQUE;

You can also use following syntax, which supports naming the constraint and multiple columns as well −

ALTER TABLE COMPANY
   ADD CONSTRAINT myUniqueConstraint UNIQUE(AGE, SALARY);

DROP a UNIQUE Constraint

To drop a UNIQUE constraint, use the following SQL −

ALTER TABLE COMPANY
   DROP CONSTRAINT myUniqueConstraint;


Advertisements