0

I have some pretty complex data that I have organised in a table with columns similar to below:

|| id_1 || id_2 || id_3 || id_4 || quantity ||

id_1 and id_2 will always be NON-NULL, but at least one of id_3 and id_4 will be NULL, deciding which other table to join to. Is it possible to have a composite primary key of id's 1-4 knowing that at least one of them will be NULL? Or is this a poor design for a table?

philipxy
  • 14,867
  • 6
  • 39
  • 83
R.Palmer
  • 31
  • 3
  • 1
    Is 'possible *and* poor' an acceptable response? –  Jul 20 '21 at 09:21
  • Does this answer your question? [What to do with null values when modeling and normalizing?](https://stackoverflow.com/questions/40733477/what-to-do-with-null-values-when-modeling-and-normalizing) – philipxy Jul 20 '21 at 09:28
  • This is a common anti-pattern for subtyping/inheritance/polymorphism. [How can you represent inheritance in a database?](https://stackoverflow.com/q/3579079/3404097) Also (as one can expect) a faq. Please before considering posting read the manual & google any error message & many clear, concise & precise phrasings of your question/problem/goal, with & without your particular names/strings/numbers, 'site:stackoverflow.com' & tags; read many answers. Reflect your research. See [ask], [Help] & the voting arrow mouseover texts. If you post a question, use one phrasing as title. – philipxy Jul 20 '21 at 09:32
  • 2
    A primary key column by definition must be defined as NOT NULL –  Jul 20 '21 at 09:34
  • Thank you philipxy, this was exactly what i'm looking for – R.Palmer Jul 20 '21 at 09:36
  • You can click to agree that this is a duplicate. PS Follow a published academic information modeling & DB design textbook/reference. PS See [How do comment replies work?](https://meta.stackexchange.com/q/43019/266284) to learn to use `@x` to notify one non-sole non-poster commenter `x` re a comment. Posters, sole commenters & followers of posts always get notified. – philipxy Jul 20 '21 at 09:42
  • @JSBach Not possible given the tags. In SQL PK means UNIQUE NOT NULL constraintwise. – philipxy Jul 20 '21 at 09:45

0 Answers0