The very first real object representation in
database is a table. The table is divided into rows
and columns. If You want to represent property of
object You will use a column. If You want to
represent an instance of the object You will use row.
To define the table You have to use Data Definition
Subset of SQL. The command to do so is:
CREATE TABLE ...
When You want create a table You must specify the
columns. Each column has to have data type. With
SQLite You can choose between INTEGER, REAL, TEXT and
BLOB.
Each of the data type represents either natural
number, real number, text or binary object. Each
column has to have it's name.
We will try to create table of information on
users. It will contain User key, user name and user
password. The best practice is to create a table with
primary key to distinct the unique objects instance.
This can be achieved either by natural key or
surrogate key. In the above case the natrual key
would be unique key across the username. In the case
of surrogate key we would add another column (in this
case user's key. That would be an artifical number
from sequence. Usage of sequence would assure the key
is unique if we would select new key each time we
would insert a row.
When You create primary key it's create with
constraint. This constraint ensures there will be no
two rows with the same key in the given table.
We will try to create our very first table. It will
have 3 columns. That will be:
USER_KEY - user's unique numeric identification
USER_NAME - name of the user
USER_PASSWORD - user's password
The next task is to determine the correct data
types for the given columns. We want user key to be a
number. And the others columns to be as a text.
This brings as to following DDL Query to specify
this table:
The syntax for column definition is followin. First
we speicfy the column name. Then we give the column
data type. The rest on the user_key column of
integers is specification of primary key. That means
there cannot be any two rows with the same value in
this column. Autoincrement caluse binds the column
to the sequence and each consecutive row is then
inserted with unique number.
In next chapter we will populate the created users
table with some data.
