Hello, I am new to SQL Server 2005 and I have 2 questions.

1) I am wondering how to set unique constraint on 'username' field in
SQL Server Management Studio in Visual Way but not using Query Window.
Here is the users table. would u please give me a step by step approach
to achieve it?

2) The users tables script is generated by the management studio, but I
am wondering why the script needs to clarify IGNORE_DUP_KEY = OFF. As
far as know, if a primary key constraint is set, it means the field
cannot be duplicated, so what is the purpose of  IGNORE_DUP_KEY = OFF ?

Thanks for answering my questions.


CREATE TABLE [dbo].[users](
    [user_id] [numeric](18, 0) IDENTITY(1,1) NOT NULL,
    [role_id] [numeric](18, 0) NOT NULL,
    [fullname] [varchar](max),
    [username] [varchar](max),
    [password] [varchar](max),
CONSTRAINT [PK_users_1] PRIMARY KEY CLUSTERED
(
    [user_id] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

1) Seriously: do not use visual tools for complex schema changes. Adding
constraints usually is a complex schema change - you should at the very least
keep the modification script stored in a safe place along with all other
scripts - so that once the modifications have been applied at the
development/test location and tested they can be just as simply applied at
the live (production) location.

2) The code generated by SSMS assumes that you're adding a constraint
*after* you've already made sure existing data corresponds with the
constraint (i.e. it's already unique), so that when you add it the engine
does not have to check all existing values, thus making the schema
modification more quickly.

If the above assumption is wrong, existing data may be in conflict with the
constraint, new data, however, will have to correspond to the constraint.


ML

---
http://milambda.blogspot.com/

ML wrote:
That's not what IGNORE_DUP_KEY does. IGNORE_DUP_KEY = ON means that if
the constraint is violated by an INSERT statement then a warning is
raised instead of an error and valid rows (not the duplicates) will be
inserted successfully. Duplicate values will not be permitted in the
table under any circumstances regardless of whether IGNORE_DUP_KEY is
ON or OFF. If duplicates already exist in the table then the constraint
will not be created.

In my view there are few cases which justify the use of IGNORE_DUP_KEY
= ON. The ON option means that inserts are no longer set-based in
nature and this may have big implications for how you perform and
control updates to the table. I wouldn't usually recommend using it
except perhaps in a staging database environment.

Yes, of course. My mistake. I was talking about the WITH NOCHECK option. Must
have been due to lack of coffee.


ML

---
http://milambda.blogspot.com/

On 25 Aug 2006 02:18:48 -0700, philip wrote:

(snip)
Hi Philip,

That clause could have been omitted, as OFF is the default for the
IGNORE_DUP_KEY setting. It's not uncommon for generated code to contain
useless options - I guess it's easier to always generate the option with
either ON or OFF than to completely leave it out if it's OFF.

The purpose of this option, as David already explained, is to cause SQL
Server to reject rows that would violate the constraint but allow others
even if they were all inserted in a single INSERT statement. Since this
violates ACID properties, I wouldn't mind if Microsoft took the option
out of the product.

how to pass in a long string to stored procedure
Need Help With a Simple Query
Error converting data type varchar to numeric
Check int value
Strange characters with Bulk Load