After the database is created, the following three SQL statements will allow you to create and change the database structure. This is done through database entities, such as a table, function, or constraint:
- The CREATE statement creates the database entity
- The ALTER statement changes the database entity
- The DROP statement deletes the database entity
There are also various SQL statements that allow you to inquire about each database entity. Such statements are database-specific and, typically, they are only used in a database console. For example, in the PostgreSQL console, \d <table> can be used to describe a table, while \dt lists all the tables. Refer to your database documentation for more details.
To create a table, you can execute the following SQL statement:
CREATE TABLE tablename ( column1 type1, column2 type2, ... );
The limitations for a table name, column names, and types of values that can be used depends on the particular database. Here is an example of a command that creates the person table in PostgreSQL:
CREATE table person (
id SERIAL PRIMARY KEY,
first_name VARCHAR NOT NULL,
last_name VARCHAR NOT NULL,
dob DATE NOT NULL );
The SERIAL keyword indicates that this field is a sequential integer number that is generated by the database every time a new record is created. Additional options for generating sequential integers are SMALLSERIAL and BIGSERIAL; they differ by size and the range of possible values:
SMALLSERIAL: 2 bytes, range from 1 to 32,767
SERIAL: 4 bytes, range from 1 to 2,147,483,647
BIGSERIAL: 8 bytes, range from 1 to 922,337,2036,854,775,807
The PRIMARY_KEY keyword indicates that this is going to be the unique identifier of the record, and will most probably be used in a search. The database creates an index for each primary key to make the search process faster. An index is a data structure that helps to accelerate data search in the table without having to check every table record. An index can include one or more columns of a table. If you request the description of the table, you will see all the existing indices.
Alternatively, we can make a composite PRIMARY KEY keyword using a combination of first_name, last_name, and dob:
CREATE table person (
first_name VARCHAR NOT NULL,
last_name VARCHAR NOT NULL,
dob DATE NOT NULL,
PRIMARY KEY (first_name, last_name, dob) );
However, there is a chance that there are two persons who will have the same name and were born on the same day.
The NOT NULL keyword imposes a constraint on the field: it cannot be empty. The database will raise an error for every attempt to create a new record with an empty field or delete the value from the existing record. We did not set the size of the columns of type VARCHAR, thus allowing these columns to store string values of any length.
The Java object that matches such a record may be represented by the following Person class:
public class Person {
private int id;
private LocalDate dob;
private String firstName, lastName;
public Person(String firstName, String lastName, LocalDate dob) {
if (dob == null) {
throw new RuntimeException("Date of birth cannot be null");
}
this.dob = dob;
this.firstName = firstName == null ? "" : firstName;
this.lastName = lastName == null ? "" : lastName;
}
public Person(int id, String firstName,
String lastName, LocalDate dob) {
this(firstName, lastName, dob);
this.id = id;
}
public int getId() { return id; }
public LocalDate getDob() { return dob; }
public String getFirstName() { return firstName;}
public String getLastName() { return lastName; }
}
As you may have noticed, there are two constructors in the Person class: with and without id. We will use the constructor that accepts id to construct an object based on the existing record, while the other constructor will be used to create an object before inserting a new record.
Once created, the table can be deleted using the DROP command:
DROP table person;
The existing table can also be changed using the ALTER SQL command; for example, we can add a column address:
ALTER table person add column address VARCHAR;
If you are not sure whether such a column exists already, you can add IF EXISTS or IF NOT EXISTS:
ALTER table person add column IF NOT EXISTS address VARCHAR;
However, this possibility exists only with PostgreSQL 9.6 and later versions.
Another important consideration to take note of during database table creation is whether another index (in addition to PRIMARY KEY) has to be added. For example, we can allow a case-insensitive search of first and last names by adding the following index:
CREATE index idx_names on person ((lower(first_name), lower(last_name));
If the search speed improves, we leave the index in place; if not, it can be removed as follows:
DROP index idx_names;
We remove it because an index has an overhead of additional writes and storage space.
We also can remove a column from a table if we need to, as follows:
ALTER table person DROP column address;
In our examples, we follow the naming convention of PostgreSQL. If you use a different database, we suggest that you look up its naming convention and follow it, so that the names you create align with those that are created automatically.