db-ddlutils-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Thomas Dudziak" <tom...@gmail.com>
Subject Re: Any workaround for unique constraints?
Date Fri, 09 May 2008 05:10:48 GMT
On Mon, May 5, 2008 at 3:28 PM, Ricardo Palomares
<rpmdisguise-java@yahoo.es> wrote:

> The typical case I'm hitting is:
>
> Table1:
>  - ID: synthetic PK
>  - T1CODE: "user-friendly" code, guaranteed to be unique
>
> Table2:
>  - ID: synthetic PK
>  - T1CODE: foreign key to Table1.T1CODE
>  - T2CODE: "user-friendly" code, unique when concatenated to T1CODE.
>
> The reason why I don't want to base Table2 foreign key on Table1.ID is
> to avoid double queries just to retrieve T1CODE.
>
> The reason why I don't want to set T1CODE the PK is because there are
> (minimal) chances for T1CODE values to change over time. Although they
> should always be unique, I'd rather not depend on this being true.

I might be misunderstanding your use case, but if you use
Table1.T1CODE as the target of a database foreign key, the database
requires that Table1.T1CODE is unique. Some databases require it to be
a primary key, others allos allow it to be a column with a unique
constraint.  Which means that if the value in T1CODE might not be
unique, then you cannot reference it via a foreign key, at least not a
database one. You can still maintain a semi-foreign key relationship
on the application level, though.

Tom

Mime
View raw message