SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Adding identity property on primary key column


Adding identity property on primary key column

Author
Message
brainy
brainy
SSChasing Mays
SSChasing Mays (627 reputation)SSChasing Mays (627 reputation)SSChasing Mays (627 reputation)SSChasing Mays (627 reputation)SSChasing Mays (627 reputation)SSChasing Mays (627 reputation)SSChasing Mays (627 reputation)SSChasing Mays (627 reputation)

Group: General Forum Members
Points: 627 Visits: 649
How can we add identity property on existing table on primary key column.
The table is having data.

I am thinking to add a temp column move all data from primary key column to the temp column and then dropping all related fks depending on this pk.

1. Then dropping the pk
2. adding identity property
3. then moving data back from the temp column,
4. creating pk

Is it a good practice or I need to use temp table instead of temp column.
Tom Brown
Tom  Brown
Hall of Fame
Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)

Group: General Forum Members
Points: 3456 Visits: 1491
I think you need to clarify this a little.

Post the existing table def, and what you want the table def to be after the alteration.

If lots of foreign keys link to that table - its going to be messy

But whatever the eventual method take backups, test it out in a safe non-production environment until its working, and it may be advisable to do the production run in downtime or single user mode in case of locking/changes during your change.
MannySingh
MannySingh
Hall of Fame
Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)

Group: General Forum Members
Points: 3147 Visits: 787
you should be able to create a new column and make it identity and primary key:
Question: will this default your existing primary key column and uniqueness..

ALTER TABLE ABC
ADD NEWCOL int IDENTITY(1,1) PRIMARY KEY

Maninder
www.dbanation.com
brainy
brainy
SSChasing Mays
SSChasing Mays (627 reputation)SSChasing Mays (627 reputation)SSChasing Mays (627 reputation)SSChasing Mays (627 reputation)SSChasing Mays (627 reputation)SSChasing Mays (627 reputation)SSChasing Mays (627 reputation)SSChasing Mays (627 reputation)

Group: General Forum Members
Points: 627 Visits: 649
I will put table definition you will get clear picture..

I have a table : test with column A, B and C with datatype int

test(A int, B int, C int)........ here A is primary key.

We have some data in this table. Now I want to make A as identity.
Tom Brown
Tom  Brown
Hall of Fame
Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)

Group: General Forum Members
Points: 3456 Visits: 1491
OK That makes it a little easier than I first thought. I prefer the temp table solution.
I've run this test example, and it seems to work, you should be able to adapt this to your situation.
However, if your existing primary key is referenced elsewhere as a foreign key it will be more complicated - Let me know if this is the case.

USE [TempDB];
GO
CREATE TABLE [dbo].[Test](
[Code] int NOT NULL,
[Data] [varchar](100) NULL,
CONSTRAINT PK_Test
PRIMARY KEY CLUSTERED
(
[Code] ASC
)
) ON [PRIMARY];
GO

INSERT INTO dbo.Test
SELECT 1, 'AAAAAA' UNION ALL
SELECT 2, 'BBBBB' UNION ALL
SELECT 3, 'CCCCC';

BEGIN TRANSACTION

CREATE TABLE dbo.TempTest
(
CODE INT IDENTITY(1,1) PRIMARY KEY,
Data VARCHAR(100) NULL
) ON [PRIMARY];
GO
SET IDENTITY_INSERT dbo.TempTest ON;
-- get exclusive table lock on dbo.Test
INSERT INTO dbo.TempTest (Code, Data)
SELECT Code, Data FROM dbo.Test WITH (TABLOCKX);
GO
SET IDENTITY_INSERT dbo.TempTest OFF;
DROP TABLE dbo.Test;

EXECUTE sp_rename N'dbo.TempTest', N'Test', 'OBJECT' ;

ROLLBACK
-- COMMIT
DROP TABLE dbo.Test;



ellavarasanbalu
ellavarasanbalu
Grasshopper
Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)

Group: General Forum Members
Points: 13 Visits: 1
How to add identity Property to an existing table In sql server 2005
MANU-J.
MANU-J.
SSCertifiable
SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)

Group: General Forum Members
Points: 7614 Visits: 8766
http://www.mssqltips.com/tip.asp?tip=1397


MJ
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search