Adding a primary key 1

[foreveryoung]

I want to add a column to a table and make that the primary key. I am using the following

alter table KH06AdmissionsTBL add column rowid int primary key

what am i doing wrong it said no nulls allowed but if i replace int to autonumber it still wont work.

Any DDL experts out there?

Many thanks
David

 

[Norris68]

alter table KH06AdmissionsTBL add column rowid int not null primary key

 

[foreveryoung]

Sorry I failed to mention that this new column has to be an autonumber field. This does not work

alter table KH06AdmissionsTBL add column rowid autonumber not null primary key

 

[Norris68]

alter table KH06AdmissionsTBL add column rowid identity(1,1) not null primary key

Sorry, I didn't check out the data types - you need an INDENTITY(Seed,Increment). You can find these listed in the help: Contents -> Jet SQL Reference -> Overview -> SQL Data Types. It gives an example of a CREATE TABLE statement using an autonumber (identity) field, although it uses a CONSTRAINT to enforce the PK.

 

[foreveryoung]

Thanks for you help. I got an error message pointing to the increment word. I have however managed to get this to work.

alter table KH06AdmissionsTBL add column rowid autoincrement not null primary key