CSCI 343 SQL Tables

See the CREATE TABLE page of the SQLite3 documentation for much more information.

Some social examples

Saved SQL command sequences for restoring often start with commands for deleting and creating tables.

/* Delete the tables if they already exist */
drop table if exists Highschooler;
drop table if exists Friend;
drop table if exists Likes;

/* Create the schema for our tables */
create table Highschooler(ID int, name text, grade int);
create table Friend(ID1 int, ID2 int);
create table Likes(ID1 int, ID2 int);

Allowed data “types”

The datatypes of sqlite3 are relatively simple. They seem like the elementary types of C.

• INTEGER
• REAL
• NUMERIC — dates, decimal fixed point
• TEXT
• BLOB — binary large object?

However, in other database management systems, there are many more choices. www.w3schools.com has a extensive list of SQL Data Types for Various DBs. The larger list for MySQL is more common. Pay particular attention to the CHAR and VARCHAR types that places limits on the size of strings.

Constraints

In many tables, the column constraint is as important as the column datatype. sqlite3 supports the usual set of column constraints.

• PRIMARY KEY
• UNIQUE
• NOT NULL
• REFERENCES — for foreign keys
• CHECK — for arbitrary and complex validation

Here is how the original table definitions might use these constraints.

create table Highschooler(ID    INT  PRIMARY KEY,
                          name  TEXT NOT NULL,
                          grade INT  CHECK (grade BETWEEN 9 AND 12)) ;
create table Friend(ID1 int REFERENCES Highschooler(ID),
                    ID2 int REFERENCES Highschooler(ID)) ;