# How Can A Foreign Key Column Be An AUTOINCREMENT?

**URL:** <https://forum.codewithmosh.com/t/how-can-a-foreign-key-column-be-an-autoincrement/24623>\
**Category:** SQL\
**Created:** [January 16, 2024, 10:01pm UTC](https://forum.codewithmosh.com/t/how-can-a-foreign-key-column-be-an-autoincrement/24623 "2024-01-16T22:01:56Z")\
**Posts on this page:** 2\
**Page:** 1

<div class="post-metadata">

**Author:** ![Adiv](https://sea2.discourse-cdn.com/flex020/user_avatar/forum.codewithmosh.com/adiv/32/7596_2.png) [@Adiv](https://forum.codewithmosh.com/u/Adiv)\
**Post date:** [January 16, 2024, 10:01pm UTC](https://forum.codewithmosh.com/t/how-can-a-foreign-key-column-be-an-autoincrement/24623/1 "2024-01-16T22:01:56Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![UniqueNospaceShort](https://sea2.discourse-cdn.com/flex020/user_avatar/forum.codewithmosh.com/uniquenospaceshort/32/1814_2.png) [@UniqueNospaceShort](https://forum.codewithmosh.com/u/UniqueNospaceShort)\
**Post date:** [January 21, 2024, 12:01pm UTC](https://forum.codewithmosh.com/t/how-can-a-foreign-key-column-be-an-autoincrement/24623/2 "2024-01-21T12:01:12Z")

</div>

Hi,

I don’t know of any case that would be useful. Is it even possible.  
But it makes absolutely no sense to me neither.

AFAIK the FK refers to the PK of another table. Table which has the responsibility of managing the key (autoincrement or any other way).

Cheers.