SQL Server 2005 How Create a Unique Constraint

SQL Server 2005 How Create a Unique Constraint?

The SQL command is:

ALTER TABLE  ADD CONSTRAINT
             UNIQUE NONCLUSTERED
    (
                
    )

See the full syntax here.

If you want to do it from a Database Diagram:

• right-click on the table and select 'Indexes/Keys'
• click the Add button to add a new index
• enter the necessary info in the Properties on the right hand side:
  
  
  • the columns you want (click the ellipsis button to select)
  • set Is Unique to Yes
  • give it an appropriate name

How to create Composite Unique Constraint in SQL Server 2005

In SQL Server Management Studio

• goto the Object Explorer
• pick your table and open its designer (can't remember what it was called in 2005 - Modify Table or something?)
• in the table designer, pick the "Manage Indexes and Keys" icons from the toolbar (the table with the little key)
• in there, add a new index and give it a name, click it's "Unique" setting

• open the list of columns in the index definition and add your columns you want to thave in the index

That's it! :)

How to create UNIQUE constraint in SSMS 2012

You were almost there. If you use the constraint keyword you need to provide a name.

ALTER TABLE Attendance ADD CONSTRAINT UQ_TagID_SessionID UNIQUE NONCLUSTERED
(
    TagID,
    SessionID
)

Or alternatively

ALTER TABLE Attendance ADD UNIQUE NONCLUSTERED
(
    TagID,
    SessionID
)

also works but then the constraint is auto named

SQL Server 2005 Unique constraint on two columns

In SQL Server, a unique constraint is really implemented as a unique index. Use:

CREATE UNIQUE INDEX  ON (, )

For more info, see this MSDN page.

Unique Constraint on condition in SQL Server

Just create a unique index on name and stop:

create unique index idx_supplier_shop_name on supplier(shop, name)

This should do exactly what you want.

As a note: you can equivalently do this with a unique constraint in the table definition or via alter table as well.

How do I create a unique constraint that also allows nulls?

SQL Server 2008 +

You can create a unique index that accept multiple NULLs with a WHERE clause. See the answer below.

Prior to SQL Server 2008

You cannot create a UNIQUE constraint and allow NULLs. You need set a default value of NEWID().

Update the existing values to NEWID() where NULL before creating the UNIQUE constraint.

Does unique constraint on multiple columns create index on each column

It will create only one index on two columns and both the column combinedly can not contain duplicate in the entire table.

You can add multiple duplicates for colA but considering that the colB is different for each row having same colA and vice-versa.

colA   colB
Tejash SO
Tejash SO1
Tejash SO2

Allowed

or

colA colB
SO1  TEJASH
SO2  TEJASH
SO3  TEJASH

is also allowed.

---

Related Topics