On Mon, May 5, 2008 at 3:28 PM, Ricardo Palomares <[EMAIL PROTECTED]> 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
