[
https://issues.apache.org/jira/browse/DDLUTILS-153?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Jun Li updated DDLUTILS-153:
----------------------------
Environment: SQL Server 2000 DB with mssqlserver-2.2.0040 jdbc driver.
(was: SQL Server 2000.)
Adding jdbc driver info.
> 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 DB with mssqlserver-2.2.0040 jdbc driver.
> 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.
