db-ddlutils-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Thomas Dudziak (JIRA)" <j...@apache.org>
Subject [jira] Commented: (DDLUTILS-8) MsSqlBuilder can't alter a table to make a column autoincremented (identity)
Date Wed, 31 Aug 2005 11:24:04 GMT
    [ http://issues.apache.org/jira/browse/DDLUTILS-8?page=comments#action_12320681 ] 

Thomas Dudziak commented on DDLUTILS-8:
---------------------------------------

Yep, this seems to be the only safe way to do so (SET IDENTITY_INSERT won't really remove
the IDENTITY definition so can't be used to remove the identity). Stupid SQL Server, got no
drop for identity :-)
However, you need to be careful about creating the temporary table: you'll have to check that
there is no such table already present in the database, and you need to move all triggers
to the new table.
I could perhaps make this easier for you by providing an explicit hook for changing the autoincrement
state for columns ?

> 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
>     Assignee: 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