Difference between one-to-one and one-to-many relationship in database

Question

This is probably a basic(dumb) question but when having a one-to-one relationship in a database the other table has a foreign key ID(in this example). And in a one-to-many relationship the table contains many foreign keys.

But the database does not know whether this is a one-to-one or one-to-many relationship right? The relationships that I make in an ER-Diagram is only to indicate where it should be foreign keys when making the actual tables?

I do not completely grasp the idea of the relationships, even though I have read many tutorials about this.

Thanks in advance.

Answer 1

In a sense, all the relationships we talk about are not known to the database, they are constructs we have invented to better understand how to design the tables.

The big difference in terms of table structure between one-to-one and one-to-many is that in one-to-one it is possible (but not necessary) to have a bidirectional relationship, meaning table A can have a foreign key into table B, and table B can have a foreign key into the associated record in table A. This is not possible with a one-to-many relationship.

One-to-one relationships associate one record in one table with a single record in the other table. One-to-many relationships associate one record in one table with many records in the other table.

Answer 2

To enable one-to-one relationship you need to add unique constraint to foreign key. It is not possible to have two foreign keys for each table as it will be impossible to create records.

Answer 3

Im having trouble understanding what the actual question is.

Your analysis is for the most part correct, in that if you have a 2 tables, and table2 has a foreign key to table one, it could be either a one-to-one or a many-to-one.

Your sentence "And in a one-to-many relationship the table contains many foreign keys."

The table of the 'many' side still contains one column that is a foreign key, its just that more than one row can have the same foreign key value (many rows point to one parent).

Also note that you can put the foreign key on the parent table, to the child, instead of the other way around. In this way, you can prevent one-to-many if you want to do that. Also note that in this way, more than one parent can share a child, which might or might not be what you want.