Hallo everyone,

Basicly i am new in database design. I've been designing a database
for my personal project application. i'm using Database design / ER
design software Toad Data Modelere Freeware.
the software devine a Foreign key (FK) and Primary Foreign Key (PFK).

If using Identifying Relationship between two tables then the Primary
Key (PK) on table one will identified as Primary Foreign Key(PFK) on
table two.

If using Non Identifying Relationship then the Primary Key (PK) on
table one will identified as Foreign Key(FK) on table two.

My question is what is the diference between Foreign Key and Primary
Foreign Key.

Thank you for your attention.
God Bless.

froditus@gmail.com wrote:

> My question is what is the diference between Foreign Key and Primary
> Foreign Key.

Not much distinction is normally drawn between the two concepts.

Essentially a primary foreign key (PFK) is a column or group of columns
that is both a foreign key (FK) *and* the primary key (PK) for its table.

Consider a table of employees for a company. It might be set up like this:

CREATE TABLE employees (
employee_number integer NOT NULL,
surname varchar NOT NULL,
forenames varchar NOT NULL,
position varchar,
dept varchar,
recruitment timestamp,
termination timestamp,
payroll_number integer NOT NULL
);

A candidate key column is any column (or combination of columns) which is
going to be non-null for all rows, and unique for each row. In this
example, two columns jump out as possibilities: employee_number and
payroll_number.

Now, while both of these columns are keys, database design theory asks us
to choose one as the *primary* key. Here let's choose employee_number.

ALTER TABLE employees
ADD PRIMARY KEY (employee_number);
ALTER TABLE employees
ADD UNIQUE (payroll_number);

Now let's add another table for storing notes on each employee, such that
multiple notes can be stored against each person. We'll need to include a
way of matching up entries in the new employee_notes table to entries in
the employees table, so we include an employee_number columns:

CREATE TABLE employee_notes (
employee_number integer NOT NULL,
note_id integer NOT NULL,
note varchar NOT NULL,
added_on timestamp,
added_by integer,
PRIMARY KEY (employee_number, note_id)
);

Now, imagine that with have three employees in the employees table with
employee_numbers of 100, 200 and 300. What is to stop us from inserting
this row into the employee_notes table?

INSERT INTO employee_notes
VALUES (400, 1, 'Hello world', CURRENT_TIMESTAMP(), 100);

Nothing stops us, so we introduce an FK constraint. We say that the
employee_notes.employee_number number field has to reference an extant
record from the employees table:

ALTER TABLE employee_notes
ADD FOREIGN KEY (employee_number)
REFERENCES employees (employee_number)
ON UPDATE CASCADE
ON DELETE RESTRICT;

This establishes a FK-to-PK relationship from employee_notes to employees.
(Further it tells the database that if Joe Blogg's employee number in the
employees table changes from 100 to 101, then the change should be
cascaded to the employee_notes table, and if someone tries to delete Joe
Bloggs from the employees table, creating orphan records in the
employee_notes table, this should result in an error. An alternative would
be to simply cascade the delete too.)

In this case, because the target of the relationship
(employees.employee_number) is a PK, we could have used a slightly
abbreviated syntax when creating the relationship:

ALTER TABLE employee_notes
ADD FOREIGN KEY (employee_number)
REFERENCES employees
ON UPDATE CASCADE
ON DELETE RESTRICT;

We'll probably also want to make sure that the added_by column references
a real employee:

ALTER TABLE employee_notes
ADD FOREIGN KEY (added_by)
REFERENCES employees
ON UPDATE CASCADE
ON DELETE SET NULL;

Also, let's record employee salaries. For tax purposes we need to keep
track of historic salary levels.

CREATE TABLE salaries (
payroll_number integer NOT NULL,
salary integer NOT NULL,
valid_from timestamp NOT NULL,
valid_to timestamp,
PRIMARY KEY (payroll_number, valid_from)
);

And now we can set up a foreign key relationship to the employees table:

ALTER TABLE salaries
ADD FOREIGN KEY (payroll_number)
REFERENCES employees (payroll_number)
ON UPDATE CASCADE
ON DELETE RESTRICT;

Note that on the previous table we established a FK-to-PK relationship.
Here we're establishing a FK-to-AK relationship. (AK = alternative key.)

So now we've set up two foreign keys, but so far no primary foreign keys.
For our last table we'll set up a primary foreign key:

CREATE TABLE current_management (
employee_number integer,
responsibility varchar,
PRIMARY KEY (employee_number),
FOREIGN KEY (employee_number) REFERENCES employees
ON UPDATE CASCADE
ON DELETE CASCADE
);

Notice that employee_number is both the PK of current_management and also
a FK referencing the employees table. Thus the column employee_number is
the PFK of the current_management table.

An implication of this is that the current_management table will never
have more rows than the employees table.

Also of interest, of the following three queries:

SELECT * FROM employees NATURAL JOIN employee_notes;
SELECT * FROM employees NATURAL JOIN salaries;
SELECT * FROM employees NATURAL JOIN current_management;

only the last of them can be guaranteed to *never* return a result set
that lists the same employee twice. This is because of the PFK-to-PK
relationship that exists between the two tables -- which is conceptually
an even stronger tie than FK-to-PK.

--
Toby A Inkster BSc (Hons) ARCS
[Geek of HTML/SQL/Perl/PHP/Python/Apache/Linux]
[OS: Linux 2.6.17.14-mm-desktop-9mdvsmp, up 15 days, 15:23.]

Mince & Dumplings
http://tobyinkster.co.uk/blog/2008/0...nce-dumplings/