Search code examples
sqlconstraintsunique

ADD combination constraint into a dabase table

I want to make a combination of columns in a table unique which they can not have any duplicates value.

For example, I have a table of Person which contain a name, age, address and phone_number.

I want that the combination of (name, age, phone_number) must be unique.

The table person contain this data:

"Name1"|22|"adresse1"|000000

So the combination (''Name1'' and 22 and 000000) it has to be unique but if only one of them changed for example ("name2",22,000000) it will be permitted to insert data to the table.

How can I manage that with SQL please?

Solution

  • If you want a column or combination of columns to be unique, you can implement a unique constraint or index (they are functionally equivalent for this purpose):

    create unique index unq_t_col1_col2_col3 on t(col1, col2, col3);

    And for a constraint:

    alter table t add constraint unq_t_col1_col2_col3
    unique (col1, col2, col3);

    Any attempt to insert values into the columns that already exists will result in an error, as will any attempt to update the columns with values in a different row.