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.
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 ...'? |
|
|
pir8d
Starting Member
10 Posts |
Posted - 2007-07-11 : 23:00:32
|
Hi rmiao,Thanks for the advice.I tried:[CODE]ALTER TABLE Table1ALTER 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! |
|
|
|
|
|