0

So we have like two relationships one is one-to-one and the other is one-to-zero. And let me explain what I mean by that. By one-to-one I mean that there is no parent, one row cannot be identified without the other. By one-to-zero I mean that parent can exist whenever it wants but the child needs parent to identify itself. The question is how can I make a one-to-one relationship with MySql using InnoDB?

sudo
  • 343
  • 1
  • 11
  • simple... a foreign key field with NO foreign key rule enforced. – Marc B Dec 09 '13 at 21:03
  • I have made a fault, and now corrected it please reread the question. – sudo Dec 09 '13 at 21:04
  • Nullable column vs not nullable column? – zerkms Dec 09 '13 at 21:04
  • If you know the answer then just post it, I will mark it green. – sudo Dec 09 '13 at 21:05
  • Oh. A one-to-one? For that you'd need a `CHECK` constraint. mysql honors the syntax for that, but doesn't actually enforce them. You could try using a trigger to simulate them, however. – Marc B Dec 09 '13 at 21:07
  • Could you explain that in a greater detail, so that my brain after a whole rough day can process it? An example would be great. – sudo Dec 09 '13 at 21:10
  • I mean I have like table `Persons` with `id` as `pk` and `auto increment`. Then I have a table `Addresses` with `id` as `pk` and `fk` to the `Persons` `id`. But now person can exist without an Address, and I don't want a Person to exist without an Address. I am looking a standard way to solve this problem. – sudo Dec 09 '13 at 21:25
  • make the fk in the persons table not the address table or else handle this in the app not the db – amaster Dec 09 '13 at 21:30
  • the drawback to what you want is that you would have to insert the address first with AI id and then insert the person and fk to the AI address id. So, you could end up with addresses without people... either way it should be handled in the application more thoroughly than the database can control. – amaster Dec 09 '13 at 21:32
  • Furthermore, if this will always be one to one you might even de-normalize and combine the address with the person all into one table and make it easy on yourself. – amaster Dec 09 '13 at 21:34
  • Alright this is the answer I am looking for. If you would post your message officially then I will mark it. – sudo Dec 09 '13 at 21:35

0 Answers0