Menu
I have a Table like this one:
|UserId | ContactID | ContactName --------------------------------------- | 12456 | Ax759 | Joe Smith | 12456 | Ax760 | Mary Smith | 12458 | Ax739 | Carl Lewis | 12460 | Ax759 | Chuck Norris | 12460 | Bx759 | Bruce Lee I need to add a constraint to this table so that no user can have duplicate contact id's. The users are importing data from various external systems so ContactId will not be unique across the board but will be unique on a per user basis.
I know how to create Unique and Non-Null contraints based on single columns but how can I create a unique contraints across 2 columns?
384
SQL UNIQUE constraint for 2 columns example Notice that we named the UNIQUE constraints using CONSTRAINT keyword. We can use this name to remove the UNIQUE constraint later if we want. To define a UNIQUE on multiple columns, we put a comma-separated columns list inside parenthesis that follows the UNIQUE keyword.
To define a UNIQUE constraint, you use the UNIQUE keyword followed by one or more columns. You can define a UNIQUE constraint at the column or the table level. Only at the table level, you can define a UNIQUE constraint across multiple columns.
UNIQUE key does not allow duplicate values. UNIQUE key allows NULL values but does not allow NULL values multiple times. We can create multiple UNIQUE columns on one table however only one PRIMARY KEY for table. Defining primary key on a column has a UNIQUE constraint property by default.
The syntax for creating a unique constraint using an ALTER TABLE statement in MySQL is: ALTER TABLE table_name ADD CONSTRAINT constraint_name UNIQUE (column1, column2, ... column_n); table_name.
You can try this:
CREATE UNIQUE CLUSTERED INDEX index_name ON TABLE (col1,col2) or
CREATE UNIQUE NONCLUSTERED INDEX index_name ON TABLE (col1,col2) or
ALTER TABLE [dbo].[TABLE] ADD CONSTRAINT UNIQUE_Table UNIQUE CLUSTERED ( col1, col2 ) ON [PRIMARY] You can add unique constraint tou your fields:
ALTER TABLE YourTable ADD CONSTRAINT UQ_UserId_ContactID UNIQUE(UserId, ContactID) If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
