Re: 8 and 8i default constraint behavior

"Galen Boyer" <galenboyer_at_yahoo.com> wrote in message news:ur8xzseav.fsf_at_primix.com...
> On Wed, 9 May 2001, howardjr_at_www.com wrote:
>
> > Possibly. My real problem is with unique indexes used to
> > enforce primary and unique key constraints taking it upon
> > themselves to be dropped whenever I disable a constraint.
>
> How would a primary constraint be disabled if the unique index
> weren't dropped?
>

It probably wouldn't. I guess that's why enforcement by non-unique indexes was invented, precisely so you *could* disable the constraint if you wanted.

On the other hand, here's a test I've never performed.... It is also true that indexes you create yourself, manually, on a column do not get dropped if a constraint that uses that index is disabled. So: create a table without a primary key, and 'create unique index blah on.... etc etc'. Now alter table and add he primary key 'using index blah'. So now you have a primary key constraint using a manually created unique index. I wonder whether you can disable that primary key constraint, since the index won't be dropped?

I'll have a bash on the system tonight and see what I come up with (unless you'd care to beat me to it??!)

> > As a DBA I'd prefer to be in charge of what gets dropped and
> > when, thanks very much. The point being that primary and
> > unique keys that are created deferrable get NON-unique indexes
> > to enforce them,
>
> So these non-unique indexes get used by the primary constraint to
> quickly figure out uniqueness but they don't actually enforce
> uniqueness?
>

I guess that's one way of putting it. The table polices itself, and hence the index becomes "unique" by default (in the sense that duplicating entries get booted by the table, and hence never get into the index).

I've never quite worked out how uniqueness is enforced without a unique index, and maybe Jonathan can explain for us the precise internals. I just know it happens (and allegedly, according to Steve Adams, there is no noticeable performance hit in doing so -otherwise I wouldn't recommend it).

Regards
HJR
> --
> Galen
> I don't want to be the rock. Yeah, okay, what do you want to be?
> I want to be the piece of glass.
Received on Wed May 09 2001 - 01:35:31 CDT