We are working on invoice management app, and we have added 2 columns to db Invoice no. and Customer id, so how can we give an unique no. to invoice like 2011/1 and for customer id how can we put Cust-1 ? even is there any possibility to start invoice no. from 1 again(like 2012/1) at beginning of each new financial year?
This is a really bizarre scheme, and there isn't an auto-increment for it, you'd have to manage it yourself (in general, the consensus to self-managing incrementing primary keys is : don't).
In your table, add in an integer column like InvoiceID int NOTNULL autoincrement. If you'd like to specify the year also, you can add a column like Year int, but I think a DateStamp column (gets the time/date when record was inserted) would be even better DateStamp DATETIME NOTNULL defaultvalue: now()
It is possible to reset AutoIncrement:
ALTER TABLE some_table AUTO_INCREMENT=2Are you asking instead of having "Invoice No." as the column header you have it show "2011/1" in its place? Also, if you have spaces in your field names its recommended to remove them, or add in underscores so you don't need to put quotes around them.
SELECT "Invoice No" AS "2011/1", "Customer ID" AS "Cust-1" FROM table
or
SELECT InvoiceNo AS "2011/1", CustomerID AS "Cust-1" FROM table
auto increment documentation may be what you are looking for. Essentially, the primary key is defined for both columns, but the auto increment is on the second column.
Set the column structure as such...
...and then display to the end users a concatenated id (2011-1-1234) in the view.
It really seems like you need to organize the schema based on your output.