Re: Time elapsed error - SQL Server Forums

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

General SQL Server Forums

New to SQL Server Programming

Re: Time elapsed error

Author

Topic

dr223
Constraint Violating Yak Guru

444 Posts

Posted - 2009-06-26 : 11:19:11

Hallo,

I have a table which is builtup of 100,000,000 records and I am trying to set a column within the table as primary key, using the following code;

Alter table dbo.o_pat
ADD PRIMARY KEY (o_pat_uid)

This fails reason being the field is set to allow nulls. In the design mode, am trying to uncheck the box not to allow nulls, but it times out when I try to save.

Tried right click and set to Primary key - it times out too, when trying to save.

I must set this key to primary key ..

Please could anyone tell me how I can go around this .

Thanks

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2009-06-26 : 11:58:38

fill in the nulls with a default value. Then try again.

[Signature]For fast help, follow this link:
http://weblogs.sqlteam.com/brettk/archive/2005/05/25.aspx
Learn SQL or How to sell Used Cars
For ultra basic questions, follow these links.
http://www.sql-tutorial.net/
http://www.firstsql.com/tutor.htm
http://www.w3schools.com/sql/default.asp

dr223
Constraint Violating Yak Guru

444 Posts

Posted - 2009-06-26 : 12:20:19

it is 100,000,000 table it will be so complex to fill all nulls

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2009-06-26 : 12:31:31

um...

If o_pat is your primary key candidate...


UPDATE 
    MassiveTable
SET 
    o_pat = 'whatever_default_value'
WHERE 
    o-pat IS NULL

It may take a while, but it will be worth it so you can add a primary key, and clustered indexes and the like...

If there are MANY rows , try adding a TOP 10000 clause and do it in batches..

[Signature]For fast help, follow this link:
http://weblogs.sqlteam.com/brettk/archive/2005/05/25.aspx
Learn SQL or How to sell Used Cars
For ultra basic questions, follow these links.
http://www.sql-tutorial.net/
http://www.firstsql.com/tutor.htm
http://www.w3schools.com/sql/default.asp

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-06-26 : 13:42:54

quote:
Originally posted by dr223

Hallo,

I have a table which is builtup of 100,000,000 records and I am trying to set a column within the table as primary key, using the following code;

Alter table dbo.o_pat
ADD PRIMARY KEY (o_pat_uid)

This fails reason being the field is set to allow nulls. In the design mode, am trying to uncheck the box not to allow nulls, but it times out when I try to save.

Tried right click and set to Primary key - it times out too, when trying to save.

I must set this key to primary key ..

Please could anyone tell me how I can go around this .

Thanks

are you sure all the values in column are unique? it might be better for you to add another identity column and make it primary key

dr223
Constraint Violating Yak Guru

444 Posts

Posted - 2009-06-29 : 06:31:07

Hallo,

When I try to add a new column and set it to Primary key the following error is highlighted as shown below. Any way I can afford this error? Thanks

'o_pat' table
- Unable to modify table.
ALTER TABLE only allows columns to be added that can contain nulls, or have a DEFAULT definition specified, or the column being added is an identity or timestamp column, or alternatively if none of the previous conditions are satisfied the table must be empty to allow addition of this column. Column 'o_pat_uni_id' cannot be added to non-empty table 'o_pat' because it does not satisfy these conditions.

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2009-06-29 : 09:51:58

So, the error tells you how to fix it.

"ALTER TABLE only allows columns to be added that can contain nulls, or have a DEFAULT definition specified, or the column being added is an identity or timestamp column, or alternatively if none of the previous conditions are satisfied the table must be empty to allow addition of this column. "

Add your new column as an IDENTITY column.

[Signature]For fast help, follow this link:
http://weblogs.sqlteam.com/brettk/archive/2005/05/25.aspx
Learn SQL or How to sell Used Cars
For ultra basic questions, follow these links.
http://www.sql-tutorial.net/
http://www.firstsql.com/tutor.htm
http://www.w3schools.com/sql/default.asp

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-06-29 : 11:46:23

quote:
Originally posted by dr223

Hallo,

When I try to add a new column and set it to Primary key the following error is highlighted as shown below. Any way I can afford this error? Thanks

'o_pat' table
- Unable to modify table.
ALTER TABLE only allows columns to be added that can contain nulls, or have a DEFAULT definition specified, or the column being added is an identity or timestamp column, or alternatively if none of the previous conditions are satisfied the table must be empty to allow addition of this column. Column 'o_pat_uni_id' cannot be added to non-empty table 'o_pat' because it does not satisfy these conditions.

the error message clearly suggests you make it identity, just as i told you earlier

dr223
Constraint Violating Yak Guru

444 Posts

Posted - 2009-06-30 : 05:32:46

Please let me know how to do that? Thanks

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-06-30 : 13:02:46

ALTER TABLE yourTable ADD NewColumnName int IDENTITY(1,1) NOT NULL CONSTRAINT column_pk PRIMARY KEY,

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources