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] Created: (DDLUTILS-153) Changing column data type fails if there were indices referencing the column.
Date Wed, 31 Jan 2007 06:05:05 GMT
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.


Mime
View raw message