Oracle FAQ Your Portal to the Oracle Knowledge Grid

Home -> Community -> Mailing Lists -> Oracle-L -> Re: Check constraint

Re: Check constraint

From: Jared Still <>
Date: Wed, 27 Oct 2004 14:15:20 +0100
Message-ID: <>

You might consider using a separate table for this.

Store the PK from the source table, and the column(s) (name?) upon which the CURRENT uniqueness should be employed, along with a unique constraint to enforce it.

It really sounds as if this data model could be optimized, but not knowing the rest of the associated data, it's difficult to say just how it might be changed.

If this is a third party app, then you can probably disregard all of this.


On Wed, 27 Oct 2004 06:44:03 -0600, William B Ferguson <> wrote:
> This has probably been hashed numerous times already, but I can't find =
> it.
> I have a table (names) with the following structure:
> dep_id number(12) -- FK to main table
> line number(4) -- PK when combined with dep_id
> name varchar2(70)
> status varchar2(8) - can be either Current or Previous
> ...and other auditing fields.
> My question is, I need/want to have a check constraint (somehow), so =
> that
> for each dep_id, there is only one record with the status of 'Current'.
> There can (and are) many previous names, but there should only be one =
> name
> flagged as 'Current'. I was thinking about an unique index, but then
> discarded that, as each row is unique with dep_id||line, but I want
> duplicates of dep_id||<status=3D'Previous'> and only one
> dep_id||<status=3D'Current'>. I also thought about forcing the
> user/submitter to always make line=3D1 the 'Current', but that would =
> never
> work, they're 'scientists" and therefore are unrestrained by rules. I =
> then
> thought about adding a column for a sequence, but even with multiple
> current's for the same dep_id it would still be unique. Using the name
> doesn't always work either, as many sites in different areas use the =
> same
> name, like 'El Dorado Mine'.
> How can I enforce this at the table level, since right now we are still
> getting data updates from spreadsheets and other data sources? The =
> asktom
> site
> (
> 980
> 0833250) almost has a way using a function based index, but I can't see
> how that approach works in my situation.
> Thanks
> ------------------------------------------------------------
> Bill Ferguson
> U.S. Geological Survey - Minerals Information Team
> PO Box 25046, MS-750
> Denver, Colorado 80225
> Voice (303)236-8747 ext. 321 Fax (303)236-4208
> ~ Think on a grand scale, start to implement on a small scale ~
> --

Jared Still
Certifiable Oracle DBA and Part Time Perl Evangelist
Received on Wed Oct 27 2004 - 08:11:01 CDT

Original text of this message