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.