CODEWITHSUNDEEP
sqloracle-databasefieldconstraints
James
James

Reputation: 73

How do I create a constraint in Oracle to avoid duplicate values?

I have to create a constraint that will prevent anyone from adding a row to the table that contains a duplicate email address, although null values are allowed in the email address column.

The table is already made, and I know how to create a basic check constraint.

How would I go through this without using a sub-query? (because I cannot use them in constraints :()

ALTER TABLE EMPS
ADD CONSTRAINT EMPSEMAIL_ALREADYEXISTS CHECK (EMAIL IS NULL AND ???);

SQL Command to obtain all the emails from the EMPS

SELECT EMAIL FROM EMPS;

Upvotes: 3

Views: 2985

Answers (1)

user330315
user330315

Reputation:

You don't need a check constraint you need a unique index:

create unique index ix_email on EMPS (email);

Oracle does not include tuples in an index where all columns are NULL. in this case there is only a single column, so NULL values won't be indexed

Other DBMS don't allow more than one NULL value for a unique index like that

Upvotes: 8

Related Questions