Search code examples
sqlt-sqlauto-increment

auto_increment dependent from other table

I have 3 Tables, like in this Image: enter image description here

so let me explain it a bit better.
Table A may be some Customers, Table B may be Orders, A_has_B is just a relation.
1 Customer has n Orders.
I want b_number being auto_incremented grouped by the Customer-ID (a_id).
So its the nth order of a Customer.

How do I set this Rule in a CREATE Statement? Or is it even possible?

Solution

  • this is not possible; that rule is not something you can set in the CREATE statement of a table.

    to satisfy such a requirement i would put the logic in the insert stored procedure for table B.

    but before starting to write code there is some more answer to get: the auto_increment column can contain gaps for deleted orders? numbers can/must be reused?

    as a side note in a order-customer relation the A_has_B table is redundant and the implementation of that rule would be unnecessarily burdened by an additional join.