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
 Transact-SQL (2000)
 Problem trying to make a column a Primary Key

Author  Topic 

pir8d
Starting Member

10 Posts

Posted - 2007-07-11 : 22:05:53
Hi guys, I'm hoping someone here will have some kind of experience with this.

I currently have a table with no primary keys ( I know, but i didn't create it...). So now I want to make a column a primary key.

At the moment it is something like this:

CREATE TABLE dbo.Table1
(
column1 char(10) NULL,
column2 char(10) NULL,
column3 char(10) NULL
) ON [PRIMARY]

The end result is that I want it to be something like this:

CREATE TABLE dbo.Table1
(
column1 char(10) PRIMARY KEY NOT NULL,
column2 char(10) NULL,
column3 char(10) NULL
) ON [PRIMARY

Because these are existing tables with lots of data, I need to alter the table properties like so:

ALTER TABLE Table1
ADD CONSTRAINT PK_Table1
PRIMARY KEY (column1)

But this code only works for columns that are set to NOT NULL. Currently the column that I want to set as the Primary Key is set to allow NULLS (I have checked the table and no records are actually NULLS).

Is there any SQL script that can change a column's properties from NULL to NOT NULL? I need to do this as a SQL script as opposed to changing it from the Design Table interface as this is a production database and the changes need to be scheduled to occur overnight.

rmiao
Master Smack Fu Yak Hacker

7266 Posts

Posted - 2007-07-11 : 22:43:46
Tried 'alter table ... alter column ...'?
Go to Top of Page

pir8d
Starting Member

10 Posts

Posted - 2007-07-11 : 23:00:32
Hi rmiao,

Thanks for the advice.

I tried:

[CODE]ALTER TABLE Table1
ALTER COLUMN column1 char(10) NOT NULL[/CODE]


And it worked!

Previously I tried to do this, but it didn't work. It was because I didn't nominate the column datatype. Thanks!
Go to Top of Page
   

- Advertisement -