Creating Tables

We have been working with default tables in the hr database. What if we need to create our own tables ? . Can we do so in SQL? Oh, yes. We can create our own tables and this section is dedicated specifically for that.

This section is one of the sections from Data Definition Language SQL category (You can take a look at SQL categories here). It deals with creating of database objects like tables, views, sequences, tablespaces, etc. For our case, we shall focus only on tables. To be able to create a table, we use the CREATE TABLE keyword. But before we even start creating tables, we must first take note of these following: data types and constraints.

Data Types

Data stored in the database is of different types. For example, data for names will be in letters, for email address, it may be a mixture of letters and numbers yet for date of birth, it will be in date format. These types of data are what we refer to as data types. We need to specify the data type for each record to be kept in the database and this is why we need to first learn about the data types we have in Oracle.

VARCHAR2

It is used to store character strings ie. letters and alpha-numerical characters. It supports up to 4000 characters for a single field for example names and emails.

NUMBER

The data type supports numerical data that can be both positive or negative. It can handle fro 1 to 38 digits (referred to as precision).

DATE

It handles date data. It contains the YEAR, MONTH, DAY, HOUR, MINUTE and SECOND as well as fractional seconds fields. It doesn’t support fractional seconds and time zone.

TIMESTAMP

It is the same as date data type though it supports the fractional seconds fields. However, it doesn’t have time zone.  

You can check here for all data types in Oracle.

Constraints

Constraints are used to provide rules for a table’s data. For instance, we need some fields in the database to be unique for example, an email has to be unique for each record, we don’t want the contact field to be left blank, the field for orders can be left empty. All these are what we call the constraints and are defined during creation of the tables. Below are some of the common constraints we have in Oracle.

PRIMARY KEY

A primary key is a field that uniquely identifies a record in the database. Confused? ? . Lets say we have a student database we’re building. We can have students with the same name and even in the same class. How do we distinguish these students from one another? ? . We can give students a student number that will be unique for each students. In this case, the student number is the primary key i.e it will be used to uniquely identify any record in the database. Primary key is always unique and can’t never be left empty (NULL). We can have only one primary key in a table.

UNIQUE

Like the name suggests, this constraint is used whenever we need a given field in the table to be unique. Unlike the primary key, we can have multiple fields with the UNIQUE constraint.

NOT NULL

This constraint is used when we don’t want a particular field to be left blank when entering data into the table. For example, we want everyone in the database to have a first name. We can given NOT NULL constraint to the first_name column. This is what we normally see in the forms we usually fill online labelled as required. Meaning the field can’t be left empty. Behind the veil, a NOT NULL constraint was used to achieve that.

NULL

It is the opposite of NOT NULL constraint. A field with this constraint can be left empty when entering data.

FOREIGN KEY

This is a very important constraint in defining relational databases. A foreign key is a primary key of a table in another table. For example, a student_id column is a primary key in students table. The same column appears in the the teachers table. It is referred to as a foreign key in the teachers table. In this case, the student table is the parent table and the teachers table is the child table.

A foreign key is used to enforce a relationship between two or more tables. Whenever the constrain is applied to a table, it means both tables are related. In other-words, you cannot enter data in one table (child table) unless it exists in another (parent table). It uses the keyword REFERENCES to enforce this relationship.

You can check here for all constraints in Oracle.

Creating New Tables

Rules when creating tables

• We cannot have two tables with the same name. Each table must have a unique name.
• A table name cannot have spaces. In case of two or more words for table name, we join them using an underscore.
• Column names cannot have spaces. In case of two or more words for a column name, we join them using an underscore e.g first_name.

After understanding data types, constraints and rules when creating tables, lets create some tables using the faculty ERD below.

Note: Some datatypes in this ERD are not supported in Oracle and should be replaced with those supported. Therefore, the INT datatype should be replaced with NUMBER datatype. TEXT should be replaced with VARCHAR2. If you wish, VARCHAR can be replaced with VARCHAR2 though VARCHAR is also supported in Oracle but the latter is better.

The PRIMARY key shall be used for every column with a yellow key besides it like faculty_code in the faculty table. The FOREIGN KEY constraint shall be used whenever we find tables related to one another. Other constraints like NOT NULL, UNIQUE etc. shall be used where we deem necessary.

To be able to create tables, we use the CREATE TABLE keyword that is followed by a table name, column name, datatype and the maximum number of characters to be supported by that column. The constraint is optional.

CREATE TABLE table_name (column_name datatype (max. number of characters) {constraint});

Lets create our first table i.e. faculty table. In the code below, we’ve created a table named faculty with column faculty_code that will accept only alpha-numeric values not exceeding 7 characters. The faculty_code is the primary key in this table and it should be unique. The name column should never be left empty and supports alpha-numeric values not exceeding 100 characters.

CREATE TABLE faculty (faculty_code VARCHAR2(7) PRIMARY KEY, name VARCHAR2(100) NOT NULL)

“Table created” message will be displayed on successful execution of the CREATE TABLE statement. You see the tables created, we can use the SELECT * FROM tab; query. This will display all the tables in your account. You should be able to see all the tables including the ones we’ve just created.

Using foreign key constraint.

The department table is related to the faculty table. Therefore, to enforce this relationship, we shall use foreign key constraint. Lets create the department table.

CREATE TABLE department (department_code VARCHAR2(7) PRIMARY KEY, title VARCHAR2(255) NOT NULL, faculty_code VARCHAR2(7) REFERENCES faculty(faculty_code));

The code is scary ☹️. Don’t worry. Lets break it down from the third column. The table has faculty_code column which is a primary key in the faculty table. In department table, this column is a foreign key. To reflect this relationship, we use the REFERENCES keyword which references the faculty table specifically the faculty_code column.

It is optional to specify the datatype and number of characters for the faculty_code column in the department table since it already has these details specified in the faculty table. Therefore, the command below can still work well.

CREATE TABLE department (department_code VARCHAR2(7) PRIMARY KEY, title VARCHAR2(255) NOT NULL, faculty_code REFERENCES faculty(faculty_code));

Multiple constraints for a column

Program Table. For title, let it be unique and never to be left empty.

CREATE TABLE program (program_code VARCHAR2(7) PRIMARY KEY, title VARCHAR2(255) NOT NULL UNIQUE, department_code REFERENCES department(department_code));

Course Table (Remember we replace the INT datatype with the NUMBER datatype)

CREATE TABLE course (course_code VARCHAR2(10) PRIMARY KEY, title VARCHAR2(255) NOT NULL UNIQUE, credit_unit NUMBER(1), program_code REFERENCES program(program_code));

Facilitator Table

CREATE TABLE facilitator (facilitator_id NUMBER(5) PRIMARY KEY, first_name VARCHAR2(25) NOT NULL, last_name VARCHAR2(25), gender VARCHAR2(6));

Student Table

CREATE TABLE student(reg_number VARCHAR(15) PRIMARY KEY, student_number VARCHAR2(20) UNIQUE NOT NULL, first_name VARCHAR2(45) NOT NULL, last_name VARCHAR2(45) NOT NULL, other_name VARCHAR2(45) NULL, gender VARCHAR2(6) NOT NULL, email VARCHAR2(50) UNIQUE, program_code REFERENCES program (program_code));

Evaluation Table—We don’t specify the number of characters for TIMESTAMP and DATE datatypes.

CREATE TABLE evaluation (evaluation_id NUMBER(9) PRIMARY KEY, facilitator_id REFERENCES facilitator(facilitator_id), course_code REFERENCES course(course_code), acad_year VARCHAR2(10) NOT NULL, semester VARCHAR2(3), responses VARCHAR2(500) NULL, reg_number REFERENCES student(reg_number), created_at TIMESTAMP, updated_at TIMESTAMP, deleted_at TIMESTAMP);

We have successfully created all tables in the faculty ERD. Congratulations.

So far, all the tables we’ve created don’t have data in them. When we query from our newly created tables, no record will be returned. We shall see how we can enter data in our tables. What if we want to create a table with data in it. We can create tables from existing tables.

Creating tables from existing tables.

Sometimes we don’t want to start from scratch. We can use the existing tables to create new tables. We use the AS keyword after CREATE TABLE statement followed by the query to allow us select columns we need to use from the existing table on our new table. Confusing ? Lets get the syntax. From the syntax below, we can stop at the table_name. The rest are optional.

CREATE TABLE AS SELECT column FROM table_name {[WHERE condition][GROUP BY column][ORDER BY column]} ; 

For example, we want to create a table called customers from existing employees table. We don’t want all columns in this table. We only need the first_name, last_name, email and phone_number columns in the employees table. Let us use the query below.

CREATE TABLE customers AS SELECT first_name, last_name, phone_number FROM employees;

When we query from this table, we can see that the table has been created with the data that existed in the employees table.

SELECT * FROM customers;

We can as well limit the data we want to use in our new table using the WHERE clause. For example we decide to use only records whose first_name ends with letter e. You can look at limiting data from here

CREATE TABLE customers AS SELECT first_name, last_name, phone_number FROM employees WHERE first_name LIKE '%e';

Summary

1. CREATE TABLE keyword is used to create tables.

2. We need to specify the datatype and number of characters for a column.

3. DATE and TIMESTAMP don’t have to be with number of characters.

4. A column can be with multiple constraints

5. A table name and column name can’t have space. We join words with an underscore.