db-ddlutils-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Paul Balomiri" <paulbalom...@gmail.com>
Subject Mysql PK Problems on table creation
Date Mon, 05 Mar 2007 15:54:39 GMT
Hi,

I encountered a problem using ddlutils with mysql.
Starting point was an export done from a life table created with this Script:
------------------------------------
CREATE TABLE A (a INTEGER(11) , b1 VARCHAR(255), b2 VARCHAR(255),
PRIMARY KEY (a) ) ;

-------------------------------------

A flat xml export from the life database yielded this xml output for table A

-----------------------------------------
    <table name="A">
      <column name="a" primaryKey="true" required="true"
type="INTEGER" size="11" default="0" autoIncrement="false"/>
      <column name="b1" primaryKey="false" required="false"
type="VARCHAR" size="255" autoIncrement="false"/>
      <column name="b2" primaryKey="false" required="false"
type="VARCHAR" size="255" autoIncrement="false"/>
      <index name="PRIMARY">
        <index-column name="a"/>
      </index>
    </table>
----------------------------------------------

The SQL generated by the SqlBuilder class contained an extra line
CREATE INDEX PRIMARY on A(a); which was rejected by the Mysql Engine
because PKs cannot be created using CREATE statements since mysql 4.1
(see http://bugs.mysql.com/bug.php?id=6062 ).

thus the problem is twohand:

1) When creating a Table the and using PRIMARY KEY(a) within the
CREATE TABLE statement,
the external statement CREATE INDEX PRIMARY... is invalid.

2) If a primary Index is to be added after table creation, one cannot
use the CREATE INDEX PRIMARY ... statement. One must instead use the
form ALTER TABLE  A ADD PRIMARY   KEY (a). PRIMARY, on the other hand
is not a valid INDEX name, thus it is true that all indexes called
PRIMARY must be PKs. There seems to be a difference in mysql between
PK and indexes in general.
Concluding from 2 I think it is safe to assume that all indexes called
'PRIMARY' from DdlUtils are in fact Primary Keys. Based on this
assumption an overloaded version of
writeExternalIndicesCreateStmt  replaces the SQL output for indexes
called 'PRIMARY' with the Alter Table form.

Even using this form the index creation is not valid, as the key is
duplicate. So I also overloaded the createTable statement, to call a
version of writeExternalIndicesCreateStmt which omits the Indexes
called 'Index'

The attached diff to MySqlBuilder.java (rev. 514702) attempts to
solve these problems.


-- 
paulbalomiri@gmail.com

Mime
  • Unnamed multipart/mixed (inline, None, 0 bytes)
View raw message