Re: Space used to store a foreign key constraint

Jos Martin wrote:

> If I define a pair of tables in Oracle 8.1.5 as
>
> CREATE TABLE parent (
> name VARCHAR(60) PRIMARY KEY
> );
>
> CREATE TABLE child (
> name VARCHAR(60) NOT NULL REFERENCES parent(name)
> );
>
> and I insert values
>
> INSERT INTO parent VALUES
> ('a value that is long enought not to want to store many of them');
> INSERT INTO child VALUES
> ('a value that is long enought not to want to store many of them');
>
> Does my database now *actually* contain 2 strings each of length 62
> Bytes, or does Oracle 'under the hood' insert some sort of reference
> (perhaps like a pointer or some other trick) that does the same job
> but saves on storage space?
>
> Thanks
>
> Jos

It contains the two strings. Since 8.0, however, there has been the "REF" data type to achieve precisely what you are describing as a wish-list item: referential integrity without storing the key in the child table.

It's long, complicated and you can end up with dangling refs, which can be painful. But the feature is there, sure enough.

Regards
HJR

-- 
--------------------------------------------
See my brand new website, soon to be full of 
new articles: www.dizwell.com.
Nothing much there yet, but give it time!!
--------------------------------------------