Making a Non Primary key a unique column

I have a table in which a column that is not part of the primary key must
contain data that can not be duplicated in the same column in another row
but it should not - for other design reasons - be the primary key.
I created an index on that field in the table and in the properties for that
index I checked Create Unique and checked Constraint. I thought that that
would prevent entering duplicate values in that field in that table.
However, when I tested this in the table in the data entry screen of
Enterprise manager, I was able to enter duplicate values in that field in
several rows in that table and the database did not return any error
messages.
What's wrong here, can anyone shed light on this behaviour?
How do I achieve the goal set out above?
The field giving me the problem is an nvarchar type max 50 length.
Thanks for any help,
RDIt is difficult for us to answer without knowing exactly what you did and ho
w we can reproduce it.
Can you post CREATE TABLE, CREATE INDEX or ALTER TABLE ADD UNIQUE CONSTRAINT
with some insert
statements we can run to reproduce the behavior?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"RD" <nospam@.nospam.net> wrote in message news:%23hgdDzSaFHA.2996@.TK2MSFTNGP10.phx.gbl...[c
olor=darkred]
>I have a table in which a column that is not part of the primary key must
> contain data that can not be duplicated in the same column in another row
> but it should not - for other design reasons - be the primary key.
> I created an index on that field in the table and in the properties for th
at
> index I checked Create Unique and checked Constraint. I thought that that
> would prevent entering duplicate values in that field in that table.
> However, when I tested this in the table in the data entry screen of
> Enterprise manager, I was able to enter duplicate values in that field in
> several rows in that table and the database did not return any error
> messages.
> What's wrong here, can anyone shed light on this behaviour?
> How do I achieve the goal set out above?
> The field giving me the problem is an nvarchar type max 50 length.
> Thanks for any help,
> RD
>
>[/color]|||A UNIQUE constraint should ineed prevent duplicate values. I suspect the
constraint has not been created as you wanted it. In Query Analyzer you can
easily generate the script for the constraint so that you can verify it and
edit it as necessary (right-click on the constraint in the Object Browser,
then click Script Object to New Window As > Create).
One reason I prefer to use QA rather than EM for any structure changes is
that you have better control and visibility over what is happening. However,
you can do a similar thing in EM when you change something in the Table
Designer. You can click the Save Change Script button on the toolbar (3rd
one along) to show you the actual script that will make the changes. The
complete change script EM generates is harder to read however than the
equivalent in QA.
Most of us will be glad when the EM/QA duality disappears in SQL2005 to be
replaced by a single place for all management and development tasks.
David Portas
SQL Server MVP
--|||You might want to create the table in QA with DDL and use the UNIQUE
constraint. This will document your design better. I have no idea why
EM would not do this properly.|||Thanks to all for your explanations.
Indeed it works properly as explained by you and the docs.
This morning I just tried again and realized that the duplicate data I
thought I entered was not EXACTLY duplicate after all, hence my mistaken
belief that it didn't work.
As usual the problem is 18 inches from the screen.
Sorry to have disturbed you like that, comes with old age I guess. Can't
stay up late anymore and do anything worthwhile ;-)
RD.
"--CELKO--" <jcelko212@.earthlink.net> wrote in message
news:1117914232.752590.247840@.g49g2000cwa.googlegroups.com...
> You might want to create the table in QA with DDL and use the UNIQUE
> constraint. This will document your design better. I have no idea why
> EM would not do this properly.
>