Index Question

Hello,

In MSS 2k can Primary Key, Unique Constraints, Indexes and Foreign Keys be
disabled? If Indexes (PK's and UN Constraints) can be disabled what happens
if data is inserted while disable? Will the index be rebuilt when enabled?

Thanks,
Rob PanoshRob Panosh (rob_!!!NO!!!SPAM!!!_panosh@.asdsoftadfdware.com) writes:
> In MSS 2k can Primary Key, Unique Constraints, Indexes and Foreign Keys
> be disabled? If Indexes (PK's and UN Constraints) can be disabled what
> happens if data is inserted while disable? Will the index be rebuilt
> when enabled?

You can disable foreign-key constraints. When you re-enable them, SQL
Server verifies that the data comply to the constraint.

You cannot disable primary-key or unique constraints, nor indexes.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Thanks ...

"Erland Sommarskog" <sommar@.algonet.se> wrote in message
news:Xns940E15C48E6CYazorman@.127.0.0.1...
> Rob Panosh (rob_!!!NO!!!SPAM!!!_panosh@.asdsoftadfdware.com) writes:
> > In MSS 2k can Primary Key, Unique Constraints, Indexes and Foreign Keys
> > be disabled? If Indexes (PK's and UN Constraints) can be disabled what
> > happens if data is inserted while disable? Will the index be rebuilt
> > when enabled?
> You can disable foreign-key constraints. When you re-enable them, SQL
> Server verifies that the data comply to the constraint.
> You cannot disable primary-key or unique constraints, nor indexes.
> --
> Erland Sommarskog, SQL Server MVP, sommar@.algonet.se
> Books Online for SQL Server SP3 at
> http://www.microsoft.com/sql/techin.../2000/books.asp|||Unfortunately, SQL-Server does not automatically check the existing
table data after turning a constraint back on. IMO it is a Microsoft
mistake to allow a database to get corrupted this way.

Gert-Jan

Erland Sommarskog wrote:
> Rob Panosh (rob_!!!NO!!!SPAM!!!_panosh@.asdsoftadfdware.com) writes:
> > In MSS 2k can Primary Key, Unique Constraints, Indexes and Foreign Keys
> > be disabled? If Indexes (PK's and UN Constraints) can be disabled what
> > happens if data is inserted while disable? Will the index be rebuilt
> > when enabled?
> You can disable foreign-key constraints. When you re-enable them, SQL
> Server verifies that the data comply to the constraint.
> You cannot disable primary-key or unique constraints, nor indexes.
> --
> Erland Sommarskog, SQL Server MVP, sommar@.algonet.se
> Books Online for SQL Server SP3 at
> http://www.microsoft.com/sql/techin.../2000/books.asp|||Gert-Jan Strik (sorry@.toomuchspamalready.nl) writes:
> Unfortunately, SQL-Server does not automatically check the existing
> table data after turning a constraint back on.

You're right, Gert-Jan. Thanks for correcting my mistake.

> IMO it is a Microsoft mistake to allow a database to get corrupted this
> way.

I can see situations where you may want this, but its deceivable that
the constraint is not rechecked. Not only it makes you think that you
have a sound table. If you use the column with a CHECK constraint in a
partitioned view, you will scratch your hair, trying to find out why
SQL Server accesses all tables after this operation.

It is possible to identify this situation though. The example is
augmented script from Books Online:

SET QUOTED_IDENTIFIER OFF
go
CREATE TABLE cnst_example
(id INT NOT NULL,
name VARCHAR(10) NOT NULL,
salary MONEY NOT NULL
CONSTRAINT salary_cap CHECK (salary < 100000)
)
go
-- Valid inserts
INSERT INTO cnst_example VALUES (1,"Joe Brown",65000)
INSERT INTO cnst_example VALUES (2,"Mary Smith",75000)
go
-- This insert violates the constraint.
INSERT INTO cnst_example VALUES (3,"Pat Jones",105000)
go
-- Disable the constraint and try again.
ALTER TABLE cnst_example NOCHECK CONSTRAINT salary_cap
INSERT INTO cnst_example VALUES (3,"Pat Jones",105000)
go
-- Reenable the constraint and try another insert, will fail.
ALTER TABLE cnst_example CHECK CONSTRAINT salary_cap
INSERT INTO cnst_example VALUES (4,"Eric James",110000)
go
-- Returns 1, because constraint has been disabled.
select objectproperty(object_id('salary_cap'), 'CnstIsNotTrusted')

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||"select objectproperty(object_id('salary_cap'), 'CnstIsNotTrusted')"

Interesting, I did not know this yet. Something for a standard script to
check the database...

Gert-Jan

Erland Sommarskog wrote:
> Gert-Jan Strik (sorry@.toomuchspamalready.nl) writes:
> > Unfortunately, SQL-Server does not automatically check the existing
> > table data after turning a constraint back on.
> You're right, Gert-Jan. Thanks for correcting my mistake.
> > IMO it is a Microsoft mistake to allow a database to get corrupted this
> > way.
> I can see situations where you may want this, but its deceivable that
> the constraint is not rechecked. Not only it makes you think that you
> have a sound table. If you use the column with a CHECK constraint in a
> partitioned view, you will scratch your hair, trying to find out why
> SQL Server accesses all tables after this operation.
> It is possible to identify this situation though. The example is
> augmented script from Books Online:
> SET QUOTED_IDENTIFIER OFF
> go
> CREATE TABLE cnst_example
> (id INT NOT NULL,
> name VARCHAR(10) NOT NULL,
> salary MONEY NOT NULL
> CONSTRAINT salary_cap CHECK (salary < 100000)
> )
> go
> -- Valid inserts
> INSERT INTO cnst_example VALUES (1,"Joe Brown",65000)
> INSERT INTO cnst_example VALUES (2,"Mary Smith",75000)
> go
> -- This insert violates the constraint.
> INSERT INTO cnst_example VALUES (3,"Pat Jones",105000)
> go
> -- Disable the constraint and try again.
> ALTER TABLE cnst_example NOCHECK CONSTRAINT salary_cap
> INSERT INTO cnst_example VALUES (3,"Pat Jones",105000)
> go
> -- Reenable the constraint and try another insert, will fail.
> ALTER TABLE cnst_example CHECK CONSTRAINT salary_cap
> INSERT INTO cnst_example VALUES (4,"Eric James",110000)
> go
> -- Returns 1, because constraint has been disabled.
> select objectproperty(object_id('salary_cap'), 'CnstIsNotTrusted')
>
> --
> Erland Sommarskog, SQL Server MVP, sommar@.algonet.se
> Books Online for SQL Server SP3 at
> http://www.microsoft.com/sql/techin.../2000/books.asp