Subject Re: [firebird-support] create unique index vs alter table add unique()
Author Martijn Tonies
Hello Erik,

> I recently found out something I didn't know, and am curious if it's
> really 'as-designed' behavior. It certainly runs counter to my
> experience with other dbms's.

I'm pretty sure it's the same with other dbms's...

> If it's really as designed, I suggest adding a more explicit note that
> the constraint is NOT created in the documentation for CREATE UNIQUE
> INDEX. I'm using firebird classic on linux.

Well, because you're doing a CREATE UNIQUE INDEX (which, btw,
only accepts unique values, but cannot be used in a FK relation).

> When creating an index using 'CREATE UNIQUE INDEX MYINDEX ON MYTABLE
> (FIELDS)', the index is created, but it apparently doesn't add the
> constraint to the relevant table limiting operations on the table data.
> Is this actually useful for anything?

IMO, constraints are a business-rule thingy, while indices are a
performance thingy.

> I did find that using 'ALTER TABLE MYTABLE ADD UNIQUE(FIELDS)' did what
> I expected, but why aren't the two idioms synonyms?

Because the latter adds a constraint (and automagically creates an index
as well, but that's a different story).

With regards,

Martijn Tonies
Database Workbench - developer tool for InterBase, Firebird, MySQL & MS SQL
Upscene Productions