It's not like you can avoid incorrect data just with foreign keys. Say you have an invoice model. There's a boolean value indicating that the invoice is final and a numerical value for invoice number. Final invoices must have invoice numbers. But a bug in your system manages to update an invoice so that it's final, but missing an invoice number. That row is incorrect and it's gonna cause an issue somewhere.
I can only imagine how many production databases contain incorrect data.
> There's a boolean value indicating that the invoice is final and a numerical value for invoice number. Final invoices must have invoice numbers. But a bug in your system manages to update an invoice so that it's final, but missing an invoice number.
If you're finalizing the invoice, you're hopefully doing something like this, right?
UPDATE invoices SET
final = TRUE,
invoice_number = @InvoiceNumber
WHERE id = @InvoiceId;
(Where @InvoiceNumber is some variable the application's substituting into the query)
If so, then the problem you present should never happen (unless the DB doesn't do atomic updates by default, but wrapping the update in a transaction should provide the necessary guarantees to prevent the problem from happening; if your DB doesn't support transactions, then you really should be switching to a different DB yesterday).
If that somehow could happen, though (i.e. you don't trust the application to be bug-free, which is a reasonable attitude), a CHECK constraint (as others have pointed out) would make the database enforce that:
ALTER TABLE invoices
ADD CONSTRAINT final_invoices_have_invoice_number
CHECK (final = FALSE OR invoice_number IS NOT NULL);
Of course, I'd also be wondering why an invoice would ever exist without an invoice number (it's easy enough to just make the invoice number NOT NULL - or, better yet, make it the primary key), but hey, if that's the business requirement, then that's the business requirement.
>you're hopefully doing something like this, right?
Well, in an ideal case, yes, you would be doing it like that. The reality might be different, especially when using an ORM.
>Of course, I'd also be wondering why an invoice would ever exist without an invoice number
Draft invoices do not have an invoice number, since they don't really exist anywhere. You can delete a draft invoice and nothing has happened. But if you have an invoice number, that's a record that must be kept.
> Well, in an ideal case, yes, you would be doing it like that.
I mean, it'd be either that specific case or a bug. And if it's a bug, then the check constraint as described previously would catch it and prevent it entirely.
Another option, though, would be to not even bother with a separate field for "final"; if the only two states are "draft" and "final", and finality is conditional on there being an invoice number, then the application logic can be greatly simplified:
SELECT CASE
WHEN invoice_number IS NULL THEN 'Draft'
ELSE 'Final'
END AS state
FROM invoices WHERE -- yadda yadda yadda
Most databases can cache this as a computed value column on the table itself or in a view or what have you.
> Draft invoices do not have an invoice number, since they don't really exist anywhere.
Sure they do: they exist in your database. Unless your company has a strict rule about invoice numbers always being sequential with no gaps (and that'd be pretty darn strict, in my experience), you might as well pre-assign it.
> Draft invoices do not have an invoice number, since they don't really exist anywhere. You can delete a draft invoice and nothing has happened. But if you have an invoice number, that's a record that must be kept.
Must invoices be sequential or something? Why can't I just assign the draft it's to-be number, and know it's a draft because FINAL=false/0
I can only imagine how many production databases contain incorrect data.