On 3/29/06, Jun Li <[EMAIL PROTECTED]> 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
