Designating an identity column when you already have primary key

Hello --

Following normal practice, I have an autoincrementing identity column designated as primary key in my table. I have two other columns that should also contain unique values per record, but the Identity option is greyed out (in Management Studio) for all columns other than the primary key.

I'm enforcing this programmatically (in my C# code) at this point, but I'd like to back that up with a constraint in the database itself.

Any help is appreciated.

Eric

Hello my friend,

Turn Allow Nulls off for these other 2 columns. In addition to this, here is some SQL you can run at any time to check for duplicate values: -

SELECT ErrorTypeID, COUNT(*) AS Counter
FROM TBLERROR
GROUP BY ErrorTypeID
HAVING COUNT(*) > 1
ORDER BY COUNT(*) DESC

Kind regards

Scotty

|||tblErrror is a custom table in my database. Substitute it for your table and the field you want to check|||

Thanks, Scotty.

Is there any way to apply manual constraints so the duplicate value doesn't end up in the table in the first place, or would I instead have to insert new rows through a stored procedure and apply the check/abort in there?

|||

Hello again my friend,

Put this index on your table: -

CREATE UNIQUE INDEX MyIndexName ON MyTableName (MyFieldName)

If there are duplicates already, the above statement will fail. If there are not, it will work and if someone, or some code, tries to insert a duplicate, the record will not be inserted/updated and an error will result.

Kind regards

Scotty

|||

If you want to remove an index you have added, do the following: -

DROP INDEX MyTableName.MyIndexName

Kind regards

Scotty

|||

EricLaszlo:

Hello --

Following normal practice, I have an autoincrementing identity column designated as primary key in my table. I have two other columns that should also contain unique values per record, but the Identity option is greyed out (in Management Studio) for all columns other than the primary key.

I'm enforcing this programmatically (in my C# code) at this point, but I'd like to back that up with a constraint in the database itself.

Any help is appreciated.

Eric

The IDENTITY property can generate auto numbers for your column however it does not guarantee uniqueness because IDENTITY can generate gaps, so Microsoft recommend you use GUID instead. But you cannot use GUID because you have three columns that need to be Unique so that leave only one option Unique constraint because you can define more than one Unique constraint in a table. I have seen table definitions with many constraints so you could run some tests in relation to ADO.NET as needed for your application. Try the link below for details. Hope this helps.

http://msdn2.microsoft.com/en-us/library/ms191166.aspx

|||

Thank you both very much for your input.

Based on that, I was able to apply the contstraints with the following syntax:

ALTER

TABLE Profiles

ADD

CONSTRAINT ncEmail

UNIQUE

NONCLUSTERED(Email)

Having read a little about clustered indices in my travels, I tried to apply one to my Id column - didn't notice that this column already had a clustered index - not sure if that was from designating is as and Identity column or applying primary key.

In any case, I think I'm good for now...