SQL CREATE TABLE Statement with Practical Examples

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.

Advertisement

 

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_nameis 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 specific data_type only like auto_INCREMENT applies only to integer and floating-point types.

 

SQL Constraints

ConstraintDescription
PRIMARY KEYPRIMARY 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 KEYUNIQUE 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 KEYThe 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 | NULLThe 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.
DEFAULTWith 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, INVISIBLEif 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.
CHECKThe 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 :

SQL Create Table Statement Explained with Examples

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

Advertisement

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;

SQL Create Table Statement Explained with Examples

SELECT patient_id, name, age, gender, address, disease, doctor_id 
FROM female_patient;

SQL Create Table Statement Explained with Examples

 

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) 
);

SQL CREATE TABLE Statement with Practical Examples

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. 

Advertisement
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://learnsql.com/cookbook/how-to-create-a-table-with-a-foreign-key-in-sql/
https://dev.mysql.com/doc/refman/8.0/en/create-table.html
https://www.javatpoint.com/sql-create-table

 

Read More

https://docs.microsoft.com/en-us/sql/t-sql/statements/create-table-transact-sql?view=sql-server-ver15

 

Didn't find what you were looking for? Perform a quick search across GoLinuxCloud

If my articles on GoLinuxCloud has helped you, kindly consider buying me a coffee as a token of appreciation.

Buy GoLinuxCloud a Coffee

For any other feedbacks or questions you can either use the comments section or contact me form.

Thank You for your support!!

Leave a Comment