Re: The DB created from XML has one more index for every table.

[Jun Li]

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.

Thanks.


--------------
Cheers,
Jun