Pls Help..adding a primary key

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

SQL Server 2000 Forums

SQL Server Development (2000)

Pls Help..adding a primary key

Author

Topic

sreehari
Starting Member

37 Posts

Posted - 2006-07-05 : 01:19:33

Hi all..pls help

GO
ALTER TABLE LKP_STATIC_DATA_DETAIL ADD LIST_NAME NVARCHAR(50),CODE NVARCHAR(80);
GO
ALTER TABLE LKP_STATIC_DATA_DETAIL
ADD CONSTRAINT LKP_STATIC_DATA_DETAIL_PK4
PRIMARY KEY(LIST_NAME, CODE, CONTEXT_CODE, LANGUAGE_CODE);
GO

The above command is giving the following error...

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

Pls give me the stps in detail...

Thanks in advance...

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-07-05 : 02:16:00

for primary key you need to define the Column as NOT NULL

ALTER TABLE LKP_STATIC_DATA_DETAIL 
    ADD LIST_NAME NVARCHAR(50)NOT NULL, 
        CODE NVARCHAR(80) NOT NULL

sreehari
Starting Member

37 Posts

Posted - 2006-07-05 : 03:07:37

Hi when i give the above command...i got the following error...

Server: Msg 4901, Level 16, State 1, Line 1
ALTER TABLE only allows columns to be added that can contain nulls or have a DEFAULT definition specified. Column 'LIST_NAME' cannot be added to table 'LKP_STATIC_DATA_DETAIL' because it does not allow nulls and does not specify a DEFAULT definition.

Pls help

Thanks

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-07-05 : 03:12:37

oh yeah. Forgot about that issue.

You will need to do it in the following steps
1. Add the column without NOT NULL
2. Populate value into the column
3. alter the column to not null
4. create the Primary Key constraint

sreehari
Starting Member

37 Posts

Posted - 2006-07-05 : 03:36:04

Thanks..it is created..

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources