Multiple Identity Columns In SQL

[firebirds98]

I am new to SQL and I am making an asp.net page in which is a small airline service for a project for scool. For one of my tables I have:

AccountID, CustomerName, FrequentFlierMiles, FrequentFliernum for my column names. The acount AccountID column can not have duplicated values and FrequentFliernum can not have duplicated values and the only thing I could think from stopping that is through identity specification, the problem is you can only set one of the columns to have identity specification.

The AccountID and FrequentFliernum I would like to automatically increment by 1.

Right now my sql statement is
sql = "Insert Into Air_Customers VALUES(','" & txtname.Text & "','0','0')"
I would like that final '0' to automatically be incremented in the database and not have to manually put it in.

Anyhelp is appreciated. If this does not make sense I will try re-writing it.

[SheSaidImaPregy]

You cannot have two, however you can get around it three ways:

1. Make a separate table and relate the two. Each will have a unique identity column.

2. Do an update statement after inserting the values into the database, but this requires two hits to the database, which you should try to avoid.

3. Solution: Do a subquery that selects the highest/last entry for frequireflyernum, then increment it by one.
Example:

sql = "Insert Into Air_Customers (CustomerName, FrequentFlierMiles, FrequentFliernum) VALUES (@CustomerName, 0, COALESCE((SELECT TOP 1 FrequentFlierNum FROM Air_Customers ORDER BY FrequentFlierNum DESC), 0)+1)"
commandname.Parameters.AddWithValue( "@CustomerName", Trim(txtname.Text) )

Now what this will do is subquery the same database for the highest frequentfliernum and then add 1 to it. Now if it doesn't return anything, it will return the value "0", which when you add 1 to it, it becomes "1".

Learn to use parameters. they are very easy. The code I gave you uses parameters. It helps prevent sql injection, and poor formatting.

[firebirds98]

Thank you for your help!

[SheSaidImaPregy]

No problem, let me know if you need further assistance.