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

Question

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!

Answer 1

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)

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 ...)

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.