CODEWITHSUNDEEP
sqlsql-server
Bruno
Bruno

Reputation: 153

How to update a foreign key?

I create two tables, customer and customer_order.

And set the column custmoer_email (foreign_key and unique) I tried to make the following code to update the column:

UPDATE customer 
SET customer_email = '[email protected]'
WHERE customer_email = '[email protected]';

So that gave me an error due to a foreign key, how do you do to update the information of the customer_email?

Upvotes: 0

Views: 114

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269853

Presumably, you are using the email as a foreign key reference in orders. When you change the email, you end up with a "dangling" reference to '[email protected]'.

What you want is a cascading foreign key reference. Something like this:

alter table orders add constraint fk_orders_customer_email
    foreign key (customer_email) references customers(customer_email)
        on update cascade;

(Do this after dropping the existing constraint.)

As mentioned in the comments, though, it is better to have a customer id and to use that. Then you can change the email with no such issues.

Upvotes: 1

Related Questions