Re: PO7 Foreign Key???

Alain Schartz <schartz_at_mail.com> wrote:

>Primary keys have (by definition) to be unique: "a primary key is a rule defined on a
>column (or set of columns) so that each row in the table can be uniquely identified by the
>values in the columns (or set of columns)". This means that, if you want to specify a
>primary key on a table, you will have to identify a set of columns whose content is unique
>in the complete table.
 

>Foreign keys are the values you specify in a second table which refer to the primary keys
>of the first table. So if you wanted to link your two tables via foreign keys, these too
>will have to be a set of columns (in fact the EXACT copy of the referred primary keys).
 

>Hope this helps,

Alain,

When I started working with PO7, I was under the same impression, so I identified all of my primary key columns as UNIQUE. I have a parent table called PRODUCT with primary keys of PART_NUMBER and SERIAL_NUMBER. and a child table called WORK_ORDER with primary keys of PART_NUMBER, SERIAL_NUMBER and ORDER_NUMBER.

With the parent keys flagged as UNIQUE, I was able to use Navigator to link the child PART_NUMBER and SERIAL_NUMBER columns to the parent primary keys as foreign keys with no problem. Also, Developer linked the tables in master/detail blocks with no problem.

However, when I tried to insert records with the same part number but a different serial number, I got an error. When I went back to my Sams PO7 for Win95 book, it looked as though the UNIQUE flag was only used when you wanted to have a non-key column carry unique values. Obviously, my PRODUCT table is going to contain non-unique values in the PART_NUMBER field, but will have a unique primary key when the part and serial number columns are considered together (same part number, different serial number).

Sure enough, when I dropped the UNIQUE flag from my primary key columns, my insert problems went away. Then, my foreign key problems began.

John Thompson Received on Fri Jan 02 1998 - 00:00:00 CST