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
>
>

Two copies ... one with the associated rowid.

But your example invites a brief comment.

1. Never build a primary key as you have done. Always use the ALTER TABLE method so that you can name the index, select storage parameters ... the default storage is almost always wrong for a primary key constraint ... and select the appropriate tablespace for managing I/O.

-- 
Daniel Morgan
http://www.outreach.washington.edu/ext/certificates/oad/oad_crs.asp
http://www.outreach.washington.edu/ext/certificates/aoa/aoa_crs.asp
damorgan_at_x.washington.edu
(replace 'x' with a 'u' to reply)