I mistakenly set a default for a uniquely-indexed AUTOINC field, and
discovered my error when trying to insert the second row, which gave a
duplicate key error.

I then tried to redefine the field, but it didn't remove the default.

What is wrong with this SQL code? (a simplified version which
demonstrates the problem)

CREATE Table Test(number1 AUTOINC DEFAULT 1)
;

CREATE UNIQUE INDEX Ix1 ON TEST(number1)
;

ALTER TABLE Test
REDEFINE number1 AUTOINC
;

--
Chris
(XP-Pro + Delphi 7 Architect + DBISAM 4.25 build 4 + EDB 1.04 build 3)

Chris Erdal <chris@No-Spam-erdal.net> wrote in
news:Xns99AA7E34CB4C714torcatis@64.65.248.118:

> I mistakenly set a default for a uniquely-indexed AUTOINC field, and
> discovered my error when trying to insert the second row, which gave a
> duplicate key error.
>
> I then tried to redefine the field, but it didn't remove the default.
>
> What is wrong with this SQL code? (a simplified version which
> demonstrates the problem)

Sorry, folks, I didn't search the newsgroup first.

The answer was supplied long ago by Ole Willy Tuv:

> Default field values are string constants
> ...
> I believe it's rather a bug that DEFAULT 1 was accepted

and more recently by Fred Schetterer:

> Oops, got it.
> After looking at the Delphi AlterTable function I see this is treated
> as a string and '' did the trick.

so the way to do it is:

ALTER TABLE Test
REDEFINE number1 AUTOINC DEFAULT ''
;

--
Chris
(XP-Pro + Delphi 7 Architect + DBISAM 4.25 build 4 + EDB 1.04 build 3)