In the lecture on inserting hierarchical data, the order_id column of the orders table and the order_id column of the order_items table both have the AUTOINCREMENT attribute.
This would violate referential integrity, it seems to me, since it is possible to add a record to the order_items table, thereby generating a new value for its order_id column that may or may not match an existing value in the order_id column of the orders table. I think in T-SQL you can’t assign a value to an AUTOINCREMENT column ordinarily.
But in the lecture it seems that MySQL allowed LAST_INSERT_ID() to be assigned to the order_id column of the order_items table.
In general, should a child table have a foreign key column with the AUTOINCREMENT attribute turned on?
I mimicked the actions shown in the lecture to create a new order record in the orders table and a corresponding child record in the order_items table. The statements worked but I don’t understand why MySQL allows values to be assigned to the order_id column of the order_items table since that column has AUTOINCREMENT turned on. Perhaps this is allowed only because it’s part of a composite key? Nevertheless, it doesn’t make sense to me that a foreign key should have the AUTOINCREMENT attribute turned on.
Can anyone advise?
Thank you kindly.