PRIMARY KEY/NOCHECK

Question

PRIMARY KEY/NOCHECK

MVDBA (Mike Vessey)

SSC-Insane

Points: 21797
More actions
November 2, 2004 at 6:58 am

#182607

can anyone answer this question for me.
SQL server 2000
In books online the alter table statement specifies that when creating a primary key or unique constraint you canot use the NOCHECK hint.
and yet if you use the generate script option in enterprise manager it uses the NOCHECK.
I had a customer who's database contains no primary keys at all and having looked at the script from the vendor it's clear that they have used enterprise manager to script the tables. Where there is a NOCHECK on a primary key the server has created just a plain vanilla index.
CREATE TABLE DocumentsOut (
ID varchar (50) NOT NULL ,
Keyword varchar (50) NULL ,
Status int,
Date_Received datetime NOT NULL ,
Version_Number int NULL ,
Row_Updated bigint NULL ,
Last_Updated datetime NOT NULL ,
Root_Tag varchar (50) ,
XML text NULL
)
ALTER TABLE DocumentsOut WITH NOCHECK ADD
CONSTRAINT PK_DocumentsOut PRIMARY KEY CLUSTERED
(
ID
)
Since then the SQL server has been service packed etc so the problem cannot be replicated, however i now have to re-script all of the primary keys and drop the existing indexes.
anyone else hit this issue?
MVDBA

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Adam Machanic SSCoach Points: 15370 More actions · Answer 1

I'm a bit confused by your post; what is the problem here? The PK -- at least on my end when I run the DDL (SS2000SP3a) -- is still created. Why is the NOCHECK bothering you?

--
Adam Machanic
whoisactive

Frank Kalis SSC Guru Points: 111183 More actions · Answer 2

Michael,

just out of curiosity, can you post the text from BOL.

All I have found in the explanations for ALTER TABLE is:

When a new PRIMARY KEY or UNIQUE constraint is added to an existing column, the data in the column(s) must be unique. If duplicate values are found, the ALTER TABLE statement fails. The WITH NOCHECK option has no effect when adding PRIMARY KEY or UNIQUE constraints.

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

MVDBA (Mike Vessey) SSC-Insane Points: 21797 More actions · Answer 3

here is the post from BOL

WITH CHECK | WITH NOCHECK

Specifies whether the data in the table is or is not validated against a newly added or re-enabled FOREIGN KEY or CHECK constraint. If not specified, WITH CHECK is assumed for new constraints, and WITH NOCHECK is assumed for re-enabled constraints.

The WITH CHECK and WITH NOCHECK clauses cannot be used for PRIMARY KEY and UNIQUE constraints.

the problem doesn't occur on my sp3a version either - however quite clearly microsofts tools produce code that is contra to their BOL documentation.

The problem HAS occurred at one of my customers where the same problem occurs on 300 SQL servers located in High street stores.

The database script may have been issued before sp3, however I can't find where this has been fixed.

I can also find similar issues related to SQL7 where the key is not created if a clustered index does not already exist, but this doesn't affect 2000.

Any ideas on what i can tell my customer the application vendor should have done before installing the database. (sp2? sp3?)

MVDBA

Frank Kalis SSC Guru Points: 111183 More actions · Answer 4

Hm, strange. Are you sure you have the latest version of BOL?

This is what I can find in my BOL version:

WITH CHECK | WITH NOCHECK

Specifies whether the data in the table is or is not validated against a newly added or re-enabled FOREIGN KEY or CHECK constraint. If not specified, WITH CHECK is assumed for new constraints, and WITH NOCHECK is assumed for re-enabled constraints.

If you do not want to verify new CHECK or FOREIGN KEY constraints against existing data, use WITH NOCHECK. This is not recommended except in rare cases. The new constraint will be evaluated in all future updates. Any constraint violations suppressed by WITH NOCHECK when the constraint is added may cause future updates to fail if they update rows with data that does not comply with the constraint.

Constraints defined WITH NOCHECK are not considered by the query optimizer. These constraints are ignored until all such constraints are re-enabled using ALTER TABLE table CHECK CONSTRAINT ALL.

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]