Re: Discovery of unique constraints using Catalog Methods

From: Hildo Biersma (Hildo.Biersma_at_morganstanley.com)
Date: 02/14/05 

Date: Mon, 14 Feb 2005 14:46:00 -0500
To: Darren Duncan <darren@DarrenDuncan.net>

Darren Duncan wrote:
> >
> > I don't have a lot of experience with databases other than Oracle
> >and a little PostgreSQL, but I'm under the impression that indexing
> >behavior in general is not very standardized, so not a good
> >candidate for DBI.
>
> I think that MySQL works the same way. You create a unique or
> primary key constraint and that doubles as an index. You can also
> create a non-constraining index. The syntax for either is almost
> identical.
>
> I suspect it makes sense for any unique constraints in any database
> products to have implied indexes, since an index is required to
> enforce a constraint in any reasonable amount of time.

... but note that IBM DB2 allows you to create indexes that are part
UNIQUE, and part just carrying data along (the INCLUDES syntax). It
uses multiple catalog tables to record the unique constraint, the
columns involved, the column order, and whether the columns are unique
or not.

I agree this isn't a good candidate for DBI - the database functionality
and implementation (plus the catalog tables) are just too different.

Relevant Pages

  • AW: Root Space Full
    ... My suspician is that the database 'prod_db' was created in ... Then recreate the unique constraints. ... in a dbspace which is not the rootdbs. ...
    (comp.databases.informix)
  • Re: Database code or application code.
    ... some of these point to unique constraints. ... Don't get me wrong I prefer business rule validation in the business layer, ... ADD CONSTRAINT Event_EventName_UNIQUE UNIQUE ... the database would have to be scanned for all ...
    (microsoft.public.dotnet.framework.aspnet)
  • Re: Avoid inserting a duplicate record on page submit?
    ... It's a common knowledge to add a primary key in your database table; ... chance perhaps even unique constraints for certain non-key columns. ...
    (microsoft.public.dotnet.languages.csharp)