Can I make a field AUTOINCREMENT after made a table?

Question

SQLite3.

Can I make a field AUTOINCREMENT after made a table?

I mean ,I make a table.

then , after that, can I make it as a AUTOINCREMENT ?

or do i have to say AUTOINCREMENT
when I make a table>

Is it only the chance to make it AUTOINCREMENT ?

Answer 1

From the SQLite Faq

Short answer: A column declared INTEGER PRIMARY KEY will autoincrement

So when you create the table, declare the column as INTEGER PRIMARY KEY and the column will autoincrement with each new insert.

Or you use the SQL statment ALTER to change the column type to an INTEGER PRIMARY KEY after the fact, but if your creating the tables yourself, it's best to do it in the initial creation statement.

Answer 2

Yes Do you have phpmyadmin installed? I believe if you go to the 'structure' tab and look along the right columnn (where the field types are listed) - I think you can change a setting there to make it autoincrement. There is also a SQL query that will do the same thing.

Answer 3

you can alter the table, altering the column definition

Answer 4

thnaks guys. I will try ALTER.

Answer 5

You cannot alter columns on a SQLite table after it has been created. You also cannot alter a table to add an integer primary key to it.

You have to add the integer primary key when you create the table.

Answer 6

@Paul Lefebvre thanks.

Then, the first table creation time is the

only chance that you can add autoincrement attribute to a culum.

Ok, thanks very much.

Answer 7

SELECT the highest ID from your "pseudo-primary key column" and then use it for your INSERT. Here is a complete example that will run in PHP 5.3:

$db = new SQLite3(":memory:");
$db->exec("CREATE TABLE foo (id INTEGER, bar VARCHAR)");

for($i=1; $i<=10; $i++) {
    $next_id = $db->querySingle("SELECT id FROM foo ORDER BY id DESC LIMIT 1") +1;
    $db->exec("INSERT INTO foo (id, bar) VALUES ($next_id, 'Just a test.')");
}

$rs = $db->query("SELECT * FROM foo");
while ($row = $rs->fetchArray()) {
    echo $row[id] ." - ". $row[bar] . "<br/>";
}

Output: "1 - Just a test." - 2 - 3 - ... - "10 - Just a test."

PS: Notice that it creates unnecessary overhead - better to use "id INTEGER PRIMARY KEY" if possible.

Answer 8

Background:

The new key will be unique over all keys currently in the table, but it might overlap with keys that have been previously deleted from the table. To create keys that are unique over the lifetime of the table, add the AUTOINCREMENT keyword to the INTEGER PRIMARY KEY declaration.

http://www.sqlite.org/faq.html#q1

SQLite limitations:

SQLite supports a limited subset of ALTER TABLE. The ALTER TABLE command in SQLite allows the user to rename a table or to add a new column to an existing table. It is not possible to rename a column, remove a column, or add or remove constraints from a table.

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

Hack seems to exist:

It appears that you can set

PRAGMA writable_schema=ON;

Then do a manual UPDATE of the sqlite_master table to insert an "id INTEGER PRIMARY KEY" into the SQL for the table definition. I tried it and it seems to work. But it is dangerous. If you mess up, you corrupt the database file.

http://www.mail-archive.com/[email protected]/msg26987.html