-1

I have a database with three tables, call them foo, bar and baz. Each instance of bar references an instance of baz, although there may be instances of baz not referenced in the bar table. In most cases, an instance of foo will reference an instance of bar (and therefore can be mapped to an instance of baz.) However in some cases an instance of foo will reference the baz table directly.

Possible ERD

In a simplified version where the foo-baz relationship doesn't exist, I can easily make barid part of the primary key for the foo table, which is what I'd like to do here, except that barid will occasionally be null.

Therefore a sample from the foo table might look like this: 

fooid    barid    bazid
    1        1     NULL
    2        2     NULL
    3     NULL        9
    4       17     NULL

The issue is how to handle creating a primary key for the foo table. Should I be using unique indexes instead of a constraint? I could also create dummy instances of bar for the purpose of connecting the foo and baz tables. In that case I might add an isreal field so I know when this is the case. I'm sure someone can tell me why this is a bad idea though.

ahh_real_numbers
  • 464
  • 3
  • 14
  • 2
    By definition a Primary Key cannot be Nullable. A Foreign Key can be Nullable, but a Primary key can never be `NULL`. – Thom A Oct 25 '19 at 19:59
  • Please [use text, not images/links, for text--including tables & ERDs.](https://meta.stackoverflow.com/q/285551/3404097) Paraphrase or quote from other text. Give just what you need & relate it to your problem. Use images only for what cannot be expressed as text or to augment text. Images cannot be searched for or cut & pasted. Include a legend/key & explanation with an image. – philipxy Oct 25 '19 at 20:18
  • 3
    I don't see anything wrong with the image here, @philipxy . It's a relational diagram that does actually add to the detail. – Thom A Oct 25 '19 at 20:26
  • 1
    It looks like fooid is the primary key to the foo table. That's consistent with your sample data. But ultimately it depends on the semantics of the data. And you haven't said anything about the semantics of the data. – Walter Mitty Oct 25 '19 at 20:59
  • @Walter in practice bar and baz represent locations. Maybe think of them like city and state. I'd prefer to map to city, but if I don't know the city, I'll settle for state, which I'll always have. Maybe I could create an intermediary bar/baz table where barid and bazid are nullable? Or use a calculated barid + bazid string field as part of the alternate key in foo? And yes fooid is the PK of the foo table but it's a surrogate key. My alternate key needs to include location info somehow (therefore one of barid/bazid needs to be not null, but not necessarily both.) – ahh_real_numbers Oct 25 '19 at 21:52
  • Alternate keys are not primary keys. That's a philosophical dodge, but nevertheless, you don't have to worry about NULLS in barid and bazid in the foo table. – Walter Mitty Oct 26 '19 at 01:41
  • Please clarify via edits, not comments. PS *First*: Give tables recording what you want & ignore constraints. When giving a business relation(ship)/association or table (base or query result), say what a row in it states about the business situation in terms of its column values. *Afterwards*: A NULL-free FK says a column list's subrows appear elsewhere where declared PK/UNIQUE. Give NOT NULL, UNIQUE & FK declarations where not implied by other declarations. One UNIQUE NOT NULL per table can be PK. Give other constraints. (Best to design without NULL then combine tables via INNER & LEFT JOIN.) – philipxy Oct 27 '19 at 07:50
  • [What to do with null values when modeling and normalizing?](https://stackoverflow.com/a/40733625/3404097) – philipxy Oct 27 '19 at 08:40

1 Answers1

0

There is not straightforward answer to this question without understanding the application and the implementation.Please see below the other permutations created for relations amongst the table.

fooid    barid    bazid
    1        1     NULL
    2        2     NULL
    3     NULL        9
    4       17     NULL
    5       18     6
    6       14     6
    6       18     10

Generic answer to the question is please do not create dummy values , instead the bar id primary key will be the fooid only. And add a unique constraint if the fooid,barid and bazid is always going to be unique.

Thom A
  • 88,727
  • 11
  • 45
  • 75
lajit nambiar
  • 21
  • 1
  • When a question is not clear it should be downvoted, close voted & commented on with a request for clarification. Guessing at answers leads to messes of streams of comments & inappropriate, incomplete & generic guess edits. – philipxy Oct 27 '19 at 07:59