CREATE TABLE statement in SQL

CREATE TABLE is the SQL statement used to create the tables that will make up our relational databases. A table is divided into rows and columns and allows you to store data and relate it to other tables through Foreign Key.

The statement CREATE TABLE therefore allows defining the fields that will refer to the columns of the table together with the corresponding data type, as well as the pertinent restrictions, either the definition of the Primary Key to identify unique and unequivocally to each record and the Foreign Key to establish relationships with other tables in the model, to name a few examples.

In summary, the structure of the CREATE TABLE is as follows:

CREATE TABLE tableName (fieldName data type and properties) CONSTRAINT (constraints).

To have a better understanding on how the CREATE TABLE statement works, we are going to write and explain the statements for creating two new tables (Customers and Orders) in our database.

We can use the store database that we used as an example in the CREATE DATABASE section.

CREATE TABLE Customers(

    customerId        INT IDENTITY(1,1),
    [name]            VARCHAR(255) NOT NULL,
    [lastName]        VARCHAR(255) NOT NULL,
    [address]         VARCHAR(255),
    [bankAccount]     INT,

    CONSTRAINT pk_customers_customerId PRIMARY KEY (customerId)

);

From this statement we can conclude the following:

• The customerId field is of type INT, that is, it only admits integer numeric values and the IDENTITY (1,1) property tells us that the value of said field will progress sequentially one by one as we add the data, starting from one. In addition, customerId is defined as the Primary Key of the table in the CONSTRAINT.

• The name, lastName and address fields accept alphanumeric values up to a maximum length of 255 characters. The first two, name and lastName share the property NOT NULL so they will not accept null values, having to always be informed for each record.

• The bankAccount field is of INT type, which admits numeric values, and by not indicating property, admits nulls.

• In the CONSTRAINT, a proper name for the Primary Key is defined: pk_customers_customerId in order to identify it more easily.

CREATE TABLE Orders(

    orderId               INT IDENTITY(1,1),
    product               VARCHAR(255),
    productDescription    VARCHAR(255),
    price                 FLOAT,
    orderDate             DATE,
    productQuantity       INT DEFAULT(0),
    totalPrice            AS price * productQuantity,
    customerId            INT,

    CONSTRAINT pk_orders_orderId PRIMARY KEY(orderId),
    CONSTRAINT uq_orders_product UNIQUE (product),
    CONSTRAINT fk_customers_orders FOREIGN KEY (customerId) REFERENCES Customers(customerId)

);

We draw the following conclusions from this statement:

• The orderId field is of integer type and has the IDENTITY (1,1) property, so it will progress sequentially one by one for each record. In addition, this field is defined in the CONSTRAINT as Primary Key.

• The product and productDescription fields are of type VARCHAR, so they accept alphanumeric values of up to 255 characters each.

• The price field is of type FLOAT, which supports numeric values with decimals.

• The orderDate field is of type DATE, to register date format. The default format can be modified with different input masks to adjust it to the required needs.

• The productQuantity field is of type INT, so it will only accept integer numeric values. The DEFAULT property indicates a default value, in this case 0.

• The totalPrice field is the result of an arithmetic operation of the multiplication of the values of the price and productQuantity fields.

• In the CONSTRAINT the orderId field is defined as Primary Key and this key is assigned its own name: pk_orders_orderId

• In the CONSTRAINT the product field is defined as Unique Key and this key is assigned its own name: uk_orders_product

• In the CONSTRAINT the relationship between both tables (Customers and Orders) is established through the Foreign Key (customerId) defined in the Orders table. In addition, this key is assigned the name: fk_customers_orders