0

Lets say I have a 2 SQL tables (table 1, table2). Table 1 has unique IDs. Table 2 has a foreign key to table 1's id.

How do I increment based on that ID.

Example:

Table 1     Table 2 
 ID           foreign key ID
 1            1           1
 2            1           2
 3            2           1
 4            2           2
              2           3
              3           1
              3           2
              3           3
etc..

Basically what is the sql syntax for the table creation to do this?

Jay
  • 2,656
  • 1
  • 16
  • 24
  • What do you want to achieve? And why? – Hans Then May 13 '14 at 20:38
  • I can't understand why you would want this. You are almost certainly abusing something. – Dave May 13 '14 at 21:13
  • Huh? What do you mean? This is just some functionality that would be nice. Basically lets say you have a table of schools and a table of students. 1 school as many students with unique IDs to that specific school. So student 1 from school 1 is not the same as student 1 from school 2. Basically just a way of reusing indexed kids (for this example); – Jay May 13 '14 at 22:09
  • Accepted practice (accepted by people wiser than you or me) is not to do this; Table2's ID should be unique, not unique relative to Table1's ID (all sorts of reasons, from readability and simplicity to performance and indexability). If you really have to do this, you will need a trigger and a clever function. It's not a built-in feature of mysql. – Paul Hicks May 13 '14 at 22:53
  • Another common application is for lineItemNumber on an invoice, where (as is common practice), a separate table is used for Invoice LineItems (InvoiceId, LineItemNumber, productId, Quantity, UnitPrice), where PK is (InvoiceId, LineItemNumber) – Charles Bretana May 13 '14 at 23:09

2 Answers2

1

You could also do it on the Insert, like this:

Insert Table2(ForeignKey, Id)
Select @ForeignKey, 1 + Coalesce(max(Id), 0)
from table2
Where ForeignKey = @ForeignKey
Charles Bretana
  • 143,358
  • 22
  • 150
  • 216
0

You cannot do this via a table autoincrement. But you could use a trigger to create those IDs.

See for example:

Community
  • 1
  • 1
feeela
  • 29,399
  • 7
  • 59
  • 71