db-ddlutils-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Jun Li (JIRA)" <j...@apache.org>
Subject [jira] Updated: (DDLUTILS-153) Changing column data type fails if there were indices referencing the column.
Date Wed, 31 Jan 2007 06:10:05 GMT

     [ 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.


Mime
View raw message