Having not null foreign key reference nullable column

Question

Say I have the following tables:

User (
  ID INT NOT NULL PRIMARY KEY
  , providerID INT
)

ProviderPlan(
  planID INT NOT NULL PRIMARY KEY
  , providerID INT NOT NULL
)

Then I want to make a foreign key mapping the two tables:

ALTER TABLE ProviderPlan ADD FOREIGN KEY (providerID) REFERENCES User(providerID) ON DELETE CASCADE;

When I try this, I get the following error:

Error Code: 1215. Cannot add foreign key constraint

My best guess for the error is because one column is nullable while the other is not. If this is the case, how can I setup a nullable column in a reference table as a not null foreign key in another table?

Ancaron · Accepted Answer · 2019-02-13 16:13:52Z

1

This error you are getting has nothing to do with one column being nullable and one not. You are getting this error because the column providerID is not a key of any kind according to your table structure.
So you can't add it as a foreign key to another table.

To remedy this, you either have to make it part of a key or rethink if it actually should be part of one or if you should drop the foreign key constraint all together.

edited Feb 13, 2019 at 16:13

answered Feb 13, 2019 at 16:06

Ancaron

5041 gold badge3 silver badges12 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Having not null foreign key reference nullable column

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related