How to Force a Column to Be Unique for an Entire Table in SQL Server 2008 R2

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 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 Column that is Case Sensitive

The uniqueness can be enforced with a unique constraint.

Whether or not the unique index is case-sensitive is defined by the server's (or the table's) collation.

You can get the current collation of your database with this query:

SELECT DATABASEPROPERTYEX('AdventureWorks', 'Collation') SQLCollation;

and you should get something like:

SQLCollation
————————————
SQL_Latin1_General_CP1_CI_AS

Here, the "CI_AS" at the end of the collation means: CI = Case Insensitive, AS = Accent sensitive.

This can be changed to whatever you need it to be. If your database and/or table does have a case-sensitive collation, I would expect that the uniqueness of your index will be case-sensitive as well, e.g. your abcdef and ABCDEF should be both acceptable as unique strings.

Marc

UPDATE:

I just tried this (SQL Server 2008 Developer Edition x64) - works for me (my database is generally using the "Latin1_General_CI_AS collation, but I can define a different one per table / per VARCHAR column even):

CREATE TABLE TestUnique
    (string VARCHAR(50) COLLATE SQL_Latin1_General_Cp1_CS_AS)

CREATE UNIQUE INDEX UIX_Test ON dbo.TestUnique(string)

INSERT INTO dbo.TestUnique(string) VALUES ('abc')
INSERT INTO dbo.TestUnique(string) VALUES ('ABC')

SELECT * FROM dbo.TestUnique

and I get back:

string
ABC
abc

and no error about the unique index being violated.

Add a column to existing table and uniquely number them on MS SQL Server

This will depend on the database but for SQL Server, this could be achieved as follows:

alter table Example
add NewColumn int identity(1,1)

How to add a unique constraint using a column from another table?

You could use an indexed view as an external constraint:

CREATE VIEW dbo.CompanyServices
WITH SCHEMABINDING
AS
SELECT
  c.COMPANY_ID,
  s.SERVICE_CODE
FROM       dbo.COMPANY c
INNER JOIN dbo.LSP     l ON c.COMPANY_ID = l.COMPANY_ID
INNER JOIN dbo.SERVICE s ON l.LSP_ID     = s.LSP_ID
GO

CREATE UNIQUE CLUSTERED INDEX UQ_CompanyServices
ON dbo.CompanyServices (COMPANY_ID, SERVICE_CODE);

The index will make sure there's no duplicates of (COMPANY_ID, SERVICE_CODE) in your data.

How can I enforce uniqueness on a NVARCHAR(MAX) column in SQL Server?

1. Create a persisted column on some kind of hash, such as CHECKSUM. For example: Hash = MyStringHash as CHECKSUM(MyString)
2. Create a non-unique index on that column

3. Create a trigger that enforces uniqueness. The index is needed to speed up the search in EXISTS clause:

           WHERE   NOT EXISTS ( SELECT 1
                                FROM   YourTable AS y
                                WHERE  y.MyStringHash = CHECKSUM(Inserted.MyString)
                                       AND y.MyString = Inserted.MyString) ;
   

Important: you need to test with respect to your collation. If you are using a case-insensitive collation, make sure that the trigger will not allow both 'MyTest' and 'MYTEST'.

If you go for a unique index and stop at that, you are just creating a bug waiting to happen.

Edit: in a case-insensitive environment I have used CHECKSUM for a persisted computed column, which is fast, case-insensitive, and selective enough.

Add a column with a default value to an existing table in SQL Server

Syntax:

ALTER TABLE {TABLENAME} 
ADD {COLUMNNAME} {TYPE} {NULL|NOT NULL} 
CONSTRAINT {CONSTRAINT_NAME} DEFAULT {DEFAULT_VALUE}
WITH VALUES

Example:

ALTER TABLE SomeTable
        ADD SomeCol Bit NULL --Or NOT NULL.
 CONSTRAINT D_SomeTable_SomeCol --When Omitted a Default-Constraint Name is autogenerated.
    DEFAULT (0)--Optional Default-Constraint.
WITH VALUES --Add if Column is Nullable and you want the Default Value for Existing Records.

Notes:

Optional Constraint Name:

If you leave out CONSTRAINT D_SomeTable_SomeCol then SQL Server will autogenerate

    a Default-Contraint with a funny Name like: DF__SomeTa__SomeC__4FB7FEF6

Optional With-Values Statement:

The WITH VALUES is only needed when your Column is Nullable

    and you want the Default Value used for Existing Records.

If your Column is NOT NULL, then it will automatically use the Default Value

    for all Existing Records, whether you specify WITH VALUES or not.

How Inserts work with a Default-Constraint:

If you insert a Record into SomeTable and do not Specify SomeCol's value, then it will Default to 0.

If you insert a Record and Specify SomeCol's value as NULL (and your column allows nulls),

    then the Default-Constraint will not be used and NULL will be inserted as the Value.

Notes were based on everyone's great feedback below.

Special Thanks to:

    @Yatrix, @WalterStabosz, @YahooSerious, and @StackMan for their Comments.

SQL Server 2008: The columns in table do not match an existing primary key or unique constraint

It means that the primary key in tblOne hasn't been properly declared - you need to go to tblOne and add the PRIMARY KEY constraint back onto it.

If you're sure that tblOne does have a PRIMARY KEY constraint, then maybe there are multiple tblOne tables in your DB, belonging to different schemas, and your references clause in your FK constraint is picking the wrong one.

If there's a composite key (which your comment would indicate), then you have to include both columns in your foreign key reference also. Note that a table can't have multiple primary keys - but if it has a composite key, you'll see a key symbol next to each column that is part of the primary key.

How to ensure my SQL varchar field is unique

I resolve this using an IF EXISTS inside a WHILE loop..

Personally I can't see what's wrong with this method but will obviously take any comments into account

DECLARE @NameInvalid varchar(100)
DECLARE @DealName varchar(100)
DECLARE @Count int
SET @Count = 1
SET @NameInvalid = 'true'

SELECT @DealName = DealName FROM Deal WHERE DealId = @DealId

--Ensure we get a unique deal name
WHILE( @NameInvalid = 'true')
BEGIN
    IF NOT EXISTS(SELECT DealName FROM Deal where DealName = @DealName + ' Cloned ' + cast(@Count as varchar(10)))
    BEGIN
        INSERT INTO Deal
            (DealName)
            SELECT @DealName + ' Cloned ' + cast(@Count as varchar(10))
                FROM Deal
                WHERE DealID = @DealId
            SET @NewDealId = @@IDENTITY
        SET @NameInvalid = 'false'
    END
    ELSE
    BEGIN
        SET @NameInvalid = 'true'
        SET @Count = @Count + 1
    END
END

---

Related Topics