CODEWITHSUNDEEP
mysqlsql
David Wyly
David Wyly

Reputation: 1701

Restrict two columns to where both fields must have data OR both fields must be NULL

I've tried my best to do my due diligence and search for a solution to this problem, but I couldn't find a suitable answer for this specific use-case.

I have two columns in a table for an order: Packaged_By and Packaged_Time. Both are initialized as NULL fields.

What I want: I want both fields to either 1) have a value that isn't NULL, or 2) leave both fields as NULL.

What I don't want: I do not ever want for there to be a case where one column has a value but where the other column remains NULL. This royally screws up my database integrity.

Unfortunately, I have not been able to figure out how to do this with unique constraints.

My question: Can I use constraints to force both fields as having either data or NULL values simultaneously? Is it possible to use triggers to accomplish this? What am I missing?

Any guidance or direction would be appreciated. Thank you.

Edit: I am using MySQL.

Upvotes: 3

Views: 1110

Answers (1)

Dour High Arch
Dour High Arch

Reputation: 21711

That design violates Second Normal Form. The way you're supposed to handle it is to create a second table including your two non-nullable data fields and a foreign key to your original table.

Upvotes: 2

Related Questions