Add Unique Constraint in SQL Server 2008 Gui

Add unique constraint in SQL Server 2008 GUI?

You need to right-click in the table designer and pick Indexes/Keys:

Then a dialog pops up and you can add a new index to the list of indices (on the left hand side) and define it to be a unique index:

How can I create a unique constraint on my column (SQL Server 2008 R2)?

To create these constraints through the GUI you need the "indexes and keys" dialogue not the check constraints one.

But in your case you just need to run the piece of code you already have. It doesn't need to be entered into the expression dialogue at all.

SQL Server 2008: Unique constraint for values non-related with columns

You could write a view like that:

select 1 as Dummy
from T t1
join T t2 on t1.ID1 = t2.ID2 AND t1.ID2 = t2.ID1 --join to corresponding row
cross join TwoRows

And create a unique index on Dummy. TwoRows is a table that contains two rows with arbitrary contents. It is supposed to make the unique index fail if there ever is a row in it. Any row in this view indicates a uniqueness violation.

Unique key vs. unique index on SQL Server 2008

A unique constraint is implemented behind the scenes as a unique index, so it doesn't really matter how you specify it. I tend to implement it simply as:

ALTER TABLE dbo.foo ADD CONSTRAINT UQ_bar UNIQUE(bar);

Some people create a unique index instead, e.g.

CREATE UNIQUE INDEX IX_UQ_Bar ON dbo.foo(bar);

The difference is in the intent - if you are creating the constraint to enforce uniqueness/business rules, you create a constraint, if you are doing so to assist query performance, it might be more logical to create a unique index. Again, under the covers it's the same implementation, but the road you take to get there may help document your intent.

I think there are multiple options to adhere to both previous Sybase functionality as well as to adhere to the ANSI standard (even though unique constraints don't adhere to the standard 100%, since they only allow one NULL value - a unique index, on the other hand, can work around this by adding a WHERE clause (WHERE col IS NOT NULL) on SQL Server 2008 and greater).

Unique constraint on multiple columns

By using the constraint definition on table creation, you can specify one or multiple constraints that span multiple columns. The syntax, simplified from technet's documentation, is in the form of:

CONSTRAINT constraint_name UNIQUE [ CLUSTERED | NONCLUSTERED ] 
(
    column [ ASC | DESC ] [ ,...n ]
)

Therefore, the resuting table definition would be:

CREATE TABLE [dbo].[user](
    [userID] [int] IDENTITY(1,1) NOT NULL,
    [fcode] [int] NULL,
    [scode] [int] NULL,
    [dcode] [int] NULL,
    [name] [nvarchar](50) NULL,
    [address] [nvarchar](50) NULL,
    CONSTRAINT [PK_user_1] PRIMARY KEY CLUSTERED 
    (
        [userID] ASC
    ),
    CONSTRAINT [UQ_codes] UNIQUE NONCLUSTERED
    (
        [fcode], [scode], [dcode]
    )
) ON [PRIMARY]

How can I force a column to be unique for an entire table in SQL Server 2008 R2?

Add a Unique index to the Description column.

Using Sql Server Management Studio right click on the table and choose Design. Then right click on a column and choose "Indexes/keys". You will be prompted with the following window

Click on Add on the bottom left and then specify properties for your index. If you want to use a DDL script then use something like this

CREATE UNIQUE NONCLUSTERED INDEX [IX_INDEXNAME] ON [dbo].[TABLENAME] 
(
    [Description] ASC
)
WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
GO

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

t-sql unique constraints over 7 columns by sql management studio

CREATE TABLE Example
(
  Col1 int NOT NULL,
  Col2 int NOT NULL,
  Col3 int NOT NULL,
  Col4 int NOT NULL,
  Col5 int NOT NULL,
  Col6 int NOT NULL,
  Col7 int NOT NULL
)

CREATE INDEX CREATE UNIQUE NONCLUSTERED INDEX UI_IndexName ON Table (Col1, Col2...)

---

Related Topics