Re: Space used to store a foreign key constraint

From: mcstock <mcstock_at_enquery.com>
Date: Thu, 23 Oct 2003 17:29:09 -0400
Message-ID: <gLmdnS-8d_Dc1QWiRVn-gg@comcast.com>

regarding using ALTER to create the PK:

the CREATE TABLE syntax allows the same USING INDEX clause that the ALTER TABLE syntax does for specifying index tablespace and storage parameters

regarding the original question:

yes, the PK value is stored in the 'parent' table, the FK value is stored in each referencing row in the 'child' table

this is one reason why PK's should be short and arbitrary (and not updatable)-- it is usually bad design to have a varchar2(60) as a PK (and hence as an FK)
it is usually best to use a number column and automatically assign the PK from a SEQUENCE object via a TRIGGER.

When mnemonic PKs cannot be avoided, it is best to keep them as short as possible -- i usually use VARCHAR2(12)

---

Mark C. Stock
www.enquery.com
(888) 512-2048

"Daniel Morgan" <damorgan_at_x.washington.edu> wrote in message news:1066940616.327071_at_yasure...
> 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)
>
Received on Thu Oct 23 2003 - 16:29:09 CDT