Re: Same field as a primary and foriegn key

On 9 Jan 2005 23:28:19 -0800, Ryan wrote:

>Hi,
>
>Let say there are 2 tables for an application, one stores the customer
>data, CUS_DATA, and another one store the customer activities in the
>application, CUS_LOG. Both tables have the field CUS_ID as the primary
>key.
>
>Should I enforce referential integrity between the 2 tables, such that
>the CUS_ID field in CUS_LOG is a primary key and as well as a foreign
>key that relates to the primary key CUS_ID in CUS_DATA?
>Thank you.
>
>Ryan

Hi Ryan,

Well, I'd say that you've already made a mistake. If a customer can have more than one activity (and based on the use of the word "activities" instead of "activcity" in your message, he can), the table to store activities can never have only CUS_ID as the primary key. You'll probably want a compound primary key on CUS_ID + ACT_DATETIME, or CUS_ID + ACT_ID or something like that.

There are other situations where two tables might have the same column(s) as primary key. A design reason might be subtyping (see example below). An optimization reason might be (in some RDBMS's) the choice to store some (rarely accessed) columns in a seperate table to speed up the use of the other columns.

An example of subtyping might be that you need to store information about persons. Some information is the same for all persons, but some other information might only apply to men or only to women. You could leave it all in the same table, with CHECK constraints to ensure that men-only information is not entered for women and vice versa, but you might also decide to create a table for all persons for the shared information, plus seperate tables for men and women. Your tables would then look like this:

CREATE TABLE Persons

            (SSN char(9) NOT NULL,
             Sex tinyint NOT NULL,
             FirstName varchar(20) NOT NULL,
--           other columns go here,
             PRIMARY KEY (SSN),
             CHECK (Sex IN ('M','F'));
CREATE TABLE Men
            (SSN char(9) NOT NULL,
             BeardLength decimal(3,1) DEFAULT NULL,
--           other columns go here,
             PRIMARY KEY (SSN),
             FOREIGN KEY (SSN) REFERENCES Persons (SSN));
CREATE TABLE Women
            (SSN char(9) NOT NULL,
             NumPregnancies int NOT NULL DEFAULT 0,
--           other columns go here,
             PRIMARY KEY (SSN),
             FOREIGN KEY (SSN) REFERENCES Persons (SSN));

It's also possible to let the database verify that only males are entered in the Men table and only females in the Women table; this requires the addition of a (seemingly redundant) UNIQUE constraint on the main table and the inclusion of one (seemingly redundant) extra column in each of the sub tables:

CREATE TABLE Persons

            (SSN char(9) NOT NULL,
             Sex tinyint NOT NULL,
             FirstName varchar(20) NOT NULL,
--           other columns go here,
             PRIMARY KEY (SSN),
             UNIQUE (SSN, Sex),
             CHECK (Sex IN ('M','F'));
CREATE TABLE Men
            (SSN char(9) NOT NULL,
             Sex tinyint NOT NULL,
             BeardLength decimal(3,1) DEFAULT NULL,
--           other columns go here,
             PRIMARY KEY (SSN),
             FOREIGN KEY (SSN, Sex) REFERENCES Persons (SSN, Sex),
             CHECK (Sex = 'M');
CREATE TABLE Women
            (SSN char(9) NOT NULL,
             NumPregnancies int NOT NULL DEFAULT 0,
--           other columns go here,
             PRIMARY KEY (SSN),
             FOREIGN KEY (SSN, Sex) REFERENCES Persons (SSN, Sex),
             CHECK (Sex = 'F'));

Best, Hugo

-- 

(Remove _NO_ and _SPAM_ to get my e-mail address)