Mysql with multiple increments

[arginafi]

Hello,

 

i have a problem, and 3 id's, 1st- primary key, autoincrement, 2nd- autoincrement only when needed, 3rd- autoincrement only when needed.

for example if in a table inserted 6 rows,

ID ID_1 ID_2

1 1 0

2 2 0

3 0 1

4 3 2

5 0 3

6 4 4

 

0- 0 is when increment is no needed.

[haku]

You can only have one auto increment column in a table, so you'll need to manually increment any other columns.

[Muddy_Funster]

ok, as far as I know you can only have a single auto inc field in a table at a time.

 

that said.....WTF are you actualy trying to do here, because I can't make what you posted fit for any logical scenrio. your sample dataset doesn't even follow any obvious pattern

[arginafi]

a billing sistem, there are invoices(ID_1) and pro-forma invoices(ID_2). and they need serials that increments.

Yes, is only one auto inc field.

[Christian F.]

Redesign your database to use tables to save the invoices, which contains the auto-incrementing primary key. Then assign the user ID of the customer as a foreign key in those tables.

In other words: The exact opposite of what you've done here.

[arginafi]

Thank you all for help, i figured out.

I created 2 tables, one form_invoices, another form_proforma_invoices. each with a id AI , and id_invoice.

in invoice table a column, form, that is invoice or proforma. and if is invoice, add to form_invoice else add to form_proforma_invoice.

[Muddy_Funster]

erm...I'm not trying to be insulting, but if this is for an actual-use piece of software then you're not qualified to build this. There are leagal constaints to the numbering of invoices and quotations that must be strictly adheared to (as well as easily auditable and completly transparent) or else the goverment will - sooner or later - fine the life out of the company using it. You have not displayed in these posts the level of competence that would be required of somone to properly write a system as highly regulated and business critcal as this.

 

If it's a just-for-fun / educational project however, then I wish you the best of luck and the utmost success with it

[arginafi]

this is

erm...I'm not trying to be insulting, but if this is for an actual-use piece of software then you're not qualified to build this. There are leagal constaints to the numbering of invoices and quotations that must be strictly adheared to (as well as easily auditable and completly transparent) or else the goverment will - sooner or later - fine the life out of the company using it. You have not displayed in these posts the level of competence that would be required of somone to properly write a system as highly regulated and business critcal as this.

 

If it's a just-for-fun / educational project however, then I wish you the best of luck and the utmost success with it

it's ok, i know all legal terms, and i've completed what i needed.

[cpd]

You don't need 2 tables, a pro forma and an invoice can be put in 1 table - called invoice - with a column called "pro_forma" of type boolean indicating if its pro forma or not.

[arginafi]

You don't need 2 tables, a pro forma and an invoice can be put in 1 table - called invoice - with a column called "pro_forma" of type boolean indicating if its pro forma or not.

ideea was that i need serials for each, serials that increments and invoices and proforma invoices to be each with consecutive numers. for example PLAF000001, PLAF000002, etc (1,2 are id from invoice/proforma invoice)