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