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 |
|
sqllearner
Aged Yak Warrior
639 Posts |
 Posted - 2004-11-04 : 15:33:44
|
| I have a primary key in a table called tbl_emp_detail where emp_id is a primary key with identity(1,1).Now I want to remove the primary key constraint and add 2 columns emp_age and emp_address to the table.how should i write the script |
|
|
rockmoose
SQL Natt Alfen
3279 Posts |
Posted - 2004-11-04 : 15:42:39
|
see ALTER TABLE in BOL  Basic idea:ALTER TABLE ... DROP CONSTRAINT ..PKconstr..ALTER TABLE ADD colname datatype not null / nullIf you add columns in TSQL they will end up last in the table ( not that order is important )If you use EM you can add columns in the position you want in the table.Another approach would be to create a new table, copy the rows into the new table, drop the old table, rename the new table.( this is more or less what EM does )And furthermore here is a link about altering tables:[url]http://www.nigelrivett.net/AlterTableProblems.html[/url]rockmoose |
 |
|
|
X002548
Not Just a Number
15586 Posts |
Posted - 2004-11-04 : 17:04:38
|
| Yeah, read Nigel's link...Another neat thing to check out is how enterprise manager does it...Which is to create a new table...Brett8-) |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2004-11-05 : 01:49:34
|
"Which is to create a new table..."Not for this job it won't - you ahve to insert s new column in the middle of the list, of modify existing columns considerably before it does that. Bung some new columns on the end and you'll just get an ALTER TABLE MyTable ADD MyColumn.This from Northwind:ALTER TABLE dbo.[Order Details] ADD FooBar char(10) NULLGOALTER TABLE dbo.[Order Details] DROP CONSTRAINT PK_Order_DetailsGO Kristen |
|
|
|
|
|
|
primary key issue