SQL unique ID

[Alboin]

How would I go about creating a unique ID for every row in an SQL database? So, if I had a table with ID's of 1, 2, 3,5, 8, how would I find an unused number in the list? Is there something for this in SQL? (I'm using sqlite in a C program, if that matters.)

Thanks!

[flann]

you could create a sequencing table, and then when an new row is inserted, select the value out of the seq table, then update it plus one.

edit:
If you are using php, pear DB has a great sequencing feature built in.

[Alboin]

Yeah, but what if an object is deleted? Then there is a space in the table.....

[Franklin52]

Important is to have records with a unique ID in your table. For new records just add 1 to the last ID-number.

The SQL statement to find out the last ID-number is:

select max(ID) as maxID from your_table;

Regards

[sdousley]

The SQL statement to find out the last ID-number is:

select max(ID) as maxID from your_table;

Regards

I dont think this will work for what he was looking for. What i read from the question was say he had the Unique ID's 1, 2, 3, 5, 8. If you added some new data, it wouldn't add it as ID 9, but in fact, add it as 4 since that's the lowest unique ID. Your method i agree is the simplest way, and had it been me, i would just set the table as an integer and set it as an auto_increment, that way passing NULL as the value will auto-asign it.

In order to assign 4 however, i can only think of one way to do so... retreive all data sorted by the Unique ID, and loop through it for the first available Unique ID.

[flann]

Your method i agree is the simplest way, and had it been me, i would just set the table as an integer and set it as an auto_increment, that way passing NULL as the value will auto-asign it.

I might be wrong, but I believe that sqlite doesn't have the auto_increment ability that mysql does.

[Alboin]

Thanks all! I think I'm going to go with the simpler method, as it doesn't really matter that much anyway. (The other way just would have been cleaner.)

I have another question, however: I have an array of numbers to store. Is there an array type with SQL? If not, how would you suggest I do such?

Thanks!

[deadram]

I might be wrong, but I believe that sqlite doesn't have the auto_increment ability that mysql does.

http://www.sqlite.org/lang_createtable.html

AUTOINCREMENT, among a few others are support in SQLite3 and I believe 2 aswell.

I have another question, however: I have an array of numbers to store. Is there an array type with SQL? If not, how would you suggest I do such?

If you know the array will always be X element long, do something like "num1 INT, num2 INT, numX INT". If not, you ~could~ use a space separated list of numbers, in a STRING. Depending on the programing language used to access the data you may even be able to access the raw binary data of the array and put it in a "BLOB"? I think it's called blob anyways. Serialized can usually spit out thier data to a BLOB and back in, but some serialized data isn't compatible between different languages and even different versions of the same language.

[Alboin]

If you know the array will always be X element long, do something like "num1 INT, num2 INT, numX INT". If not, you ~could~ use a space separated list of numbers, in a STRING.

Hm, I think I'll use a string, as that should be a bit more friendly. Thanks!