primary key declaration error while creating table

Question

CreateL()
{
_LIT(KSQLCountry, "CREATE TABLE Country(CountryID INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,CountryName VARCHAR(45) NOT NULL,CountryCode VARCHAR(10) NOT NULL)");
User::LeaveIfError(iDatabase.Execute(KSQLCountry));
}

while creating table i want to declare for primary key and foreign key

which showing run time error (it crashes) during creation of table

what is right way to declare primary key

Answer 1

I don't know which DB are you using, but maybe this will help you

http://snippets.dzone.com/posts/show/1680

Try to use COUNTER data type instead of INTEGER and AUTOINCREMENT.

Another guess: isn't that AUTO_INCREMENT with underscore?

Answer 2

AUTO_INCREMENT is indeed with underscore, this is the error in that SQL

Answer 3

Seems like you're using the old legacy Symbian DBMS and not SQLite.

The old DBMS only supports a small subset of SQL. If my memory serves me well, that includes only some basic SELECT queries.

To create tables using the old DBMS, use the C++ API, e.g.

CDbColSet* columns = CDbColSet::NewLC();

TDbCol id(_L("CountryID"), EDbColInt32);
id.iAttributes = TDbCol::EAutoIncrement | TDbCol::ENotNull;
columns->AddL(id);     

columns->AddL(TDbCol(_L("CountryName"), EDbColText, 45));
columns->AddL(TDbCol(_L("CountryCode"), EDbColText, 10));

User::LeaveIfError(aDatabase.CreateTable(_L("Country"), *columns));

CleanupStack::PopAndDestroy(columns);

Or just use the more recent SQLite-backed RSqlDatabase API.