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
