MySQL Force Fail a statement when querying with an invalid (non existent) foreign key value

user_9547864 0
Viewed 26

What I am looking for is a way to have a MySQL statement fail when querying table through an invalid index (id) of a foreign key column of that table.

DETAILS:

I have two tales:

cars:

id | username | brand | model | location

reservations:

id | car_id | username | date

The column reservations.car_id is bound to the column cars.id via a foreign key (ON DELETE CASCADE ON UPDATE CASCADE), where the column reservations.car_id is the child of the column cars.id

Wanted Behaviour

What I am trying to do is to have a SQL statement fail when trying to fetch a single or multiple reservation rows using an invalid car_id. The statement should return an empty array when the car_id is valid (a row with that id is present in the cars table), but there are no reservations in the table with that car_id.

I am looking for this behavior as I want to distinguish when a query is successful but simply has no results (so an empty array), and when a query fails (so I would return None). For the sake of my project, when querying reservations via an invalid car_id, I want this to fail and not simply return an empty array.

Actual Behaviour

When I run the statement:

SELECT * FROM reservations WHERE car_id = :car_id

This statement is successful, but when fetching the query results, it simply returns an empty array. I would want this to return null instead.

These are the attempts I have tried before:

SELECT * FROM reservations
            JOIN cars ON reservations.car_id = cars.id
            WHERE cars.id = :car_id;

This statement is successful but returns an empty array.

SELECT * FROM reservations WHERE car_id = ( SELECT id FROM cars WHERE id = :car_id );

This statement is also successful but returns an empty array.

mysql
1 Answers

It's not clear what you mean by fail. If you mean raise an exception and get the calling program to handle it, you'll need a stored procedure or similar code. But this would be a strange and hard-to-maintain way to fulfill your requirement.

If you mean return the value None somewhere in the result set, you can do something like this. It uses the LEFT JOIN ... IS NULL pattern for detecting missing joined rows.

SELECT cars.id, 
       COALESCE(reservations.car_id, 'No reservations!!!') 
  FROM cars
  LEFT JOIN reservations  ON reservations.car_id = cars.id
 WHERE cars.id = :car_id;

I'd try to put this into a more useful query, but you did SELECT * so I can't guess at the columns your tables have.

user_2056080
Related