Changing column data type fails if there were indices referencing the column.
-----------------------------------------------------------------------------
Key: DDLUTILS-153
URL: https://issues.apache.org/jira/browse/DDLUTILS-153
Project: DdlUtils
Issue Type: Bug
Components: Core - SqlServer
Environment: SQL Server 2000.
Reporter: Jun Li
Assigned To: Thomas Dudziak
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.
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
