Geek asks and answers

Mapping a primary key as a foreign key in a database

I have a logical problem in my database design. I have a table with 2 fields, one of the integer field is the primary key and acts like a foreign key in all other tables.

Table structure

  • Table with identifier as primary key
  • An identifier can have basic data types as values
  • Based on these data types, the tables are mapped to the main table with the identifier as the primary key

How can I match this in database creation? How can I create a table with this requirement.

+5
source share
1 answer

Standard SQL can handle this mapping simply:

CREATE TABLE employee (
    first_name varchar,
    last_name varchar,
    date_started date,
    id int primary key
);
create table salary (
    employee_id int primary key references employee(id),
    yearly_amount numeric
);
CREATE TABLE wage (
    employee_id int primary key references employee(id),
    hourly_amount numeric
 );
+2
source

More articles:


All Articles