db-ddlutils-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Christoffer Hammarström (JIRA) <j...@apache.org>
Subject [jira] Created: (DDLUTILS-8) MsSqlBuilder can't alter a table to make a column autoincremented (identity)
Date Wed, 31 Aug 2005 10:30:24 GMT
MsSqlBuilder can't alter a table to make a column autoincremented (identity)
----------------------------------------------------------------------------

         Key: DDLUTILS-8
         URL: http://issues.apache.org/jira/browse/DDLUTILS-8
     Project: DdlUtils
        Type: Bug
 Reporter: Christoffer Hammarström
 Assigned to: Thomas Dudziak 


A column in an existing table can't be made autoincremented using ALTER TABLE.

I want to add this capability to MsSqlBuilder, but i'm not sure whether to copy the approach
of Microsoft Enterprise Manager, or if there is some better way, and i would like some direction
or input.
I've started by extracting methods alterTable() and alterColumns() from alterDatabase() in
SqlBuilder, and i'm overriding alterColumns() in MsSqlBuilder with a check for autoincremented
columns in the desiredTable but not in the currentTable.

The approach used when scripting this change from Microsoft Enterprise Manager is to:
1. Drop table constraints
2. Create a new replacement table with the name prefixed by 'Tmp_'
3. SET IDENTITY_INSERT Tmp_table ON
4. Copy the data from the table to the Tmp_table
5. Set IDENTITY_INSERT Tmp_table OFF
6. DROP TABLE table
7. EXECUTE sp_rename N'dbo.Tmp_table', N'table', 'OBJECT'
8. Readd table constraints

An example follows:

BEGIN TRANSACTION
SET QUOTED_IDENTIFIER ON
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
SET ARITHABORT ON
SET NUMERIC_ROUNDABORT OFF
SET CONCAT_NULL_YIELDS_NULL ON
SET ANSI_NULLS ON
SET ANSI_PADDING ON
SET ANSI_WARNINGS ON
COMMIT
BEGIN TRANSACTION
ALTER TABLE dbo.phones
    DROP CONSTRAINT FK_phones_users
GO
COMMIT
BEGIN TRANSACTION
ALTER TABLE dbo.phones
    DROP CONSTRAINT DF_phones_phonetype_id
GO
CREATE TABLE dbo.Tmp_phones
(
    phone_id int NOT NULL IDENTITY (1, 1),
    number varchar(25) NOT NULL,
    user_id int NOT NULL,
    phonetype_id int NOT NULL
)  ON [PRIMARY]
GO
ALTER TABLE dbo.Tmp_phones ADD CONSTRAINT
    DF_phones_phonetype_id DEFAULT (0) FOR phonetype_id
GO
SET IDENTITY_INSERT dbo.Tmp_phones ON
GO
IF EXISTS(SELECT * FROM dbo.phones)
    EXEC('INSERT INTO dbo.Tmp_phones (phone_id, number, user_id, phonetype_id)
SELECT phone_id, number, user_id, phonetype_id FROM dbo.phones TABLOCKX')
GO
SET IDENTITY_INSERT dbo.Tmp_phones OFF
GO
DROP TABLE dbo.phones
GO
EXECUTE sp_rename N'dbo.Tmp_phones', N'phones', 'OBJECT'
GO
ALTER TABLE dbo.phones ADD CONSTRAINT
PK_phones PRIMARY KEY NONCLUSTERED
(
    phone_id,
    user_id
) ON [PRIMARY]
GO
ALTER TABLE dbo.phones WITH NOCHECK ADD CONSTRAINT
FK_phones_users FOREIGN KEY
(
    user_id
) REFERENCES dbo.users
(
    user_id
)
GO
COMMIT


-- 
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators:
   http://issues.apache.org/jira/secure/Administrators.jspa
-
For more information on JIRA, see:
   http://www.atlassian.com/software/jira


Mime
View raw message