Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Adding identity property on primary key column Expand / Collapse
Author
Message
Posted Tuesday, June 23, 2009 5:37 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, July 4, 2014 7:44 AM
Points: 115, Visits: 646
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.




Post #740070
Posted Tuesday, June 23, 2009 8:02 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Today @ 4:01 PM
Points: 1,293, Visits: 1,426
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.

Post #740193
Posted Tuesday, June 23, 2009 8:08 AM
SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Thursday, January 2, 2014 10:30 AM
Points: 646, Visits: 731
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
Post #740200
Posted Tuesday, June 23, 2009 1:00 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, July 4, 2014 7:44 AM
Points: 115, Visits: 646
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.
Post #740479
Posted Wednesday, June 24, 2009 1:28 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Today @ 4:01 PM
Points: 1,293, Visits: 1,426
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;


Post #740816
Posted Tuesday, July 14, 2009 1:30 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, July 14, 2009 1:29 AM
Points: 1, Visits: 1
How to add identity Property to an existing table In sql server 2005
Post #752501
Posted Wednesday, July 15, 2009 10:51 PM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Thursday, May 22, 2014 7:04 AM
Points: 1,688, Visits: 8,766
http://www.mssqltips.com/tip.asp?tip=1397


MJ
Post #753965
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse