3

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.

David Wyly
  • 1,671
  • 1
  • 11
  • 19
  • Why don't you set a DEFAULT value to both of them? – JordanBelf May 12 '15 at 20:08
  • I think in MS SQL you could maybe set up constraints (but I am not familiar with it to say for sure). In MySQL I think the only way you could accomplish this is with TRIGGERs to force both to NULL if either is set to NULL. Also, regarding UNIQUE constraints, MySQL treats every NULL as unique; and that is probably not relevant since it is probably legitimately possible (even if unlikely) for two rows to have the same values in these fields. – Uueerdo May 12 '15 at 20:13
  • 1
    check this question and answers: http://stackoverflow.com/questions/2115497/check-constraint-in-mysql-is-not-working – Tim3880 May 12 '15 at 20:20
  • sounds like it should be business logic in the application, or a stored procedure. I wouldn't want a trigger removing a value if one column isn't null. – ps2goat May 12 '15 at 22:21
  • @ps2goat the goal is to trigger a constraint when the rules are violated, not automatically null out a value. – David Wyly May 12 '15 at 22:52

1 Answers1

2

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.

Dour High Arch
  • 21,513
  • 29
  • 75
  • 90
  • I checked out your resource, which is very interesting, but I'm not sure (in plain English) as to why what I'm trying to do is bad practice when the end result is that I'd be effectively constraining my data. Part of me feels like this would lead to unnecessary joins, further slowing down my queries. What is the benefit of sticking to this standard? – David Wyly May 12 '15 at 22:47
  • Why what is bad practice? Your original design? [See here](http://stackoverflow.com/questions/29930453/). If you need to do this it's a necessary join, not an unnecessary one. You cannot profile performance by “feel”, you have to test it on your real application. If something like this slows your application measurably you're using the wrong database technologies. – Dour High Arch May 12 '15 at 22:55
  • Joins might effect query performance and some people will do some de-normalization to help performance.The issue here is that you want both benefits and use a database server not supporting check constraints. – Tim3880 May 13 '15 at 04:29