CODEWITHSUNDEEP
mysqlforeign-keysrelationship
Marco Sulla
Marco Sulla

Reputation: 15930

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?

Upvotes: 0

Views: 2567

Answers (2)

Mike Sherrill 'Cat Recall'
Mike Sherrill 'Cat Recall'

Reputation: 95642

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.

Upvotes: 1

DatRid
DatRid

Reputation: 1169

I think you allways need an primarykey for this, or you write a trigger who checks the consistence of ID from B when a change occurs. I dont know that it would be possible without a trigger or a constraint ...

Upvotes: 1

Related Questions