[DDLUTILS-153] Changing column data type fails if there were indices referencing the column.

• Type: Bug
• Status: Resolved
• Priority: Major
• Resolution: Fixed
• Fix Version/s: 1.0 RC1
• Component/s: Core - SqlServer
• Environment: SQL Server 2000 DB with mssqlserver-2.2.0040 jdbc driver.
• Assignee: Thomas Dudziak
• Reporter: Jun Li
• Created: 31/Jan/07 06:03
• Updated: 04/Jun/07 02:34
• Resolved: 06/Feb/07 07:20

Description

Creating a database using the following schema:

<?xml version="1.0"?>
<!DOCTYPE database SYSTEM "http://db.apache.org/torque/dtd/database.dtd">
<database name="test">
<table name="person">
<column name="id" primaryKey="true" required="true" type="INTEGER" autoIncrement="true"/>
<column name="organisation_fk" type="NUMERIC" size="8"/>
<index name="IX_Person_Org">
<index-column name="organisation_fk"/>
</index>
<foreign-key foreignTable="organisation">
<reference local="organisation_fk" foreign="id"/>
</foreign-key>
</table>
<table name="organisation">
<column name="id" primaryKey="true" required="true" type="NUMERIC" size="8" autoIncrement="true"/>
<column name="name" type="VARCHAR" size="200"/>
</table>
</database>

*************************************************

And then change the data type of id column of organisation table from 'Numeric' to 'Integer' resulting the following schema:
<?xml version="1.0"?>
<!DOCTYPE database SYSTEM "http://db.apache.org/torque/dtd/database.dtd">
<database name="test">
<table name="person">
<column name="id" primaryKey="true" required="true" type="INTEGER" autoIncrement="true"/>
<column name="organisation_fk" type="INTEGER"/>
<index name="IX_Person_Org">
<index-column name="organisation_fk"/>
</index>
<foreign-key foreignTable="organisation" name="FK_Persion_Org">
<reference local="organisation_fk" foreign="id"/>
</foreign-key>
</table>
<table name="organisation">
<column name="id" primaryKey="true" required="true" type="INTEGER" autoIncrement="true"/>
<column name="name" type="VARCHAR" size="200"/>
</table>
</database>

************************************************

The sql statements generated by using platform.getAlterTablesSql() API are as follows:
IF EXISTS (SELECT 1 FROM sysobjects WHERE type = 'F' AND name = 'FK_Persion_Org')
ALTER TABLE person DROP CONSTRAINT FK_Persion_Org;

ALTER TABLE person
ALTER COLUMN organisation_fk INT;

ALTER TABLE organisation
ALTER COLUMN id INT NOT NULL IDENTITY (1,1) ;

ALTER TABLE person
ADD CONSTRAINT FK_Persion_Org FOREIGN KEY (organisation_fk) REFERENCES organisation (id);

***********************************************

There were two problems with the above statements when trying to run it in the SQL Query Analyzer:
1.
Server: Msg 156, Level 15, State 1, Line 8
Incorrect syntax near the keyword 'IDENTITY'.

2.
Server: Msg 5074, Level 16, State 8, Line 4
The index 'IX_Person_Org' is dependent on column 'organisation_fk'.
Server: Msg 4922, Level 16, State 1, Line 4
ALTER TABLE ALTER COLUMN organisation_fk failed because one or more objects access this column.