Add UNIQUE constraint

Hi,
I want to add unique contraint to EXISTING column in EXISTING table.

I have ms sql 2005.

How to do that?

If you have ms SQL Express 2005, then you should probably download and install SQL Server Management Studio Express. Once that's done, it is easy - attach the database file to SQL Express, then add the unique constraint there.

Careful if you are coming from an MS Access world. In MS Access, you can specify a unique constraint that will ignore NULL values. In that case, the constrained column will be unique only if the value is not null. You can have many entries where the value is null. If that is an issue, then there is another way to do it using a Schema-bound view that selects only non-null entries from the original table and applies a unique index to the entry in the view.

You will also find an excellent reference in the SQL Books Online, which you can download and install on your machine.

Hope this helps,

Flavelle

|||I have SQL Standrard. I read books online.

I need exact steps, I cannot understand Books.

Can you please provide me with steps or procedure that would do that?|||

give me a little time, and I'll come up with a couple of sample scripts that you can run.

Flavelle

|||Ok, I'm waiting.

Just give me the simplest script, or explain how to set it in Studio.|||

fballem:

If you have ms SQL Express 2005, then you should probably download and install SQL Server Management Studio Express. Once that's done, it is easy - attach the database file to SQL Express, then add the unique constraint there.

Careful if you are coming from an MS Access world. In MS Access, you can specify a unique constraint that will ignore NULL values. In that case, the constrained column will be unique only if the value is not null. You can have many entries where the value is null. If that is an issue, then there is another way to do it using a Schema-bound view that selects only non-null entries from the original table and applies a unique index to the entry in the view.

You will also find an excellent reference in the SQL Books Online, which you can download and install on your machine.

Hope this helps,

Flavelle

In SQL Server you can create a Unique Index with the IGNORE_DUP_KEY option and duplicates will not be inserted. Unique constraints are nullable while Unique indexes are not. Try the link below for step by step of how to create Unique Constraint with the GUI in management studio. Hope this helps.

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/vdtsql/dvhowcreatinguniqueindex.asp

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/vdtsql/dvhowcreatinguniqueconstraint.asp

|||Thanks a lot.
These articles really helped me.|||

And if you wanted to do it via script, here are two versions:

This script, applied to a table, will result in a unique index. If the column allows Null, then only one row can contain Null. Replace TestTable with the name of your table and TestColumnUnique with the name of the column:

ALTER TABLE [dbo].[TestTable]ADD CONSTRAINT [UQ_TestTable_TestColumnUnique]UNIQUE NONCLUSTERED ([TestColumnUnique]ASC)WITH (PAD_INDEX =OFF, SORT_IN_TEMPDB =OFF, IGNORE_DUP_KEY =OFF, ONLINE =OFF)ON [PRIMARY]

The following two scripts will result in a schema-bound view with an index:

The first script will create the schema bound view. Note that TestView is the name of the view, TestTable is the name of the table from which the data will be obtained and TestColumnNonNullUnique is the name of the column to which you want to apply the constraint. Note that only non-null values are included in the view:

CREATE VIEW [dbo].[TestView]
WITH SCHEMABINDING
AS
SELECT ID, TestColumnNonNullUnique
FROM dbo.TestTable
WHERE (TestColumnNonNullUniqueISNOT NULL)
GO

Once you have set up the view, then run the following script, which will setup the index on the view. Because only non-null values are included in the view, you can have multiple null entries in the original column:

CREATE UNIQUE CLUSTERED INDEX [UQ_TestView_TestColumnNonNullUnique]ON [dbo].[TestView] ([TestColumnNonNullUnique]ASC)WITH (PAD_INDEX =OFF, SORT_IN_TEMPDB =OFF, DROP_EXISTING =OFF, IGNORE_DUP_KEY =OFF, ONLINE =OFF)ON [PRIMARY]

Hope that this is the final piece to the puzzle. I wish that I could take credit for this approach, but another member of the forum was kind enough to provide me with the solution, so I am happy to pass it on.

Regards,

Flavelle