Android Sqlite Create table using Table_Name.Column_Name

Question

In my application i have 3 Sqlite tables with some foreign keys and inner join queries.

I've created DatabaseSqlHelper class which saves all my tables and columns name.

I've tried to use this line in order to create a table

Create Table If Not Exists Users(
           Users.User_Id Varchar(50) Primary Key Not Null,
           Users.Name Varchar(50) Not Null,
           Users.First_Name Varchar(50),
           Users.Profile_Picture Blob);

I've been testing this in mySql and and it has no problem but when running it on Android i get:

09-09 17:58:30.350: E/Database(12973): Failure 1 (near ".": syntax error) on 0x1cf850 when preparing 'Create Table If Not Exists Users(Users.User_Id Varchar(50) Primary Key Not Null,Users.Name Varchar(50) Not Null,Users.First_Name Varchar(50),Users.Profile_Picture Blob);'.

The reason i'm using TableName.ColumnName is because i saved does name in Strings like that so later in the inner join it will be easier for me to do Inner Join TableName on Users.Id = TableName.Id

Is there a way to fix it ?

Are you also using the fully qualified names in mysql? Without being sure I don't think you can use the full names inside the create statement. You haven't created the table yet and it's trying to fetch the table names from a table that hasn't been created... It's like sitting on a branch trying to it saw it over, while sitting on it ;-) — Darwind
– Darwind, Commented Sep 9, 2013 at 15:14
i've tried this in mySql and it works.. that why i'm asking and that why i used it.. — Asaf Nevo
– Asaf Nevo, Commented Sep 9, 2013 at 15:15

dymmeh · Accepted Answer · 2013-09-09 15:12:05Z

1

Get rid of the table name and period in front of each column.

Ex.

Create Table If Not Exists Users(
           User_Id Varchar(50) Primary Key Not Null,
           Name Varchar(50) Not Null,
           First_Name Varchar(50),
           Profile_Picture Blob);

Your inner joins will be fine using Users.id. You don't need to specify that when creating the table for that to work.. Just specify the table names when doing the joins

ex. select Id as Tablename.Id from Tablename inner join Users as U on U.user_id = Tablename.Id

answered Sep 9, 2013 at 15:12

dymmeh

22.3k5 gold badges56 silver badges60 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Asaf Nevo Over a year ago

thanks. the inner join wasn't the problem. i wanted to be efficient and save all my column names Strings as Table_Name.Column_Name in order to avoid don't TABLE_NAME_STRING + "." + TABLE_COLUMN_STRING for each inner join...

dymmeh Over a year ago

Well, think about it.. If it were legal and the column was named "Users.Name".. you still aren't providing a table name for your join. You'd still need to do "Users.Users.Name" to specify table AND column on the inner join

Asaf Nevo Over a year ago

you're right... but after testing it in mySql i thought maybe the sql system knows that if the table name == the name before the column name - don't use it

dymmeh Over a year ago

MySql and SQLite don't always conform to the same rules. MySQL is a bit "smarter" in some respects.

Isaiah · Accepted Answer · 2013-09-09 15:15:26Z

0

Just try to remove all User. in the sql.

answered Sep 9, 2013 at 15:15

Isaiah

4422 silver badges6 bronze badges

Collectives™ on Stack Overflow

Android Sqlite Create table using Table_Name.Column_Name

2 Answers 2

4 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related