mysqlforeign-keysrelationship

How to make a relation between two tables without foreign key


I'm trying to do a mysql database, using xampp for linux 1.8.1.

I want to make a relation between two tables A and B. I only created a column inside table A that stores the id of table B.

Is this correct? There's not a way to enforce it? I mean, this way you could delete a row of table B that is referenced in table A. How to prevent this?


Solution

  • The main problem for me is to prevent deletion of a row of table B if the row id is referenced by a row of table A.

    create table table_b (
      b_id integer primary key
    );
    
    create table table_a (
      b_id integer primary key references table_b (b_id)
    );
    
    insert into table_b values (1);
    insert into table_a values (1);
    

    The following statement will fail.

    delete from table_b where b_id = 1;
    

    If you'd built that with PostgreSQL, the error message would say

    ERROR: update or delete on table "table_b" violates foreign key constraint "table_a_b_id_fkey" on table "table_a" Detail: Key (b_id)=(1) is still referenced from table "table_a".

    That structure gives you a "1 to 0 or 1" relationship between the two tables. For "1 to 0 or many", add one or more columns to table_a's primary key.

    create table table_b (
      b_id integer primary key
    );
    
    create table table_a (
      b_id integer references table_b (b_id),
      checkout_date date not null default current_date,
      primary key (b_id, checkout_date)
    );
    

    That structure will let table_a store multiple rows for one value of b_id, but each of those rows must have a different checkout_date.