CODEWITHSUNDEEP
mysqlforeign-keys
Adam
Adam

Reputation: 20882

MySQL Foreign Keys + which table to put the key on

I just starting to use Foreign Keys to enforce the contents of certain Columns and want to be sure I'm putting the Key on the correct table.

In a situation with 2 tables that have a one to many relationship.

e.g.: customers (one) and orders (many).

Where I want to ensure a valid customer number is used in an orders column.

I'm I correct in saying the Foreign Key would placed on the orders table.

e.g.: orders (columnX) references customers (columnX)

Upvotes: 0

Views: 576

Answers (2)

quentinxs
quentinxs

Reputation: 866

Yes, the primary key (typically a customer id) on the one table (customers) should be linked as a foreign key on the many table (orders) in a one-to-many relationship. Each order can belong to only one customer, but a customer can place many orders.

Upvotes: 1

Perception
Perception

Reputation: 80603

Remember that a foreign key is a referential constraint that says the range of values in a table column(s) is being scoped by another table. Specifically, for your example, you would have a customer_id column in your order table that would be a foreign key back to the customer table, meaning that the in all cases you need a valid customer id in order to insert a record in the order table.

Upvotes: 1

Related Questions