Re: How are foreign keys stored in 7.1?

sehlke_at_cts.com (Steve Ehlke) wrote:

>I need to find out how foreign keys are stored in a table. Does
>Oracle make a copy of the other tables primary key, or does it use a
>pointer of some sort like ROWID? Eample: If Table A's primary key is
>50 bytes and table B uses this as a foreign key, is 50 bytes used for
>storing the foreign key?
 

>Steve...

The content of foreign key columns is stored regularly.

When you insert or update a row which contains foreign key columns, the contents of those columns are used in a select like SELECT 'x' FROM referenced_table rt WHERE rt.pk = 'fk'; The foreign key constraint is simply guaranteeing that the matching value exists in the referenced table. This is a relational DB, so there are no pointers other than the data itself.

Those 50 bytes must be stored in table B. Let me illustrate by explaining the table creation procedures that I use.

1. create all the tables, but without any constraints. Just plain and simple "create table t1 (col1 number, col2 char(10)) tablespace ..."
2. then I create all the primary keys using "alter table t1 constraint pk_t1(col1) using index ..."
3. now I create all the foreign keys using "alter table t2 constraint fk_t2_t1(col5) references t1"

After step 1, the column definitions for the tables and the way the table data is stored is not altered by adding constraints. In fact, I usually load the data between steps 1 and 2 'cause its so much faster. When I add the primary key constraint, Oracle makes sure that the column contains non-null unique data. When I add the foreign key constraints, all the fk column values get checked for validity.

--
Doug Henderson, Glen Coulee Consulting, Calgary, Alberta, Canada
[Sent using Free Agent 1.0]