Weak entities

These entities exist only when another entity exists. The example used here is the one discussed in this section concerning employees and their salary history.

e17.png

Suppose that the employee table includes the id and name while the salary history contains the employee id, date of salary revision, and new salary:

employee(__id__, name)
salhist(__id__*, __revision_date__, salary)

The SQL create statements for the above relations are as follows:

create table employee (
   id type primary key,
   name type);
create table salhist (
   id type,
   revision_date date,
   salary number,
   primary key (id, revision_date),
   foreign key (id) references employee
    //on delete cascade//);

The last line is a new phrase. This tells Oracle that when a record is deleted from employee (yes, employee) then each record in salhist whose id column contains the value from that deleted employee should also be deleted.

Back to ER to SQL page.

Unless otherwise stated, the content of this page is licensed under Creative Commons Attribution-NonCommercial-ShareAlike 3.0 License