The SQLÂ Create Statement
Before we start working on any database management system first, we need to create database and data tables, SQL Create Statement is used to design and create tables within a database.
SQL CREATE TABLE
SQL tables are used to store data in the database. Tables are uniquely named within a database and schema. Each table contains one or more columns. And each column has an associated data type that defines the kind of data it can store e.g., numbers, strings, or temporal data.
The SQL Create Statement has two formats:
- The Simple SQL CREATE TABLE Statement:Â Using simple SQL CREATE TABLE statement we can create table with multiple columns, with each column definition consist of name, data types and optically constrains on that column value.
- SQL CREATE TABLE ... SELECT Statement:  In SQL we can crate table as the result of old table by adding a SELECT statement at the end of the CREATE TABLE statement.
By default, tables are created in the default database, using the InnoDB storage engine. An error occurs if the table exists, if there is no default database, or if the database does not exist.
The Simple SQL CREATEÂ TABLE statement Syntax
Here is the syntax to be used with SQL CREATE TABLE Statement:
CREATE TABLE [IF NOT EXISTS] [Database_name].[Schema_name].table_name
(
Column_name datatype [ UNIQUE [KEY] | [[PRIMARY] KEY] | CONSTRAINT (NOT NULL | NULL, VISIBLE | INVISIBLE | DEFAULT | CHECK],
Column_name datatype [ UNIQUE [KEY] | [[PRIMARY] KEY] | CONSTRAINT (NOT NULL | NULL, VISIBLE | INVISIBLE | DEFAULT | CHECK],
…
CONSTRAINT constraint_name constrain_type ( column_name)..
);
- [Database_name]:
Database_name
 is the name of the database in which the table is to be created. database_name
 must specify the name of an existing database. - [Schema_name]:Â
Schema_name
 is the name of the schema to which the new table belongs. - [table_name]: Â
table_name
is the name of the new table. table name can be specified as database_name.table_name
 to create the table in a specific database. Table names must follow the rules for identifiers. - [IF NOT EXISTS]: it checks for an error from occurring if the table exists. However, there is no verification that the existing table has a structure identical to that indicated by the CREATE TABLE statement.
- [column_name]:Â it is any valid expression or name which is defined as filed of the table.
- [data_type]: it is a system data type, an alias type based on a SQL Server system data type. Â
data_type
  represents the type of value to be store in that column. There are some attributes that can be applied on specificdata_type
only likeauto_INCREMENT
 applies only to integer and floating-point types.
SQL Constraints
Constraint | Description |
---|---|
PRIMARY KEY | PRIMARY KEY is the constraint on a column that creates an index. A table can have only one PRIMARY KEY. A PRIMARY KEY is a unique identifier of a particular row of data in a table. |
UNIQUE KEY | UNIQUE creates a constraint such that all values in the column must be distinct. We can define a unique key where all key columns must be defined as NOT NULL. If they are not explicitly declared as NOT NULL, MySQL declares them so implicitly (and silently). |
FOREIGN KEY | The foreign key constraint is used to prevent operations in a relational database that would destroy links between tables. The FOREIGN KEY is a column (or a group of columns) in one table, that refers to the PRIMARY KEY of another table. |
NOT NULL | NULL | The NULL | NOT NULL constraint for a column determines whether that column can allow a null value (NULL) as the data in that column. NULL is not zero or blank.If neither NULL nor NOT NULL is specified, the column is treated as though NULL had been specified. |
DEFAULT | With default constraint, we can Specifies a default value for a column. If we do not enter the value to a specific column the default value will automatically enter for a record of the column. A column can have only one DEFAULT definition. |
VISIBLE, INVISIBLE | if we do not specify this constraint to a column then by default it will be VISIBLE. The table must at least have one VISIBLE column. |
CHECK | The CHECK constraint is verifying the value for a specific column must follow the condition specified with the check constraint. |
SQL CREATE TABLE | SELECT Statement Syntax
Here is the syntax to be used with SQL CREATE TABLE with SELECT statement:
CREATE TABLE table_name [AS] SELECT * FROM exist_table ;
CREATE TABLE table_name AS (SELECT column1_name, column2_name, column3_name...
FROM exist_table WHERE condition);
[table_name]
: name for a table to be created.[exist_table]
: name of the existing table from which a new table is created.
Let’s create some tables with SQL Create statement :
The following simple SQL creates table Doctor with one constraint, doctor_id as a primary key:Â
Create table doctor
(
doctor_id int PRIMARY KEY,
name varchar (20),
age int,
gender varchar (10),
address varchar (25)
);
- Once we execute the above query, it shows the message of ‘table created successfully’
- Doctor_id is a primary key column with data type int, name column as a data type varchar (20) means we can store character value up to 20 characters in this column, same in column gender and address.
SQL CREATE TABLE | SELECT Statement Examples
For an example of creating a new SQL table from an existing one, suppose we want to extract a female patient table and store it in a new table called female_patient
.Â
Two ways to write this SQL query:
SELECT patient_id, name, age, gender, address, disease, doctor_id
INTO female_patient
FROM patient
WHERE (gender = 'female');
CREATE TABLE female_patient
AS (SELECT patient_id,patient_name,age,gender,address,disease,doctor_id
FROM patient WHERE gender='female');
OUTPUT:Â Â
To see the result of the above SQL, create a statement, we need to use SELECT Statement:
SELECT patient_id, name, age, gender, address, disease, doctor_id
FROM patient;
SELECT patient_id, name, age, gender, address, disease, doctor_id
FROM female_patient;
SQL CREATE STATEMENTÂ | NOT NULL, PRIMARY KEY constraints
Use SQL Create a statement to create a patient table which having patient_id as a primary key column with auto-increment, patient name, and disease with NOT NULL constraint, also doctor_id foreign key constraintÂ
Create table patient
(
patient_id int PRIMARY KEY AUTO_INCREMENT,
name varchar (20) NOT NULL,
age int,
gender varchar (10),
address varchar (25),
disease varchar (20) NOT NULL,
doctor_id int FOREIGN KEY REFERENCES doctor(doctor_id)
);
Once we created both the tables using the SQL Create table, the database diagram will be the same as above.
SQL Create Statement | FOREIGN KEY, PRIMARY KEY, CHECK constraints
Now if we want to create a table called laboratory with two foreign keys and a primary key constraint, also using check constraint to check amount entered should be greater than 0, how we execute it with SQL create table statement:Â
Create table laboratory
(
lab_no int PRIMARY KEY,
patient_id int,
doctor_id int,
date date,
amount int NOT NULL CHECK (amount >0),
FOREIGN KEY (patient_id) REFERENCES patient(patient_id),
FOREIGN KEY (doctor_id) REFERENCES doctor(doctor_id)
);
- Once we execute the above query, it shows the message of ‘table created successfully’
- In the above query, we have used one more way to define a foreign key in the SQL Create table, first we have declared column name with data type and at the end of the query we define it as a foreign key for both patient_id and doctor_id.
- We have also defined check constrain in the amount column as the value must be greater than 0.
If we see the above database diagram, we have created three tables, now we are creating a room table first, also applied a check constraint to check only two values can be store in the status of the room table constraint.
Create table room
(
room_no int PRIMARY KEY,
room_type varchar (20) NOT NULL,
status varchar (10) CHECK IN ('occupied', 'free')
);
- Once we execute the above query, it shows the message of ‘table created successfully’
- In the previous query of SQL create we use to check to constrain with IN command we mean that value must be from values after IN command here status value either occupied or free.
SQL Create Statement | NOT NULL, UNIQUE constraints
Create table inpatient with patient_id as primary key, room_no, and lab_no reference to room and laboratory table respectively, date_of_adm and date_of_dis as a date data_type columns.Â
Create table inpatient
(
patient_id int PRIMARY KEY,
room_no int,
date_of_adm date NOT NULL,
date_of_dis date NOT NULL,
lab_no int UNIQUE,
FOREIGN KEY (room_no) REFERENCES room(room_no),
FOREIGN KEY (lab_no) REFERENCES laboratory(lab_no)
);
- Once we execute the above query, it shows the message of ‘table created successfully’
- In the above query, we have defined one more constraint UNIQUE on the lab_no column which means that lab_no column values are unique not have a single repeated value.
Taking the example of SQL create a statement with DEFAULT constraints, create a table with name bill which contains columns bill no as PRIMARY KEY, patient_if as foreign key, room_chargers and room_charges both column value must be greater than 0, and no_of_days having constrained as NOT NULL and DEFAULT value as 1.
Create table bill
(
bill_no int PRIMARY KEY,
patient_id int,
doctor_charge int NOT NULL CHECK (doctor_charge >0),
room_charge int NOT NULL CHECK (room_charge > 0),
no_of_days int NOT NULL DEFAULT 1,
FOREIGN KEY (patient_id) REFERENCES patient(patient_id),
);
Summary
Data stored in the database is logically stored in data tables, using SQL Create statement we can create data tables in the database management system. In this tutorial article, we have learned two ways to create a table in a database and also different constraints which we can apply to columns of the table that created it.
References
https://dev.mysql.com/doc/refman/8.0/en/create-table.html
Read More