0

I have a question related to this already answered question regards to MySql DB design. I was wondering what are the possible problems/sacrifices related to a decision not to put a "Not Null" constraint on foreign keys in the table? (As mentioned in the linked question, I can have multiple foreign keys in one table and I do not have to always know all of them when uploading data)

Here is an example (simplified): There are three tables in my DB:

  1. Company
  2. Investor
  3. Investment Investment table has among others following columns:
  • Company FK
  • Investor FK

Problem: I wanted to know what will be the consequences for the end user, f.e. data analyst, when I will allow "Null value" for Investor FK.

Therefore I think, my question was best answered by Vojta F who showed me both pros and cons of my solution from a perspective of a DB user.

Ludek Cizinsky
  • 13
  • 2
  • You "sacrifice" up to one byte storage space per row for the isNULL flag. But do you have a choice? – Paul Spiegel Jun 13 '20 at 14:30
  • 1
    Put everything needed for your question in your post. Show what you have done towards answering a question. "possible problems/sacrifices" is not a valid SO question. Nevertheless this is a faq. (Which clearly we can expect.) Please before considering posting read your textbook and/or manual & google any error message or many clear, concise & precise phrasings of your question/problem/goal, with & without your particular strings/names & site:stackoverflow.com & tags; read many answers. Reflect your research. See [ask] & the voting arrow mouseover texts. – philipxy Jun 14 '20 at 01:20

2 Answers2

1

As a DB user (i.e. not a DB admin) I think it is perfectly fine to omit a not null constraint from a foreign key if you don't know its value upon upload. The effect of such an omission is two-fold:

  • positive: it will be easier for you to upload new data - you won't be forced to insert a fkey value which I think is fine as long as you are aware of this when joining on this column ,
  • negative: weaker data integrity: it will be harder to resolve records among multiple tables and you'll have to think about the nulls when joining.
Vojta F
  • 534
  • 3
  • 17
1

In general the gain for using NULL when you need it exceeds any performance, etc, loss (or even gain).

The space consumed so small as to be not worth computing.

The speed considerations are usually non-existent. The Optimizer does a few things differently depending on the NULLability of a indexed column. But, again, your benefit of having (or not having) NULL is likely to exceed any downside.

There are a small number of restrictions. A PRIMARY KEY must include NOT NULL column(s).

Rick James
  • 135,179
  • 13
  • 127
  • 222