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.
Anyhelp is appreciated. If this does not make sense I will try re-writing it.
Let's see if I got this right. You want two unique columns, in this case AccountID and FrequentFliernum. I did not know you can not have two identity columns in one table, or at least I have not tried it.
Since the nature of the columns you're trying to use should be unique within the table, but it does not mean they could not be the same (AccountId = FrequentFliernum). Now, since it would also make sense to make them different (FrequentFliernum could have more digits), you can have the first few digit as the AccountId.
So you can have AccountId using an IDENTITY column. You can actually use a AccountId + A random number for your FrequentFliernum. Since AccountId is an IDENTITY which makes it unique, then your entire FrequentFliernum is unique. You just want it longer (more digits). In the business world, these numbers have some conventions. First few digits could be the AccountId followed by (maybe) Branch ID, followed by (another maybe) type of account like Platinum, Gold, etc...
Another thing, IDENTITY columns ensures uniqueness, not sequence. Meaning if you have an IDENTITY column on table, and you delete a row, that number will not be reused.
If you really have to do it the way you describe it, try using a
1. Calculated/Computed Column
2. Trigger to implement your formua
Just some thought...
Good luck...
-- CK
15
