Question about inserting data into a table part of a 1:m relationship

Question

When inserting records to a database table which has a 1:m relationship to another table, is it not best practise to ensure that the related table is updated (inserted) with a new record? Is there any consequence if just one of the related tables is updated?

Also, if I update the table on the m side of the relationship (for example, a relationship of cars and wheels where one car can have many wheels but a wheel can only belong to one car), should I always insert the foreign key as an explicit parameter? E.g. if the other related table has a PK system of 1, 2, 3, 4, 5 and going up in a unique fashion, should I just insert the foreign key value as a value through a stored procedure?

EDIT: This sounds very n00bish but unfortunately I have only learnt/learning database design and Sql Server in particular from self practise (no formal training).

Answer 1

Let's use your example..

car:
    car_id
    name

wheel:
    wheel_id
    car_id

If your first question is asking if you should ensure that there is a corresponding row in the car table when you insert a record into the wheel table, then yes! In reality, if your RDBMS is set up correctly with foreign keys, then you won't be able to insert a row into the wheel table without a parent row in car.

I think this may address your second question too. If not, could you elaborate?

Answer 2

When you have a one to many relationship, you must insert the record to the parent table first. At the time of the insert to the parent table, you may or may not need to also insert to the child table(s). This is dependent entirely on your design and what information is available at the time the inital record is inserted. For instance, customers and orders havea one to many relationship. But a cusotmer may be added before he or she actually buys anything and thus would have no orders. Or a new customer may be trying to buy something and would need to have both his/her customer information stored and his/her order. YOu should set up a promary key/foreign key relationship at the time you set up the datanbase structure. This will ensure you never have an order without an associated customer. In the Order table you would add a column for the id field(s) from the Customer table. When you do insert the record, this value would be part of the insert just like the other information.

If you update either the parent or child table, you do not need to update the other table unless you change the field that has the PK.FK relationship. It is best if you never change this value though (Primary keys should be such that they almost never need to be chnaged. If you are using something such as name for your primary key, you need to to rethink your design.)