Here is something I would like to demo regarding foreign key constraints.
This regards what type of errors foreign key constraints can (and can’t!) save you from.
Occasionally you hear developers say how important foreign key constraints are and how foreign key constraints saved them from some bug. That might all seem well and good but when you think it through if a foreign key constraint saved you from a bug it also might very well just have been luck that saved you and the thing that went wrong might have gone wrong another way and have gone undetected as database corruption and potential accidental data disclosure to an incorrect entity.
To set the scene let’s use some schema/data from what we were talking about earlier:
create table t_skinny_customer ( customer_id bigint not null primary key, customer_name varchar(255) not null, customer_record_created_on timestamp default current_timestamp, customer_record_updated_on timestamp default current_timestamp on update current_timestamp ); create table t_skinny_customer_phone ( customer_phone_id bigint not null primary key, customer_id bigint not null references t_skinny_customer( customer_id ) on delete cascade, phone_number varchar(255) not null, phone_type enum( 'phone', 'tollfree', 'mobile' ) not null default 'phone', phone_type_order tinyint unsigned not null default 0, phone_record_created_on timestamp default current_timestamp, phone_record_updated_on timestamp default current_timestamp on update current_timestamp ); insert into t_skinny_customer ( customer_id, customer_name ) values ( 1, 'John Doe' ), ( 2, 'Jane Doe' ); insert into t_skinny_customer_phone ( customer_phone_id, customer_id, phone_number, phone_type, phone_type_order ) values ( 11, 1, '123-456-7890', 'phone', 1 ), ( 21, 2, '123-456-7894', 'phone', 1 );
Then let’s run this code:
$stmt = $pdo->prepare("
update
t_skinny_customer_phone
set
customer_id = :customer_id
where
customer_phone_id = :phone_id
");
try {
$stmt->execute([
'customer_id' => 3,
'phone_id' => 11,
]);
assert( false );
}
catch ( PDOException $ex ) {
// 2024-02-13 jj5 - foreign key constraint saved the day!
assert(
0 === strpos(
$ex->getMessage(),
'SQLSTATE[23000]: Integrity constraint violation: 1452 Cannot add or update a child row: a foreign key constraint fails'
)
);
}
$stmt->execute([
'customer_id' => 2,
'phone_id' => 11,
]);
// 2024-02-13 jj5 - ... whoops! what we've done here is switch
// one of John Doe's phone numbers to be one of Jane Doe's
// phone numbers. The foreign key constraint is of no value in
// detecting this type of data corruption.
Now I’m not saying that you shouldn’t have foreign key constraints. I’m just pointing out that if you do have foreign key constraints and you’re relying on them for “referential integrity” your door may be open for all manner of bugs (and exploits).
That your foreign key points to an actual record doesn’t necessarily imply that your foreign key points to a correct record.
