Re: primary and foregin keys

rallyka..._at_hotmail.com wrote:
> Okey, I get your point. This solution will make me do a transfer of
> data from the "product" table into the "product_history" table each
> time the "product" table is updataded with an already existing
> "product_number". Let's imagine that I go with my solution. How would
> you solve the problem if you only got the tables "products" and
> "storage". Would you use a foregin key pointing out the
> "product_number" in "products" from "storage" even if the
> "product_number" itself not is the primary key in "products" or is
this
> totaly against the constraint concept?

Your solution will not work, since a foreign key MUST reference the whole of a primary or unique key in the "parent" table. If you don't want a separate history table, then perhaps you should consider adding the date column to the storage table as part of the foreign key?

Otherwise, you will have to come up with a solution that does not use a foreign key, which seems unwise. This could be done with a materialized view and a check consraint:

SQL> create materialized view product_storage_mv 2 build immediate
3 refresh complete on commit as
4 select s.prod_number sp, p.prod_number pp, s.rowid sr, p.rowid pr 5 from storage s, product p
6 where p.prod_number (+) = s.prod_number;

Snapshot created.

SQL> alter table product_storage_mv
2 add constraint product_storage_mv_chk 3 check (pp is not null);

Table altered.

The check constraint is validated on commit:

SQL> insert into storage values (1,100);

1 row created.

SQL> commit;
commit
*
ERROR at line 1:
ORA-12008: error in materialized view refresh path ORA-02290: check constraint (TANDREWS.PRODUCT_STORAGE_MV_CHK) violated

SQL> insert into product values (1,sysdate);

1 row created.

SQL> insert into storage values (1,100);

1 row created.

SQL> commit;

Commit complete.

SQL> delete product where prod_number=1;

1 row deleted.

SQL> commit;
commit
*
ERROR at line 1:
ORA-12008: error in materialized view refresh path ORA-02290: check constraint (TANDREWS.PRODUCT_STORAGE_MV_CHK) violated

BUT having said all that, isn't the problem that your tables are the "wrong way round"? The STORAGE table appears to be keyed on prod_number, which makes it the product "master" table. So perhaps:

SQL> drop materialized view product_storage_mv;

Snapshot dropped.

SQL> rename product to product_history;

Table renamed.

SQL> rename storage to product;

Table renamed.

SQL> alter table product add constraint prod_pk primary key (prod_number);

Table altered.

SQL> alter table product_history add constraint prod_hist_fk_prod foreign key (prod_number) references product;

Table altered. Received on Fri Dec 24 2004 - 12:07:15 CET