Обсуждение: foreign key data type dereferencing

Hi all, I have a quick question...

In function definitions one can dereference the type of some
relation's column using the %TYPE syntax.  This is a very useful
feature that helps prevent type mismatch problems in databases that
are frequently changing (for example in a scientific environment
where tables are being added often, and the re-use of an already
defined function is then simple).

It would also be useful to have the same functionality in the CREATE
TABLE statement, in particular when defining a FOREIGN KEY
constraint.  For example, something along these lines:

CREATE TABLE foo (attribute_1 other_table.attribute_pk%TYPE
REFERENCES other_table.attribute_pk);

or the even simpler:

CREATE TABLE foo (attribute_1 REFERENCES other_table.attribute_pk);

However, this doesn't seem possible.  Now, under the assumption that
many people have encountered this in the past, I'm curious about the
different solutions/workarounds most have employed successfully.

Thanks for any insight on this,

Murat

On 3/23/06 3:51 PM, "Murat Tasan" <murat.tasan@cwru.edu> wrote:

> Hi all, I have a quick question...
>
> In function definitions one can dereference the type of some
> relation's column using the %TYPE syntax.  This is a very useful
> feature that helps prevent type mismatch problems in databases that
> are frequently changing (for example in a scientific environment
> where tables are being added often, and the re-use of an already
> defined function is then simple).
>
> It would also be useful to have the same functionality in the CREATE
> TABLE statement, in particular when defining a FOREIGN KEY
> constraint.  For example, something along these lines:
>
> CREATE TABLE foo (attribute_1 other_table.attribute_pk%TYPE
> REFERENCES other_table.attribute_pk);
>
> or the even simpler:
>
> CREATE TABLE foo (attribute_1 REFERENCES other_table.attribute_pk);
>
> However, this doesn't seem possible.  Now, under the assumption that
> many people have encountered this in the past, I'm curious about the
> different solutions/workarounds most have employed successfully.

I tend to make my primary keys serial and then define unique indices on
column(s) of interest.  Then all primary keys are integers and all foreign
keys are the same.  It may add an extra column over a single primary key in
some cases, but it doesn't bother me to do that.

Sean