[ 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
