Making a Non Primary key a unique column

4 Jun 2005 5:41 PM

RD

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,

RD

4 Jun 2005 6:15 PM

Tibor Karaszi

It is difficult for us to answer without knowing exactly what you did and how 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/

Show quoteHide quote

"RD" <nospam@nospam.net> wrote in message news:%23hgdDzSaFHA.2996@TK2MSFTNGP10.phx.gbl...
>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,
>
> RD
>
>
>

4 Jun 2005 6:19 PM

David Portas

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
--

4 Jun 2005 7:43 PM

--CELKO--

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.

5 Jun 2005 2:01 PM

RD

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.

Show quoteHide quote

"--CELKO--" <jcelko***@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.
>