Dev Dev - 1 month ago 7
SQL Question

How to use foreign key in this case?

In structure database there are two tables:

Courier | Order


The table
Order
has field:
courier_id
. This if foreign key to table
Courier
.

By default it should be NULL(here attention).

When courier accept order then filed
courier_id
is changed on id of courier.

But when courier reject this order, I should set NULL ot zero to field. I know this is not possible for foreign key.

What I should do in this case?

Answer

As I can see you are trying to achieve two use cases here.

  1. Order assignment to a Courier.
  2. Order status flow.

Ideally these two should represented in two columns. Same like Courier your Order table should have a Status column and based on the situation it should have some value like PENDING, ASSIGNED, REJECTED, DELIVERED etc. I think that is more cleaner than trying to handle different use cases from a single data point.