db-ddlutils-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Thomas Dudziak" <tom...@gmail.com>
Subject Re: The DB created from XML has one more index for every table.
Date Sun, 09 Apr 2006 20:36:25 GMT
On 3/29/06, Jun Li <allanjunli@gmail.com> wrote:
> OK, I know the reason now.
>
> For a schema (generated by DdlUtils from a live database) as following:
>
>     <table name="Accrual">
>       <column name="AccrualID" primaryKey="true" required="true"
> type="NUMERIC" size="8" autoIncrement="true"/>
>       <column name="PeriodFK" primaryKey="false" required="true"
> type="NUMERIC" size="8" autoIncrement="false"/>
>       <column name="FileNameChanged" primaryKey="false" required="true"
> type="VARCHAR" size="80" autoIncrement="false"/>
>       <column name="AccruedOn" primaryKey="false" required="false"
> type="TIMESTAMP" size="23" autoIncrement="false"/>
>       <unique name="PK_Accrual">
>         <unique-column name="AccrualID"/>
>       </unique>
>       <index name="IX_Accrual_Period">
>         <index-column name="PeriodFK"/>
>       </index>
>     </table>
>
> DdlUtils generates sql statements :
>
> CREATE TABLE "Accrual"
> (
>     "AccrualID" NUMERIC(8,0) NOT NULL IDENTITY (1,1) ,
>     "PeriodFK" NUMERIC(8,0) NOT NULL,
>     "FileNameChanged" VARCHAR(80) NOT NULL,
>     "AccruedOn" DATETIME,
>     PRIMARY KEY ("AccrualID")
> )
>
> And
>
> CREATE UNIQUE INDEX "PK_Accrual" ON "Accrual" ("AccrualID")
>
> CREATE INDEX "IX_Accrual_Period" ON "Accrual" ("PeriodFK")
> OK. It looks all right so far.
> But on SQL Server 2000, with the  PRIMARY KEY ("AccrualID")
> in the create table statement, an index called PK__Accrual__XXXXXX will
> generated automatically on the table, here is where the extra index come
> from.
>
> However, if I don't include primary key in the create table statement but
> use
>
> alter table Accrual
>  add constraint PK_Accrual primary key
>   (
>     AccrualID
>   )
>
> later to define primary key, the index is called PK_Accrual which is what it
> should be.
>
> I'm not sure is this something that I can config on the SQL Server or
> DdlUtils should take care of this.

I've enhanced the filtering for sql server a bit so that such unique
indices are no longer returned. However, this will be more easily done
once named primary keys are in place (after the 1.0).

Tom

Mime
View raw message