SQLite - Creating Tables

What is a database without tables?

My next chalenge in SQLite is to create tables. Using the SQLite create table documentation I am able to create tables without any hassle.

Say for example I want to store data about a person in the database and to do that I want to create a table with fields for firstnames, surname, preferred name, birthdate,title and some sort of primary reference that should be indexed and that should auto increment whenever a new row is added to the table. I also want to indicate in this table whether the person is active.

So I start up SQLite and attached my database;

SQLite version 3.5.4
Enter ".help" for instructions
sqlite> ATTACH DATABASE 'myDB.fl' AS myDB;
sqlite> .databases

Ok so I now have everything in place to start creating the table.

sqlite> CREATE TABLE myDB.person
...> (
...> 'ID' INTEGER PRIMARY KEY AUTOINCREMENT,
...> 'FIRSTNAMES' TEXT,
...> 'SURNAME' TEXT,
...> 'NAME' TEXT,
...> 'BIRTHDATE' DEFAULT CURRENT_DATE,
...> 'TITLE' TEXT,
...> 'ACTIVE' TEXT(1) DEFAULT 'N'
...> );

Fine so far. Now to insert data!

sqlite> INSERT INTO myDB.person('FIRSTNAMES','SURNAME','NAME','BIRTHDATE','TITLE
') VALUES ('Johannes','Strydom','Johan','1977-02-22','Mr');

No problemo. To see whether everything works I do a SELECT

sqlite> SELECT * FROM myDB.person;
1|Johannes|Strydom|Johan|1977-02-22|Mr|N

And another insert to test the default date.

sqlite> INSERT INTO myDB.person('FIRSTNAMES','SURNAME','NAME','TITLE'
,'ACTIVE') VALUES ('Armourette','Strydom','Armourette','Mrs','Y');
sqlite> SELECT * FROM myDB.person;
1|Johannes|Strydom|Johan|1977-02-22|Mr|N
2|Armourette|Strydom|Armourette|2008-01-17|Mrs|Y
sqlite>

Wasn't that easy? Now If I can only get SQLite to show me the table schema. For some reason it does not want to do that.

sqlite> .schema myDB.person
sqlite>

So what worked:

• Create Table (passed)
• Insert (passed)
• Auto Increment (passed)
• Default values (passed)
• .schema (failed)

There must be a configuration or command somewhere for schema which I must have missed.

I solved the problem by exiting SQLite and opening my database as a parameter to the SQLite command line executable.

C:\sqlite-3_5_4\sqlite3.exe myDB.fl
SQLite version 3.5.4
Enter ".help" for instructions
sqlite> .schema person
CREATE TABLE person
(
'ID' INTEGER PRIMARY KEY AUTOINCREMENT,
'FIRSTNAMES' TEXT,
'SURNAME' TEXT,
'NAME' TEXT,
'BIRTHDATE' DEFAULT CURRENT_DATE,
'TITLE' TEXT,
'ACTIVE' TEXT(1) DEFAULT 'N'
);
sqlite>

So the master database is now my SQLite database! Isn't that cool? :)

With my database as the master I am also able to get the schema by running the following SELECT statement

sqlite> SELECT * FROM sqlite_master WHERE type IN ('table') AND tbl_name NOT LIKE 'INFORMATION_SCHEMA_%' ;
table|person|person|2|CREATE TABLE person
(
'ID' INTEGER PRIMARY KEY AUTOINCREMENT,
'FIRSTNAMES' TEXT,
'SURNAME' TEXT,
'NAME' TEXT,
'BIRTHDATE' DEFAULT CURRENT_DATE,
'TITLE' TEXT,
'ACTIVE' TEXT(1) DEFAULT 'N'
)
table|sqlite_sequence|sqlite_sequence|3|CREATE TABLE sqlite_sequence(name,seq)
sqlite>

Conclusion?

• .schema (!passed!)