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 |
|
mflammia
Starting Member
44 Posts |
 Posted - 2008-06-03 : 06:06:58
|
| Completely new to SQL so wondered if anyone could help with an issue I'm sure there is a simple answer too.Need to create a Primary Key for an existing table. Now I have managed to create a new column called _ID, which set the contents to NULL. So populated the column with the number 1 so that I could set it to no null.Now when I run the query:ALTER TABLE tablename ADD PRIMARY KEY (_ID);I get the error:The CREATE UNIQUE INDEX statement terminated because a duplicate key was found for the object name 'dbo._DATACAPTURE' and the index name 'PK___DATACAPTURE__145C0A3F'. The duplicate key value is (1).Which is obviously due to the repeated 1 in _ID.So I am thinking I just need to run a query that fills the _ID with a unique number.Any ideas how I do that?Thanks for your help in advance. |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2008-06-03 : 06:11:38
|
| Are you using sql 2005? |
 |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2008-06-03 : 06:17:28
|
ALTER TABLE tablename ADD _ID INT IDENTITY PRIMARY KEY ALTER TABLE tablename ADD _ID INT IDENTITY PRIMARY KEY CLUSTERED
E 12°55'05.25"N 56°04'39.16" |
|
|
|
mflammia
Starting Member
44 Posts |
Posted - 2008-06-03 : 06:29:06
|
| Sorry I know this is another obvious question but I am not sure what SQL version I am running as it was installed via another program - how do you tell?Also run the command provided, thanks, but get this error:Column names in each table must be unique. Column name '_ID' in table 'tablename' is specified more than once.Thanks |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2008-06-03 : 06:35:41
|
quote:
Originally posted by mflammia
Sorry I know this is another obvious question but I am not sure what SQL version I am running as it was installed via another program - how do you tell?Also run the command provided, thanks, but get this error:Column names in each table must be unique. Column name '_ID' in table 'tablename' is specified more than once.Thanks
thats because you've added column already. The solution is to drop and create it once againIF EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE COLUMN_NAME = '_ID' AND TABLENAME = 'YourTable')ALTER TABLE tablename DROP COLUMN _IDALTER TABLE tablename ADD _ID INT IDENTITY PRIMARY KEY Also, the version of sql can be checked by running EXEC sp_dbcmptlevel 'yourdbhere' |
|
|
|
mflammia
Starting Member
44 Posts |
Posted - 2008-06-03 : 07:02:02
|
| That worked a treat. Thanks very much.For the records I entered EXEC sp_dbcmptlevel 'yourdbhere'And got....The current compatibility level is 90. |
|
|
|
|
|
|
Creating a Primary Key