It is a very poor practice to expect to maintain the PK/FK relationships from the application. In any db that is nontrivial, data has close to a 100% chance of being changed from other sources including ad hoc queries, data imports, etc. It is irresponsible to think the data in the database is protected because the application has protections. Sure you think that you will force all changes through a webservice or some such, but the reality is that no one is going to add a million new customer records, from that company you just bought, one record at a time through a service. Your database has to be designed to account for the fact that people will change it directly at the source or through other applications (some of which may not be able to use your web service). Further, the application interface is usually more likely to be thrown away or redesigned than the data and when that happens, you may lose all or some of the data integrity rules. In designing databases, you have to think about how the data needs to be protected over time not about what is easiest and most convenient for the programmer or the intial application. A data professional sets up PK/FK relationships in the database because that is where the data lives and it is the best place to set it up to protect the data quality.
I see data from a lot of different companies in my current position and I see all too many that didn't set up their PK/FK relationships in the database because they have data integrity problems that a correctly designed database would not have.
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](https://i.stack.imgur.com/nWzTB.png)
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](https://i.stack.imgur.com/hWrdc.png)
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.
Best Answer
The relationship between tables in any relational database is done using Foreign Keys.
From the Wikipedia definition:
Without FKs there will be no way for any schema modelling tool of knowing that two tables are related.
A solution might be for you to script out FKs by parsing the names of the tables (if the related tables have similar names), then names and data types of columns that define the relationship. But that's no trivial task.