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.
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 (
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:
customerIdfield 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,
customerIdis defined as the Primary Key of the table in the
addressfields accept alphanumeric values up to a maximum length of
255characters. The first two,
lastNameshare the property
NOT NULLso they will not accept null values, having to always be informed for each record.
bankAccountfield is of
INTtype, which admits numeric values, and by not indicating property, admits nulls.
CONSTRAINT, a proper name for the Primary Key is defined:
pk_customers_customerIdin 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:
orderIdfield 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
CONSTRAINTas Primary Key.
productDescriptionfields are of type
VARCHAR, so they accept alphanumeric values of up to 255 characters each.
pricefield is of type
FLOAT, which supports numeric values with decimals.
orderDatefield 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.
productQuantityfield is of type
INT, so it will only accept integer numeric values. The
DEFAULTproperty indicates a default value, in this case 0.
totalPricefield is the result of an arithmetic operation of the multiplication of the values of the
orderIdfield is defined as Primary Key and this key is assigned its own name:
productfield is defined as Unique Key and this key is assigned its own name:
CONSTRAINTthe relationship between both tables (
Orders) is established through the Foreign Key (
customerId) defined in the
Orderstable. In addition, this key is assigned the name:
It is possible to define temporary tables from the newly created tables. As the name suggests, these tables will not be permanent and will allow us to temporarily handle the data without affecting the original tables and data.
The insertion of data in the tables is done through the INSERT INTO statement.