Dev Dev - 11 months ago 62
SQL Question

How to use foreign key in this case?

In structure database there are two tables:

Courier | Order

The table
has field:
. This if foreign key to table

By default it should be NULL(here attention).

When courier accept order then filed
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 Source

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.