Set field to default null drop foreign key

Databases
1

Hi,
in a table I’ve a field like
payment_id CHAR(5) NOT NULL,
with fk
CONSTRAINT order_fk_payment FOREIGN KEY (payment_id) REFERENCES payment_method (payment_id) ON DELETE CASCADE ON UPDATE CASCADE

now I need to do on client request (there are customer with not payment_id)
ALTER TABLE order
MODIFY payment_id CHAR(5) default NULL,

so I’m wondering if I should get rid of the fk or not like
ALTER TABLE order DROP FOREIGN KEY order_fk_payment;

Thanks in advance.

2

can a foreign key be null ?

3

not

:slight_smile:

4

:slight_smile:
http://stackoverflow.com/questions/334830/mysql-composite-pk-with-nullable-fks
but it sounds a little strange to me I can have a row with a null value as fk without a corresponding value
in the reference table as pk

5

well, that would be a null pk, right? and that’s not allowed

but the point is, the nullable fk means the value is optional, but if it’s not null, then it has to exist in the referenced table

6

Modify the column to accept null and make sure your not going to mess up any unique key constraints that contain the payment_id – that is if any exists.