Foreign Key as Primary Key when “converting” conceptual entity-relationship elements to logical relations

database-designerd

Hi guys I'm trying to implement database models using MySQL. One thing struck me when I was trying to "convert" an entity-relationship diagram (ERD) to relations (or SQL tables).

Note: Please keep in mind that a) "relationship" from the entity-relationship view and b) "relation" from the relational model are different constructs.

As far as I know, the following points are the standard when converting entity sets and relationships (as per the entity-relationship view) to relations (as per the relational model).

  • Entity Sets: Simply use all the attributes as columns (key attributes become the primary key).

  • Relationships (Many-to-Many): Use key attributes (from both entity sets) as relation attributes (i.e., table columns). So this means that when I'm trying to create a relation (table) for any relationship, I have to bring the key(s) from existing relations (tables) as a foreign key.

But when I looked up, almost everyone told me I shouldn't use a referenced foreign key as a primary key for that table (because foreign keys should allow non-unique values). This doesn't make sense because all kinds of relationship have to reference the keys from entity sets (relations).

I'm totally confused, somebody please help me!

Best Answer

Apparently, the problem at hand is about "resolving" many-to-many relationships that may occur in ERDs. A popular approach is: to add an intersection entity - aka as junction and under various other names - that has 1-to-many relationships to the original entities. Example - entities: NUMBER and LETTER. Suppose the (integer) numbers 1-26 can be combined with the letters A-Z. And: we only want to allow unique combinations of numbers and letters. Initially, we have a M:M relationship between the 2 entities. In order to resolve this, we add an INTERSECTION (the original M:M relationship is still there, but will not be needed at the later stages) enter image description here

When using Oracle's "datamodeler", we can forward-engineer the ERD to a relational model, which shows us some implementation details: 1 the 2 columns of the INTERSECTION have foreign key constraints (referencing the tables NUMBER and LETTER, respectively. This will allow multiple occurrences of (one and the same) letter/number. 2 the 2 columns "NUMBER_number_" and "LETTER_letter_" have a primary key constraint ie (among other things): only unique number/letter combinations are allowed. (Notice that this particular modelling software even generates an intersection table "Relational_1" automatically ...)

enter image description here

The resulting DDL code, and some code for testing, could be something like (table/column/constraint names differ from the models, MySQL 5.7): 

-- test tables and data
-- parent tables
create table t_numbers ( number_ integer primary key ) ;
create table t_letters ( letter_ varchar(1) primary key );


drop procedure if exists populate_n_l;
delimiter //

create procedure populate_n_l()
begin
  declare v1 int default 1 ;

  while v1 <= 26 do
    insert into t_numbers ( number_ ) values ( v1 ) ;
    insert into t_letters ( letter_ ) values ( char( v1 + 64 ) ) ;
    set v1 := v1 + 1 ;
  end while;

end//

delimiter ;

call populate_n_l() ;

Finally:

-- intersection
create table t_intersect (
  inumber integer
, iletter varchar(1)
-- constraints
, constraint fkey_number 
     foreign key( inumber ) references t_numbers( number_ )
, constraint fkey_letter
     foreign key( iletter ) references t_letters( letter_ )
, constraint pkey_combination 
     primary key ( inumber, iletter )
) Engine=InnoDB;

Testing:

-- okay (valid combination)
  insert into t_intersect ( inumber, iletter )
    values ( 1, 'A' ) ;

-- does NOT work ( as expected! - number/letter combinations must be unique ... )
  insert into t_intersect ( inumber, iletter )
    values ( 1, 'A' ) ;
-- ERROR 1062 (23000): Duplicate entry '1-A' for key 'PRIMARY'

  insert into t_intersect ( inumber, iletter )
    values ( 1, '1' ) ;
-- ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails ...

  insert into t_intersect ( inumber, iletter )
    values ( 0, 'D' ) ;
-- ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails ...


-- fine
  insert into t_intersect ( inumber, iletter )
    values ( 1, 'B' ) ;
  insert into t_intersect ( inumber, iletter )
    values ( 1, 'C' ) ;


mysql> select * from t_intersect;
+---------+---------+
| inumber | iletter |
+---------+---------+
|       1 | A       |
|       1 | B       |
|       1 | C       |
+---------+---------+

Dbfiddle here.

Related Question