adding primary key is not working.

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

adding primary key is not working.

Author

Topic

tvspsekhar
Starting Member

20 Posts

Posted - 2010-03-08 : 06:06:18

Please help me in correcting the following query. ( Even though no null data is available in locno column, it is not being executed.

alter table towersch add primary key(locno);

error message:
Msg 8111, Level 16, State 1, Line 1
Cannot define PRIMARY KEY constraint on nullable column in table 'towersch'.
Msg 1750, Level 16, State 0, Line 1
Could not create constraint. See previous errors.

locno twrtypw span fdn count earth
1/0 DA+0 325.00 DFR 1 NULL
1/1 DA+0 276.00 DRY 1 NULL
1/2 DA+3 346.00 DFR 1 NULL
1/3 DA+6 222.00 DFR 1 NULL
1/4 DA+0 256.00 DFR 1 NULL
2/0 DC+0 333.00 DFR 1 NULL
2/1 DA+9 290.00 DRY 1 NULL
2/2 DA+0 329.00 PS 1 NULL
2/3 DA+3 290.00 PS 1 NULL
3/0 DB+3 333.00 WFR 1 NULL
3/1 DA+3 239.00 WFR 1 NULL
3/2 DA+6 328.00 SFR 1 NULL
4/0 DC+9 289.00 WET 1 NULL
4/1 DA+0 324.00 WET 1 NULL
4/2 DA+9 389.00 WET 1 NULL
5/0 DB+6 400.00 WET 1 NULL
5/1 DA+6 375.00 DFR 1 NULL
5/2 DA+0 267.00 WET 1 NULL
6/0 DB+3 300.00 WET 1 NULL

tvspsekhar

AndrewMurphy
Master Smack Fu Yak Hacker

2916 Posts

Posted - 2010-03-08 : 06:20:56

the problem is not that there are currently no null data items
the column "as defined" allows nulls in future.

fix this and then your primary key can be added.

raky
Aged Yak Warrior

767 Posts

Posted - 2010-03-08 : 07:33:49

try this

alter table towersch alter column locno <dataType> not null

go

alter table towersch add primary key(locno)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-03-08 : 09:50:30

quote:
Originally posted by raky

try this

alter table towersch alter column locno <dataType> not null

go

alter table towersch add primary key(locno)

where did you get latter syntax from? It wont work in SQL Server

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2010-03-08 : 13:19:45

quote:
Originally posted by visakh16

quote:
Originally posted by raky

try this

alter table towersch alter column locno <dataType> not null

go

alter table towersch add primary key(locno)

where did you get latter syntax from? It wont work in SQL Server

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

It'll work in 2005 and 2008. Not sure about previous versions of SQL.

But, I'd suggest naming the constraint:

ALTER TABLE <table name>
	ADD CONSTRAINT <constriant name> PRIMARY KEY  CLUSTERED (<column(s)>)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources