Subject Re: [ib-support] primary key not enforced after recreating the table
Author David Garamond
Paul Reeves wrote:
>>yes, that _is_ my problem. why doesn't it fail after the table is
>>dropped and recreated??? i am deliberately inserting the value "1" so
>>the second insert would be rejected by the unique index. but after
>>DROP+CREATE, i can insert duplicate rows.
> You might try committing after the drop and the create.

thanks, paul. i originally did a DROP+CREATE+COMMIT. it turns out that i
need a DROP+COMMIT+CREATE+COMMIT to get things right.

> Is this a bug or is it 'as-designed' (or both)? I don't know without
> investigating things further.

i just tried this with on windows and the same thing happened.
however, 1.5a5 doesn't behave the same way. my DROP+CREATE+COMMIT works
ok. i personally prefer the 1.5a5 behaviour.

> However, the usual practice with DDL
> statements is to commit after each one is executed.

thanks a lot for pointing this out. i am seeing this will be a trap for
many beginners. just a suggestion, if weird things happen anyway when
two/more DDL statements are combined, perhaps all DDL's should be made
autocommitting [by default]?