How to add a NOT NULL constraint without check

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

Old Forums

CLOSED - General SQL Server

How to add a NOT NULL constraint without check

Author

Topic

yeek
Starting Member

6 Posts

Posted - 2005-06-03 : 15:15:19

I have a table with million of records, one column suppose to be NOT NULL, but we don't have a NOT NULL CONSTRAINT on it yet. I know the existing data are all NOT NULL. Is there a way I can add a NOT NULL constraint without checking existing data?

When I use

ALTER TABLE <TableName>
ALTER COLUMN <ColumnName> <DataType> NOT NULL

It is extreamly slow.

Any suggestion will be appreciated.

Thanks

X002548
Not Just a Number

15586 Posts

Posted - 2005-06-03 : 15:22:32

How did you check that the Column has no nulls

SELECT COUNT(*) FROM myTable99 WHERE Col IS NOT NULL?

Also. it's slow because of the logging.

Also, nr (Nigel) had a great article about ALTER Causing a lot of wasted space. Don't know if that would apply with an Alter or not. Regardless, I would do:

1. Create a table with structure you want
2. bcp out all the data
3. bcp the data in to the new object
4. rename the original table to soemthing else
5. change the new table to the old name

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

yeek
Starting Member

6 Posts

Posted - 2005-06-03 : 15:31:14

Thanks for the reply.

I used following query to check the existance.
SELECT TOP 1 *
FROM <TableName> WITH(NOLOCK)
WHERE ColName IS NULL

Is there a solution that I can do without interrupt others.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2005-06-03 : 15:32:30

quote:
Originally posted by yeek

Is there a solution that I can do without interrupt others.

No. Whatever solution you go with, it'll impact your users, so this should be done during a maintenance window.

Tara

yeek
Starting Member

6 Posts

Posted - 2005-06-03 : 15:37:27

I know I can add a trigger to validate future insert and update on this column to insure NOT NULL, without interrupt others.

But that is not a good choice, is it?

X002548
Not Just a Number

15586 Posts

Posted - 2005-06-03 : 15:38:13

How long does it take to rename a table? Microseconds?

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

X002548
Not Just a Number

15586 Posts

Posted - 2005-06-03 : 15:45:40

quote:
Originally posted by yeek

I know I can add a trigger to validate future insert and update on this column to insure NOT NULL, without interrupt others.

But that is not a good choice, is it?

Depends....You'll incur more overhead.

Why is the solution I gave you not a good idea?

EDIT: And the funny thing, is that the update to millions of rows is Already causing more locking and logging and probably giving your users more grief than what I suggested.

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

TimS
Posting Yak Master

198 Posts

Posted - 2005-06-03 : 16:10:01

ALTER TABLE doc_exd WITH NOCHECK
ADD CONSTRAINT exd_check CHECK (column_a IS NOT NULL)

Any reason the above won't work.

Tim S

yeek
Starting Member

6 Posts

Posted - 2005-06-03 : 21:28:23

Thanks TimS, I will try see if NOCHECK can be used.

yeek
Starting Member

6 Posts

Posted - 2005-06-03 : 21:32:59

Thanks Brett, you solution is good, but I have about 5 tables referencing this one, and the 5 children tables also have referencing tables.

yeek
Starting Member

6 Posts

Posted - 2005-06-04 : 15:16:02

It works great. Thanks

quote:
Originally posted by TimS

ALTER TABLE doc_exd WITH NOCHECK
ADD CONSTRAINT exd_check CHECK (column_a IS NOT NULL)

Any reason the above won't work.

Tim S

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources