Adding a PRIMARY KEY

simimathew · New User Joined: 26 Jan 2006 Posts: 21 Location: London

Hi,

Suppose I have a table SAMPLE with a nullable column SNO CHAR(5). I have inserted a couple of records to the table (but no null values are inserted). Now if I have to make SNO the primary key of the table, I have to make the column not nullable. Could you please share the corresponding ALTER TABLE ALTER COLUMN syntax?

Many Thanks,
Simi

murmohk1 · Posted: Wed Jul 09, 2008 3:41 pm Post subject:

Simi,

You can use the following ddl to create primary key for the table -

simimathew · New User Joined: 26 Jan 2006 Posts: 21 Location: London

Hi Murali,

Thanks for the quick reply.

But I was asking for the syntax to make an already existing nullable column to not nullable. My problem is I have a nullable column (though the values are all NOT NULL) and I would like to make that column my primary key. When i try adding the primary key, it gives me an error saying that the column can contain null values.

Thanks again,
Simi

ashimer · Posted: Wed Jul 09, 2008 4:27 pm Post subject:

If you see the ALTER stmt syntax there is no provision for changing a column from NULL to NOT NULL ... at the max wht u will be able to do is add a check constraint on the col for NOT NULL ..but in this case you cannot make this col as your primary key ...

I am not sure whether any other possibilities are there

ashimer · Posted: Wed Jul 09, 2008 4:42 pm Post subject:

from IBM site ...

The ALTER TABLE statements that you cannot use are those that require scanning of column data to verify the validity of the alteration operations. Specifically, this means that you cannot execute the following statements on a table:

ADD UNIQUE CONSTRAINT
ADD CHECK CONSTRAINT
ADD REFERENTIAL CONSTRAINT
ALTER COLUMN SET NOT NULL
ALTER TABLE ADD REFERENTIAL CONSTRAINT
ALTER TABLE ADD CONSTRAINT
ALTER TABLE ADD UNIQUE CONSTRAINT

simimathew · New User Joined: 26 Jan 2006 Posts: 21 Location: London

Thanks very much Ashimer