Trying to create a primary key with code

[Paul Rice]

I'm using ODBC and SQL to create a primary key column in a table.
My sql is:

Alter Table Dept Add No INTEGER Not NULL, constraint No_pkey PRIMARY KEY (No)

I got this code from MSDN but it doesn't work. I know that if I try to create a column with just the Not NULL constraint, the column will be created but NULL will be possible. The column needs to be NOT NULL inorder for it to be a primary key. What am I doing wrong?

Thanks,
Paul

[mikeecc]

Hi Paul,

I generated this sample code using Enterprise Manager - the alter table statement is a little different, maybe it will give you a clue.


CREATE TABLE [dbo].[REPORT_ID] (
[QUEUE_ID] [decimal](18, 0) NOT NULL ,
[LOGIN_ID] [varchar] (20) NULL ,
[USER_NAME] [varchar] (40) NULL ,
[TEMPLATE_YN] [decimal](3, 0) NOT NULL
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[REPORT_ID] WITH NOCHECK ADD
CONSTRAINT [PK_REPORT_ID] PRIMARY KEY NONCLUSTERED
(
[QUEUE_ID]
) ON [PRIMARY]
GO



HTH
Mike