Pages

Foreign key

We can put estabilish a relation between two different tables. As a connector, we use a field, the foreign key, that identifies the row from the first table referring to row in the second one.

As an example, think of a table defining contacts in this way:

create table contact (
id number(*,0) not null,
first_name varchar2(20 byte) not null,
last_name varchar2(20 byte) not null,
constraint contact_pk primary key(id)
);

We have a primary key, id, and a couple of data fields, first name and last name.

We want each contact having a undefined number of interests. To implement this requisite we create another table, interest, where we store each interest description in a row, and then we link it to the actual owner of the interest through a foreing key:

create table interest (
id number(*,0) not null,
description varchar2(20 byte) not null,
contact_id number(*,0) not null,
constraint interest_pk primary key (id),
constraint contact_fk foreign key (contact_id) references contact(id)
);

Say that we have inserted a contact like this:

insert into contact (id, first_name, last_name)
values(1, 'tom', 'jones');

We can create a couple of his interests in this way:

insert into interest (id, description, contact_id)
values(1, 'trekking', 1);

insert into interest (id, description, contact_id)
values(2, 'reading', 1);

We use interest.contact_id to estabilish a connection to contact.id.

A fun basic book on SQL: Head First SQL.

No comments:

Post a Comment