Category: Table

SQL Constraints

SQL Constraints are the rules applied to a data columns or the complete table to limit the type of data that can go into a table. When you try to perform any INSERT, UPDATE, or DELETE operation on the table, RDBMS will check whether that data violates any existing constraints and if there is any violation between the defined constraint and the data action, it aborts the operation and returns an error.

We can define a column level or a table level constraints. The column level constraints are applied only to one column, whereas the table level constraints are applied to the whole table.

SQL Create Constraints

We can create constraints on a table at the time of a table creation using the CREATE TABLE statement, or after the table is created, we can use the ALTER TABLE statement to create or delete table constraints.

CREATETABLE table_name (
   column1 datatype constraint,
   column2 datatype constraint,....
   columnN datatype constraint);

Different RDBMS allows to define different constraints. This tutorial will discuss about 7 most important constraints available in MySQL.

NOT NULL Constraint

When applied to a column, NOT NULL constraint ensure that a column cannot have a NULL value. Following is the example to create a NOT NULL constraint:

CREATETABLE CUSTOMERS (
   ID INTNOTNULL,
   NAME VARCHAR(20)NOTNULL,
   AGE INTNOTNULL,
   ADDRESS CHAR(25),
   SALARY DECIMAL(18,2));

Check further detail on NOT NULL Constraint

UNIQUE Key Constraint

When applied to a column, UNIQUE Key constraint ensure that a column accepts only UNIQUE values. Following is the example to create a UNIQUE Key constraint on column ID. Once this constraint is created, column ID can’t be null and it will accept only UNIQUE values.

CREATETABLE CUSTOMERS (
   ID INTNOTNULLUNIQUE,
   NAME VARCHAR(20)NOTNULL,
   AGE INTNOTNULL,
   ADDRESS CHAR(25),
   SALARY DECIMAL(18,2));

Check further detail on Unique Key Constraint

DEFAULT Value Constraint

When applied to a column, DEFAULT Value constraint provides a default value for a column when none is specified. Following is the example to create a DEFAULT constraint on column NAME. Once this constraint is created, column NAME will set to “Not Available” value if NAME is not set to a value.

CREATETABLE CUSTOMERS (
   ID INTNOTNULLUNIQUE,
   NAME VARCHAR(20)DEFAULT'Not Available',
   AGE INTNOTNULL,
   ADDRESS CHAR(25),
   SALARY DECIMAL(18,2));

Check further detail on DEFAULT Value Constraint

PRIMARY Key Constraint

When applied to a column, PRIMARY Key constraint ensure that a column accepts only UNIQUE value and there can be a single PRIMARY Key on a table but multiple columns can constituet a PRIMARY Key. Following is the example to create a PRIMARY Key constraint on column ID. Once this constraint is created, column ID can’t be null and it will accept only unique values.

CREATETABLE CUSTOMERS(
   ID          INTNOTNULL,
   NAME        VARCHAR(20)NOTNULL,
   AGE         INTNOTNULL,
   ADDRESS     CHAR(25),
   SALARY      DECIMAL(18,2),PRIMARYKEY(ID));

Check further detail on PRIMARY Key Constraint

FOREIGN Key Constraint

FOREIGN Key constraint maps with a column in another table and uniquely identifies a row/record in that table. Following is an example to create a foreign key constraint on column ID available in CUSTOMERS table as shown in the statement below −

CREATETABLE ORDERS (
   ID INTNOTNULL,DATEDATETIME,
   CUSTOMER_ID INTFOREIGNKEYREFERENCES CUSTOMERS(ID),
   AMOUNT DECIMAL,PRIMARYKEY(ID));

Check further detail on FOREIGN Key Constraint

CHECK Value Constraint

When applied to a column, CHECK Value constraint works like a validation and it is used to check the validity of the data entered into the particular column of the table. table and uniquely identifies a row/record in that table. Following is an example to create a CHECK validation on AGE column which will not accept if its value is below to 18.

CREATETABLE CUSTOMERS(
   ID          INTNOTNULL,
   NAME        VARCHAR(20)NOTNULL,
   AGE         INTNOTNULLCHECK(AGE>=18),
   ADDRESS     CHAR(25),
   SALARY      DECIMAL(18,2),PRIMARYKEY(ID));

Check further detail on CHECK Value Constraint

INDEX Constraint

The INDEX constraints are created to speed up the data retrieval from the database. An Index can be created by using a single or group of columns in a table. A table can have a single PRIMARY Key but can have multiple INDEXES. An Index can be Unique or Non Unique based on requirements. Following is an example to create an Index on Age Column of the CUSTOMERS table.

CREATEINDEX idx_age ON CUSTOMERS ( AGE );

Check further detail on INDEX Constraint

Dropping SQL Constraints

Any constraint that you have defined can be dropped using the ALTER TABLE command with the DROP CONSTRAINT option. For example, to drop the primary key constraint from the CUSTOMERS table, you can use the following command.

ALTERTABLE CUSTOMERS DROPCONSTRAINTPRIMARYKEY;

Some RDBMS allow you to disable constraints instead of permanently dropping them from the database, which you may want to temporarily disable the constraints and then enable them later.

Data Integrity Constraints

Data integrity constraints are used to ensure the overall accuracy, completeness, and consistency of data. Now a days data integrity also refers to the data safety in regard to regulatory compliance, such as GDPR compliance etc.

Data integrity is handled in a relational database through the concept of referential integrity. There are many types of integrity constraints that play a role in Referential Integrity (RI). These constraints include Primary Key, Foreign Key, Unique Constraints and other constraints which are mentioned above.

The SQL DELETE is a command of Data Manipulation Language (DML), so it does not delete or modify the table structure but it delete only the data contained within the table. Therefore, any constraints, indexes, or triggers defined in the table will still exist after you delete data from it.

SQL DELETE TABLE Statement

The SQL DELETE TABLE statement is used to delete the existing records from a table in a database. If you wish to delete only the specific number of rows from the table, you can use the WHERE clause with the DELETE statement. If you omit the WHERE clause, all rows in the table will be deleted. The SQL DELETE statement operates on a single table at a time.

Syntax

Following is the basic syntax for using the SQL DELETE command in SQL −

DELETEFROM table_name;

SQL DELETE TABLE with WHERE Clause

We can use the SQL DELETE statement to delete specific rows from a table based on a single condition using the WHERE clause.

Syntax

Following is the syntax for deleting specific rows based on single condition −

DELETEFROM table_name
WHERE condition;

Example

Assume we have creating a table named CUSTOMERS, which contains the personal details of customers including their name, age, address and salary etc.

CREATETABLE CUSTOMERS (
   ID INTNOTNULL,
   NAME VARCHAR(20)NOTNULL,
   AGE INTNOTNULL,
   ADDRESS CHAR(25),
   SALARY DECIMAL(18,2),PRIMARYKEY(ID));

Now insert values into this table using the INSERT statement as follows −

INSERTINTO CUSTOMERS VALUES(1,'Ramesh',32,'Ahmedabad',2000.00),(2,'Khilan',25,'Delhi',1500.00),(3,'Kaushik',23,'Kota',2000.00),(4,'Chaitali',25,'Mumbai',6500.00),(5,'Hardik',27,'Bhopal',8500.00),(6,'Komal',22,'Hyderabad',4500.00),(7,'Muffy',24,'Indore',10000.00);

If you retrieve the contents of the above created table using the SELECT * FROM CUSTOMERS statement you will get the following output −

ID	NAME	AGE	ADDRESS	SALARY
1	Ramesh	32	Ahmedabad	2000.00
2	Khilan	25	Delhi	1500.00
3	Kaushik	23	Kota	2000.00
4	Chaitali	25	Mumbai	6500.00
5	Hardik	27	Bhopal	8500.00
6	Komal	22	Hyderabad	4500.00
7	Muffy	24	Indore	10000.00

Now, let’s try to delete all the customers with the name ‘Hardik’ as shown in the query below −

DELETEFROM CUSTOMERS WHERE NAME='Hardik';

Output

We get the following result. We can observe that 1 row has been deleted.

Query OK, 1 row affected (0.05 sec)

Verification

Now if you retrieve the contents of the CUSTOMERS table using the SELECT * FROM CUSTOMERS command you will get the following output −

ID	NAME	AGE	ADDRESS	SALARY
1	Ramesh	32	Ahmedabad	2000.00
2	Khilan	25	Delhi	1500.00
3	Kaushik	23	Kota	2000.00
4	Chaitali	25	Mumbai	6500.00
6	Komal	22	Hyderabad	4500.00
7	Muffy	24	Indore	10000.00

Deleting rows based on multiple conditions

We can also use the SQL DELETE TABLE statement to delete specific rows from a table based on multiple conditions using the WHERE clause. This is useful when we want to remove a subset of rows from a table that meet a certain criterion.

When using multiple conditions, we can use the comparison operators such as AND, OR, and NOT to refine our conditions. This way, only rows that satisfy the conditions will be deleted.

Syntax

Following is the basic syntax for deleting specific rows based on multiple conditions which can be connected using either AND or OR operators −

DELETEFROM table_name
WHERE condition1 AND condition2 OR... conditionN;

Here, table_name is the name of the table from which we want to delete rows, and condition1 through conditionN are the conditions that must be met for the rows to be deleted. The AND or OR operators can be used to join the conditions together.

Example

In the following query we are trying to delete all the customers whose name is either ‘Komal’ or their address is ‘Mumbai’ −

Open Compiler

DELETEFROM CUSTOMERS 
WHERE NAME='Komal'OR ADDRESS='Mumbai';

Output

We get the following result. We can observe that 2 rows has been deleted.

Query OK, 2 rows affected (0.03 sec)

Verification

Now if you retrieve the contents of the CUSTOMERS table using the SELECT * FROM CUSTOMERS command you will get the following output −

ID	NAME	AGE	ADDRESS	SALARY
1	Ramesh	32	Ahmedabad	2000.00
2	Khilan	25	Delhi	1500.00
3	Kaushik	23	Kota	2000.00
6	Komal	22	Hyderabad	4500.00
7	Muffy	24	Indore	10000.00

Deleting all the records in a table

We can use the SQL DELETE TABLE statement without a WHERE clause to delete all records in a table in SQL. This statement will remove all the rows from the specified table, effectively resetting the table to its original state (containing only the structure and its constraints).

However, it’s important to note that this operation cannot be undone, and all the data in the table will be permanently deleted.

Example

In here, we are trying to delete all the records from the CUSTOMERS table −

DELETEFROM CUSTOMERS;

Output

Following is the result produced by executing the above query −

Query OK, 4 rows affected (0.13 sec)

Verification

Now, if you retrieve the contents of the CUSTOMERS table using the SELECT * FROM CUSTOMERS statement you will get the following result −

Empty set (0.00 sec)

SQL provides command to DROP an existing table completely in a database. Once SQL DROP command is issued then there is no way back to recover the table including its data, so be careful before issuing this command in production system.

The SQL DROP Table Statement

The SQL DROP TABLE statement is a Data Definition Language (DDL) command that is used to remove a table’s definition, and its data, indexes, triggers, constraints and permission specifications (if any).

Note −

• You should be very careful while using this command because once a table is deleted then all the information available in that table will also be lost forever.
• If the table is partitioned, the statement removes the table definition, all its partitions, all data stored in those partitions, and all partition definitions.
• To drop a table in a database, one must require ALTER permission on the said table and CONTROL permissions on the table schema.
• Even though it is a data definition language command, it is different from TRUNCATE TABLE statement as the DROP statement completely frees the table from the memory.
• DROP TABLE causes an implicit commit, except when used with the TEMPORARY keyword.

Syntax

The basic syntax of this DROP TABLE statement is as follows −

DROPTABLE table_name;

Example

Assume we have created a table named CUSTOMERS using the CREATE TABLE statement as shown below −

CREATETABLE CUSTOMERS(
   ID          INTNOTNULL,
   NAME        VARCHAR(20)NOTNULL,
   AGE         INTNOTNULL,
   ADDRESS     CHAR(25),
   SALARY      DECIMAL(18,2),PRIMARYKEY(ID));

Let us first verify the CUSTOMERS table using the DESC command then we will delete it from the database −

DESCTable

If the table is created successfully the DESC command displays the structure of the table as shown below −

Field	Type	Null	Key	Default	Extra
ID	int(11)	NO	PRI	NULL
NAME	varchar(20)	NO		NULL
AGE	int(11)	NO		NULL
ADDRESS	char(25)	YES		NULL
SALARY	decimal(18,2)	YES		NULL

This means that the CUSTOMERS table is available in the database, so let us now drop it as shown below.

DROPTABLE CUSTOMERS;

Output

The output is displayed as follows −

Query OK, 0 rows affected (0.001 sec)

Verification

Now, to verify if the table is actually dropped, you can use the DESC CUSTOMERS command as shown −

DESC CUSTOMERS;

Following error is displayed −

ERROR 1146 (42S02): Table 'tutorials.CUSTOMERS' doesn't exist

When a MySQL table is dropped using SQL DROP command, privileges granted specifically for the table are not automatically dropped. They must be dropped manually.

The IF EXISTS Clause

Instead of always checking if the table exists or not in a database before dropping it, you can use the IF EXISTS clause in the DROP TABLE statement.

This clause, when specified in the DROP TABLE query, will automatically check whether the table exists in the current database and then drops it, if yes. If the table does not exist in the database, the query will be ignored.

Syntax

Following is the basic syntax of DROP TABLE IF EXISTS −

DROPTABLE[IFEXISTS] table_name;

Example

If you try to drop a table that doesn’t exist in the database, without using the IF EXISTS clause, as shown below −

DROPTABLE CUSTOMERS;

An error will be generated −

ERROR 1051 (42S02): Unknown table 'tutorials.CUSTOMERS'

If you use the IF EXISTS clause along with the DROP TABLE statement as shown below, the specified table will be dropped and if a table with the given name, doesn’t exist the query will be ignored.

But if you try to drop a table that does not exist in a database, using the IF EXISTS clause, as shown below −

DROPTABLEIFEXISTS CUSTOMERS;

The query will be ignored with the following output displayed −

Query OK, 0 rows affected, 1 warning (0.001 sec)

DROP – TEMPORARY TABLE

You can include TEMPORARY keyword with DROP TABLE statement which will drop only TEMPORARY tables. Including the TEMPORARY keyword is a good way to prevent accidentally dropping non-TEMPORARY tables.

Syntax

DROPTEMPORARYTABLE TEMP_TABLE;

Example

Following is an example to delete a temporary table CUSTOMERS.

DROPTEMPORARYTABLE CUSTOMERS;

Print Page

SQL − ALTER TABLE Statement

The SQL ALTER TABLE command is a part of Data Definition Language (DDL) and modifies the structure of a table. The ALTER TABLE command can add or delete columns, create or destroy indexes, change the type of existing columns, or rename columns or the table itself.

The ALTER TABLE command can also change characteristics of a table such as the storage engine used for the table. We will make use of the following table in our examples

ID	NAME	AGE	ADDRESS	SALARY
1	Ramesh	32	Ahmedabad	2000.00
2	Khilan	25	Delhi	1500.00
3	Kaushik	23	Kota	2000.00
4	Chaitali	25	Mumbai	6500.00
5	Hardik	27	Bhopal	8500.00
6	Komal	22	Hyderabad	4500.00
7	Muffy	24	Indore	10000.00

Syntax

Following is the basic syntax of an ALTER TABLE command −

ALTERTABLE table_name [alter_option ...];

Where, the alter_option depends on the type of operation to be performed on a table. This article will discuss such important operations one by one.

ALTER TABLE − ADD Column

If you need to add a new column to a table, you should use the ADD COLUMN option along with ALTER TABLE statement as shown below −

ALTERTABLE table_name ADD column_name datatype;

Example

Following is the example to ADD a New Column to an existing table −

ALTERTABLE CUSTOMERS ADD SEX char(1);

Output

Executing the query above will produce the following output −

Query OK, 0 rows affected (0.09 sec)
Records: 0  Duplicates: 0  Warnings: 0

Verification

To verify whether the CUSTOMERS table is altered by adding a new column SEX, use the SELECT statement to retrieve the records of the table −

SELECT*FROM CUSTOMERS;

Now, the CUSTOMERS table will be displayed as follows −

ID	NAME	AGE	ADDRESS	SALARY	SEX
1	Ramesh	32	Ahmedabad	2000.00	NULL
2	Khilan	25	Delhi	1500.00	NULL
3	Kaushik	23	Kota	2000.00	NULL
4	Chaitali	25	Mumbai	6500.00	NULL
5	Hardik	27	Bhopal	8500.00	NULL
6	Komal	22	Hyderabad	4500.00	NULL
7	Muffy	24	Indore	10000.00	NULL

ALTER TABLE − DROP COLUMN

If you need to drop an existing column from a table, you should use the DROP COLUMN option along with ALTER TABLE statement as shown below.

ALTERTABLE table_name DROPCOLUMN column_name;

Example

Following is the example to DROP sex column from the existing table.

ALTERTABLE CUSTOMERS DROPCOLUMN SEX;

Output

Executing the query above will produce the following output −

Query OK, 0 rows affected (0.07 sec)
Records: 0  Duplicates: 0  Warnings: 0

Verification

To verify whether the CUSTOMERS table is altered by dropping an existing column SEX, use the SELECT statement to retrieve the records of the table −

SELECT*FROM CUSTOMERS;

Now, the CUSTOMERS table is changed and following would be the output from the SELECT statement.

ID	NAME	AGE	ADDRESS	SALARY
1	Ramesh	32	Ahmedabad	2000.00
2	Khilan	25	Delhi	1500.00
3	Kaushik	23	Kota	2000.00
4	Chaitali	25	Mumbai	6500.00
5	Hardik	27	Bhopal	8500.00
6	Komal	22	Hyderabad	4500.00
7	Muffy	24	Indore	10000.00

ALTER TABLE − ADD INDEX

You can add index to an existing column of a table using the ADD INDEX statement along with the ALTER statement −

ALTERTABLE table_name 
ADDINDEX index_name [index_type]

Example

Following query adds an index on the column NAME of CUSTOMERS table −

ALTERTABLE CUSTOMERS ADDINDEX name_index (NAME);

Output

The output will be displayed as −

Query OK, 0 rows affected (0.003 sec)
Records: 0  Duplicates: 0  Warnings: 0

ALTER TABLE − DROP INDEX

You can drop an existing index from a table using the DROP INDEX statement along with the ALTER statement −

ALTERTABLE table_name DROPINDEX index_name;

Example

Following query adds an index on the column NAME of CUSTOMERS table −

ALTERTABLE CUSTOMERS DROPINDEX name_index;

Output

The output will be displayed as −

Query OK, 0 rows affected (0.003 sec)
Records: 0  Duplicates: 0  Warnings: 0

ALTER TABLE − ADD PRIMARY KEY

Following is the syntax to add a primary key in an existing table of a database −

ALTERTABLE table_name 
ADDCONSTRAINT constraint_name
PRIMARYKEY(column1, column2...);

Example

Before we add a primary key to an existing table, first let’s create a new table called EMPLOYEES as follows:

CREATETABLE EMPLOYEES(
   ID          INTNOTNULL,
   NAME        VARCHAR(20)NOTNULL,
   AGE         INTNOTNULL,
   ADDRESS     CHAR(25),
   SALARY      DECIMAL(18,2));

Following query adds primary key constraint on the column ID of EMPLOYEES table −

ALTERTABLE EMPLOYEES 
ADDCONSTRAINT MyPrimaryKey 
PRIMARYKEY(ID);

This will produce the following output −

Query OK, 0 rows affected, 1 warning (0.003 sec)
Records: 0  Duplicates: 0  Warnings: 1

Verification

To verify the above query if you describe the table using the DESC EMPLOYEES command −

DESC EMPLOYEES;

This will display the structure of the table created: column names, their respective data types, constraints (if any) etc.

Field	Type	Null	Key	Default	Extra
ID	int(11)	NO	PRI	NULL
NAME	varchar(20)	NO		NULL
AGE	int(11)	NO		NULL
ADDRESS	char(25)	YES		NULL
SALARY	decimal(18,2)	YES		NULL

ALTER TABLE − DROP PRIMARY KEY

Following is the syntax to delete a primary key from an existing table of a database −

ALTERTABLE table_name DROPPRIMARYKEY;

Example

Following query deletes primary key constraint from the column ID of EMPLOYEES table −

ALTERTABLE EMPLOYEES DROPPRIMARYKEY;

This will produce the following output −

Query OK, 0 rows affected, 1 warning (0.003 sec)
Records: 0  Duplicates: 0  Warnings: 1

ALTER TABLE − ADD CONSTRAINT

Following is the syntax to add a unique constraint to a column of an existing table −

ALTERTABLE table_name 
ADDCONSTRAINT constraint_name 
UNIQUE(column1, column2...);

Example

Following query adds UNIQUE constraint to the table CUSTOMERS −

ALTERTABLE EMPLOYEES ADDCONSTRAINT CONST UNIQUE(NAME);

This will produce the following output −

Query OK, 0 rows affected (0.003 sec)
Records: 0  Duplicates: 0  Warnings: 0

ALTER TABLE − DROP CONSTRAINT

Following is the syntax to drop a unique constraint from an existing table −

ALTERTABLE table_name DROPCONSTRAINT constraint_name;

Example

Following query adds UNIQUE constraint to the table CUSTOMERS −

ALTERTABLE EMPLOYEES DROPCONSTRAINT CONST;

This will produce the following output −

Query OK, 0 rows affected (0.003 sec)
Records: 0  Duplicates: 0  Warnings: 0

ALTER TABLE − RENAME COLUMN

Following is the syntax to rename a column name of an existing table −

ALTERTABLE table_name 
RENAMECOLUMN old_column_name to new_column_name;

Example

Following query renames NAME column in table CUSTOMERS −

ALTERTABLE CUSTOMERS RENAMECOLUMN name to full_name;

This will produce the following output −

Query OK, 0 rows affected (0.002 sec)
Records: 0  Duplicates: 0  Warnings: 0

ALTER TABLE − MODIFY DATATYPE

Following is the syntax to change the data type of any column in MySQL, MS Server and Oracle.

SQL Server/MS Access Syntax

ALTERTABLE table_name ALTERCOLUMN column_name datatype;

MySQL Syntax

ALTERTABLE table_name MODIFYCOLUMN column_name datatype;

Oracle Syntax

ALTERTABLE table_name MODIFYCOLUMN column_name datatype;

Example

Following query modifies datatype of SALARY column in MySQL CUSTOMERS table −

ALTERTABLE CUSTOMERS MODIFYCOLUMN ID DECIMAL(18,4);

This will produce the following output −

Query OK, 0 rows affected (0.003 sec)
Records: 0  Duplicates: 0  Warnings: 0

What are Temporary Tables?

Temporary tables are pretty much what their name describes: they are the tables which are created in a database to store temporary data. We can perform SQL operations similar to the operations on permanent tables like CREATE, UPDATE, DELETE, INSERT, JOIN, etc. But these tables will be automatically deleted once the current client session is terminated. In addition to that, they can also be explicitly deleted if the users decide to drop them manually.

Various RDBMS, like MySQL, support temporary tables starting from version 3.23 onwards. If you are using an older version of MySQL than 3.23, you can’t use temporary tables, but you can use heap tables.

As stated earlier, temporary tables will only last as long as the client session is alive. If you run the code in a PHP script, the temporary table will be destroyed automatically when the script finishes executing. If you are connected to the MySQL database server through a MySQL client program, then the temporary table will exist until you close the client connection or manually destroy the table.

Creating Temporary Tables in MySQL

To create temporary tables in MySQL, we follow the same query as creating regular database tables. However, instead of using the CREATE TABLE statement, you use CREATE TEMPORARY TABLE statement.

Syntax

Following is the syntax to create a temporary table −

CREATETEMPORARYTABLE table_name(
   column1 datatype,
   column2 datatype,
   column3 datatype,.....
   columnN datatype,PRIMARYKEY( one or more columns));

Example

Following is the SQL Query to create a temporary table in MySQL database −

CREATETEMPORARYTABLE CUSTOMERS(
   ID   INTNOTNULL,
   NAME VARCHAR(20)NOTNULL,
   AGE  INTNOTNULL,
   ADDRESS  CHAR(25),
   SALARY   DECIMAL(18,2),PRIMARYKEY(ID));

Just like normal tables you can insert data into a temporary table using the INSERT statement. Following query inserts 3 records into the above created temporary table −

INSERTINTO CUSTOMERS VALUES(1,'Ramesh',32,'Ahmedabad',2000.00),(2,'Khilan',25,'Delhi',1500.00),(3,'Kaushik',23,'Kota',2000.00);

The temporary table CUSTOMERS will be created and will have following records −

ID	NAME	AGE	ADDRESS	SALARY
1	Ramesh	32	Ahmedabad	2000.00
2	Khilan	25	Delhi	1500.00
3	Kaushik	23	Kota	2000.00

When you issue a SHOW TABLES command, then your temporary table will not be displayed in the list of tables. To verify whether the temporary table is created you need to retrieve its data using the SELECT statement. Since all the temporary tables will be removed when the current session is closed, if you log out of the MySQL session and then issue a SELECT command, you will not find temporary table in the database.

Dropping Temporary Tables in MySQL

Though all the temporary tables are deleted by MySQL when your database connection gets terminated, still, if you want to delete them manually, then you can do so by issuing a DROP TEMPORARY TABLE command.

Syntax

Following is the basic syntax to delete a temporary table:

DROPTEMPORARYTABLE table_name;

Example

Following query drops the temporary table CUSTOMERS created in the previous example −

DROPTEMPORARYTABLE CUSTOMERS;

Verification

Since we have removed the temporary table CUSTOMERS, if you try to retrieve the contents of it using the SELECT statement, it will generate an error saying the table does not exist.

SELECT*FROM CUSTOMERS;

This will produce following result −

ERROR 1146: Table 'TUTORIALS.CUSTOMERS' doesn't exist

Temporary Tables in SQL Server

The temporary table created in MySQL is visible only within the current session. But, in Microsoft SQL Server you can create two types of temporary tables.

• Local Temporary Tables: A Local Temporary Table is accessible only in the session that has created it. It is automatically deleted when the connection that has created it gets closed. If the Temporary Table is created inside the stored procedure, it get dropped automatically upon the completion of stored procedure execution.
• Global Temporary Tables: Global Temporary Tables are visible to all connections and Dropped when the last connection referencing the table is closed.

Syntax of the Local Temporary Tables

To create Local Temporary Table in SQL Server a single # is used as the prefix of a table name, as shown below −

CREATETABLE#table_name(
   column1 datatype,
   column2 datatype,
   column3 datatype,.....
   columnN datatype,PRIMARYKEY( one or more columns));

Example of the Local Temporary Tables

Following query creates a Local temporary table named CUSTOMERS in the SQL server −

CREATETABLE#CUSTOMERS(
   ID   INTNOTNULL,
   NAME VARCHAR(20)NOTNULL,
   AGE  INTNOTNULL,
   ADDRESS  CHAR(25),
   SALARY   DECIMAL(18,2),PRIMARYKEY(ID));

Syntax of the Global Temporary Tables

To create a Global Temporary Table, we need to add the prefix ## before the table name, as shown below −

CREATETABLE##table_name(
   column1 datatype,
   column2 datatype,
   column3 datatype,.....
   columnN datatype,PRIMARYKEY( one or more columns));

Example of the Global Temporary Tables

Following query creates a Global temporary table named Buyers in the SQL server −

CREATETABLE##Buyers(
   ID   INTNOTNULL,
   NAME VARCHAR(20)NOTNULL,
   AGE  INTNOTNULL,
   ADDRESS  CHAR(25),
   SALARY   DECIMAL(18,2),PRIMARYKEY(ID));

Dropping Temporary Tables in SQL Server

If you want to drop a temporary table in SQL Server manually, you need to execute the DROP TABLE statement by placing # before the local temporary table name and ## before the global temporary table name.

Example

Following query removes the Local temporary table Customers created in the previous example.

DROPTABLE#Customers;

Whereas, following query removes the global temporary table Buyers.

DROPTABLE##Buyers;

There may be a situation when you need an exact copy of a table with the same columns, attributes, indexes, default values and so forth. Instead of spending time on creating the exact same version of an existing table, you can create a clone of the existing table.

SQL Cloning Operation allows to create the exact copy of an existing table along with its definition. There are three types of cloning possible using SQL in various RDBMS; they are listed below −

• Simple Cloning
• Shallow Cloning
• Deep Cloning

Simple Cloning in MySQL

Simple cloning operation creates a new replica table from the existing table and copies all the records in newly created table. To break this process down, a new table is created using the CREATE TABLE statement; and the data from the existing table, as a result of SELECT statement, is copied into the new table.

Here, clone table inherits only the basic column definitions like the NULL settings and default values from the original table. It does not inherit the indices and AUTO_INCREMENT definitions.

Syntax

Following is the basic syntax to perform simple cloning in MySQL−

CREATETABLE new_table SELECT*FROM original_table;

Example

Consider the following existing CUSTOMERS table which will be cloned in next new few steps.

ID	NAME	AGE	ADDRESS	SALARY
1	Ramesh	32	Ahmedabad	2000.00
2	Khilan	25	Delhi	1500.00
3	Kaushik	23	Kota	2000.00
4	Chaitali	25	Mumbai	6500.00
5	Hardik	27	Bhopal	8500.00
6	Komal	22	Hyderabad	4500.00
7	Muffy	24	Indore	10000.00

Now let’s use the following SQL statement to create NEW_CUSTOMERS table using the existing table CUSTOMERS.

CREATETABLE NEW_CUSTOMERS SELECT*FROM CUSTOMERS;

Output

The output is displayed as −

Query OK, 7 rows affected (0.06 sec)
Records: 7  Duplicates: 0  Warnings: 0

Verification

To verify whether the table has been cloned successfully, we can use the following SELECT query −

SELECT*FROM NEW_CUSTOMERS;

If NEW_CUSTOMERS table is created successfully, then it should get all the records which are available in CUSTOMERS table.

Shallow Cloning in MySQL

Shallow cloning operation creates a new replica table from the existing table but does not copy any data records into newly created table, so only new but empty table is created.

Here, the clone table contains only the structure of the original table along with the column attributes including indices and AUTO_INCREMENT definition..

Syntax

Following is the basic syntax to perform shallow cloning in MySQL RDBMS −

CREATETABLE new_table LIKE original_table;

Example

Following is an example to create a shallow clone copy of the existing table CUSTOMERS.

CREATETABLE SHALL_CUSTOMERS LIKE CUSTOMERS;

Output

The output is displayed as −

Query OK, 0 rows affected (0.06 sec)

Verification

To verify whether the table has been cloned successfully, we can use the following DESC table_name query −

DESC SHALL_CUSTOMERS;

This will display the following information about the SHALL_CUSTOMERS table which is just a replica of CUSTOMERS table −

Field	Type	Null	Key	Default	Extra
ID	int(11)	NO	PRI	NULL
NAME	varchar(20)	NO		NULL
AGE	int(11)	NO		NULL
ADDRESS	char(25)	YES		NULL
SALARY	decimal(18,2)	YES		NULL

Deep Cloning in MySQL

Deep cloning operation is a combination of simple cloning and shallow cloning. It not only copies the structure of the existing table but also its data into the newly created table. Hence, the new table will have all the contents from existing table and all the attributes including indices and the AUTO_INCREMENT definitions.

Since it is a combination of shallow and simple cloning, this type of cloning will have two different queries to be executed: one with CREATE TABLE statement and one with INSERT INTO statement. The CREATE TABLE statement will create the new table by including all the attributes of existing table; and INSERT INTO statement will insert the data from existing table into new table.

Syntax

Following is the basic syntax to perform deep cloning in MySQL RDBMS −

CREATETABLE new_table LIKE original_table;INSERTINTO new_table SELECT*FROM original_table;

Example

Following is an example to create a deep clone copy of the existing table CUSTOMERS. First step is to create a shallow clone of the existing table.

CREATETABLE DEEP_CUSTOMERS LIKE CUSTOMERS;

The output is displayed as −

Query OK, 0 rows affected (0.06 sec)

Now second step is to copy all the records from the CUSTOMERS table to DEEP_CUSTOMERS.

INSERTINTO DEEP_CUSTOMERS SELECT*FROM CUSTOMERS;

Output

The output is displayed as −

Query OK, 7 rows affected (0.01 sec)
Records: 7  Duplicates: 0  Warnings: 0

Verification

To verify whether the table has been cloned successfully, we can use the following SELECT query −

SELECT*FROM DEEP_CUSTOMERS;

If DEEP_CUSTOMERS table is cloned successfully, then it should get all the records which are available in CUSTOMERS.

Table Cloning in SQL Server

However, there is no direct way to fully clone a table in an SQL server. However, we have some work around to handle the situation.

SELECT…INTO STATEMENT

MS SQL Server can make use of the SELECT…INTO statement to create a new table and copies the data from an existing table into it. However, this command only copies the data and not the definition of it, thus, omitting constraints, indexes etc., if any. They need to be added separately if one wishes to have the exact same structure of the original table in their new table.

You can use the SELECT…INTO command to copy a table within the same database as well as across different databases.

Syntax

Following is the basic syntax of the SELECT…INTO statement −

SELECT*INTO new_table FROM original_table;

The above SQL command will create a table new_table using the structure of original_table and then it will copy all the data from original_table to new_table.

Example

Consider the following existing CUSTOMERS table which will be cloned in this section.

ID	NAME	AGE	ADDRESS	SALARY
1	Ramesh	32	Ahmedabad	2000.00
2	Khilan	25	Delhi	1500.00
3	Kaushik	23	Kota	2000.00
4	Chaitali	25	Mumbai	6500.00
5	Hardik	27	Bhopal	8500.00
6	Komal	22	Hyderabad	4500.00
7	Muffy	24	Indore	10000.00

Now, if you want to clone the data of this table into a new table NEW_CUSTOMERS, let’s use the following SQL query as shown below −

SELECT*INTO NEW_CUSTOMERS FROM CUSTOMERS;

Output

The output will be displayed as −

(7 rows affected)

Verification

To verify whether all the data has been copied into the new table NEW_CUSTOMERS, we shall use the SQL SELECT statement as follows −

SELECT*FROM NEW_CUSTOMERS;

If NEW_CUSTOMERS table is created successfully, then it should get all the records which are available in CUSTOMERS table.

SQL provides command to TRUNCATE a table completely in one go instead of deleting table records one by one which will be very time consuming and cumbersome process.

The SQL TRUNCATE TABLE Statement

The SQL TRUNCATE TABLE command is used to empty a table. This command is a sequence of DROP TABLE and CREATE TABLE statements and requires the DROP privilege.

You can also use DROP TABLE command to delete a table but it will remove the complete table structure from the database and you would need to re-create this table once again if you wish you store some data again.

Syntax

The basic syntax of a TRUNCATE TABLE command is as follows.

TRUNCATETABLE table_name;

Example

First let’s create a table CUSTOMERS which can store the personal details of customers including their name, age, address and salary etc. as shown below −

CREATETABLE CUSTOMERS (
   ID INTNOTNULL,
   NAME VARCHAR(20)NOTNULL,
   AGE INTNOTNULL,
   ADDRESS CHAR(25),
   SALARY DECIMAL(18,2),PRIMARYKEY(ID));

Now insert values into this table using the INSERT statement as follows −

INSERTINTO CUSTOMERS VALUES(1,'Ramesh',32,'Ahmedabad',2000.00),(2,'Khilan',25,'Delhi',1500.00),(3,'Kaushik',23,'Kota',2000.00),(4,'Chaitali',25,'Mumbai',6500.00),(5,'Hardik',27,'Bhopal',8500.00),(6,'Komal',22,'Hyderabad',4500.00),(7,'Muffy',24,'Indore',10000.00);

The table will be created as follows −

ID	NAME	AGE	ADDRESS	SALARY
1	Ramesh	32	Ahmedabad	2000.00
2	Khilan	25	Delhi	1500.00
3	Kaushik	23	Kota	2000.00
4	Chaitali	25	Mumbai	6500.00
5	Hardik	27	Bhopal	8500.00
6	Komal	22	Hyderabad	4500.00
7	Muffy	24	Indore	10000.00

Following SQL TRUNCATE TABLE CUSTOMER statement will remove all the records of the CUSTOMERS table −

TRUNCATETABLE CUSTOMERS;

Verification

Now, the CUSTOMERS table is truncated and the output from SELECT statement will be as shown in the code block below −

SELECT*FROM CUSTOMERS;

Following will be the output −

Empty set (0.00 sec)

TRUNCATE vs DELETE

Even though the TRUNCATE and DELETE commands work similar logically, there are some major differences that exist between them. They are detailed in the table below.

DELETE	TRUNCATE
The DELETE command in SQL removes one or more rows from a table based on the conditions specified in a WHERE Clause.	SQL’s TRUNCATE command is used to remove all of the rows from a table, regardless of whether or not any conditions are met.
It is a DML(Data Manipulation Language) command.	It is a DDL(Data Definition Language) command.
There is a need to make a manual COMMIT after making changes to the DELETE command, for the modifications to be committed.	When you use the TRUNCATE command, the modifications made to the table are committed automatically.
It deletes rows one at a time and applies same criteria to each deletion.	It removes all of the information in one go.
The WHERE clause serves as the condition in this case.	The WHERE Clause is not available.
All rows are locked after deletion.	TRUNCATE utilizes a table lock, which locks the pages so they cannot be deleted.
It makes a record of each and every transaction in the log file.	The only activity recorded is the deallocation of the pages on which the data is stored.
It consumes a greater amount of transaction space compared to TRUNCATE command.	It takes comparatively less amount of transaction space.
If there is an identity column, the table identity is not reset to the value it had when the table was created.	It returns the table identity to a value it was given as a seed.
It requires authorization to delete.	It requires table alter permission.
When it comes to large databases, it is much slower.	It is much faster.

TRUNCATE vs DROP

Unlike TRUNCATE that resets the table structure, DROP command completely frees the table space from the memory. They are both Data Definition Language (DDL) operations as they interact with the definitions of database objects; which allows the database to automatically commit once these commands are executed with no chance to roll back.

However, there are still some differences exist between these two commands, which have been summarized in the following table −

DROP	TRUNCATE
The DROP command in SQL removes an entire table from a database including its definition, indexes, constraints, data etc.	The TRUNCATE command is used to remove all of the rows from a table, regardless of whether or not any conditions are met and resets the table definition.
It is a DDL(Data Definition Language) command.	It is also a DDL(Data Definition Language) command.
The table space is completely freed from the memory.	The table still exists in the memory.
All the integrity constraints are removed.	The integrity constraints still exist in the table.
Requires ALTER and CONTROL permissions on the table schema and table respectively, to be able to perform this command.	Only requires the ALTER permissions to truncate the table.
DROP command is much slower than TRUNCATE but faster than DELETE.	TRUNCATE command is faster than both DROP and DELETE commands.

SQL provides two ways to rename an MySQL table. You can use either SQL RENAME TABLE or ALTER TABLE statement to change a table name in MySQL RDBMS.

The SQL RENAME TABLE Statement

You can change a MySQL table name using SQL RENAME TABLE statement.

Syntax

Following is the syntax of the SQL RENAME TABLE Statement −

RENAMETABLE table_name TO new_table_name;

Where, table_name is the current name of an existing table and new_table_name is the new name of the table.

Example: SQL RENAME TABLE Statement

Let us create a table with the name CUSTOMERS which contains the personal details of customers including their name, age, address and salary etc. as shown below −

CREATETABLE CUSTOMERS (
   ID INTNOTNULL,
   NAME VARCHAR(20)NOTNULL,
   AGE INTNOTNULL,
   ADDRESS CHAR(25),
   SALARY DECIMAL(18,2),PRIMARYKEY(ID));

Now, let us insert few records into this table using the INSERT statement as follows −

INSERTINTO CUSTOMERS VALUES(1,'Ramesh',32,'Ahmedabad',2000.00),(2,'Khilan',25,'Delhi',1500.00),(3,'Kaushik',23,'Kota',2000.00),(4,'Chaitali',25,'Mumbai',6500.00),(5,'Hardik',27,'Bhopal',8500.00),(6,'Komal',22,'Hyderabad',4500.00),(7,'Muffy',24,'Indore',10000.00);

The table will be created as follows −

ID	NAME	AGE	ADDRESS	SALARY
1	Ramesh	32	Ahmedabad	2000.00
2	Khilan	25	Delhi	1500.00
3	Kaushik	23	Kota	2000.00
4	Chaitali	25	Mumbai	6500.00
5	Hardik	27	Bhopal	8500.00
6	Komal	22	Hyderabad	4500.00
7	Muffy	24	Indore	10000.00

Following SQL Query changes the name of the CUSTOMERS table to BUYERS −

RENAMETABLE CUSTOMERS to BUYERS;

Verification

Once you change the name of a table, you can start using the new table name in your SQL queries.

SELECT*FROM BUYERS;

If table name got changed successfully, then it should list down all the records which were available in CUSTOMERS table.

The SQL ALTER TABLE Statement

The ALTER TABLE statement can be used to change or modify the structure of an existing table i.e. using this statement you can add/delete columns, create/destroy indexes, change the datatypes of the existing columns, rename the columns and, we can even rename the table.

Syntax

Following is the syntax of the SQL ALTER TABLE statement to rename an existing table −

ALTERTABLE table_name RENAME[TO|AS] new_table_name

Example: SQL ALTER TABLE Statement

Following SQL ALTER TABLE statement will change the table name from BUYERS to CUSTOMERS.

ALTERTABLE BUYERS RENAMETO CUSTOMERS;

Verification

Once you change the name of the table to CUSTOMERS, you can start using this name in your SQL queries.

SELECT*FROM CUSTOMERS;

This will produce the following result:

ID	NAME	AGE	ADDRESS	SALARY
1	Ramesh	32	Ahmedabad	2000.00
2	Khilan	25	Delhi	1500.00
3	Kaushik	23	Kota	2000.00
4	Chaitali	25	Mumbai	6500.00
5	Hardik	27	Bhopal	8500.00
6	Komal	22	Hyderabad	4500.00
7	Muffy	24	Indore	10000.00

Renaming a Table in SQL Server

There isn’t a query in SQL Server that can rename a table directly. However, it does give you access to a stored procedure called sp_rename that enables you to rename a table.

The sp_rename is a system stored procedure (set of pre-built subroutines that perform tasks within the database) in SQL that can be used to rename various database objects including tables, columns, indexes, and constraints.

Syntax

Following is the basic syntax to rename a table in SQL Server −

EXEC sp_rename 'old_table_name','new_table_name'

Here, you must ensure that old table name is present in the database and that new table name does not already exist. Otherwise, it will issue a warning. Second important point is to make sure that the table is not locked and there is no active transaction involving this table.

Example: Renaming a Table in SQL Server

Assume we already have the CUSTOMERS table in our database. Now, we are going to rename this table from CUSTOMERS to WORKERS, using the following query −

EXEC sp_rename 'CUSTOMERS','WORKERS';

Output

The result obtained is as shown below −

Completion time: 2023-08-15T19:21:49.1144163+05:30

Verification

We can verify whether the changes are reflected by retrieving its contents using the SELECT statement as follows −

SELECT*FROM WORKERS;

This will list down all the records available in WORKERS table as follows −

ID	NAME	AGE	ADDRESS	SALARY
1	Ramesh	32	Ahmedabad	2000.00
2	Khilan	25	Delhi	1500.00
3	Kaushik	23	Kota	2000.00
4	Chaitali	25	Mumbai	6500.00
5	Hardik	27	Bhopal	8500.00
6	Komal	22	Hyderabad	4500.00
7	Muffy	24	Indore	10000.00

Because you have renamed the table to WORKERS so if you try to get the details by using the old table name, it will throw an error showing that the table does not exist.

Rules to be followed while renaming tables

When renaming tables in SQL, there are some rules and best practices that should be followed to ensure that the renaming process goes smoothly and does not cause any unintended consequences or issues.

• Avoid renaming system tables − System tables are tables that are created and used by the database management system itself. Renaming these tables can cause issues with the functioning of the database system, so it is generally not recommended to rename system tables.
• Update all references to the table − After renaming a table, any stored procedures, views, triggers, or other database objects that reference the table will need to be updated to use the new name of the table. Failure to update these references can result in errors or issues with the functioning of the database system.
• Test thoroughly − Before renaming a table in a production environment, it is important to test the renaming process thoroughly in a development or testing environment to ensure that all references to the table have been updated correctly and that the database system continues to function as expected.
• Use a consistent naming convention − It is a good practice to use a consistent naming convention for tables and other database objects to make it easier to understand and maintain the database system. If you need to rename a table, consider following the same naming convention that you have used for other tables in the database.
• Backup the database − Before renaming a table, it is recommended to create a backup of the database to ensure that you have a restore point; in case anything goes wrong during the renaming process.

There are several instances when you need to retrieve a list of tables from your database. This could be done for testing purposes, to identify any existing tables before adding or removing any, or for any other reason. This tutorial will discuss how we can list down all the table in MySQL, SQL Server and Oracle using simple SQL commands.

MySQL – Listing Tables

You can use SQL SHOW TABLES statements in MySQL to list down all the tables available in a selected database.

Syntax

Following is the syntax to list all the tables in SQL in MySQL −

SHOWTABLES;

Example

Following is an example which will list down all the tables from a testDB database.

USE testDB;SHOWTABLES;

This will display the following output depending on the number of tables available in your database.

Tables_in_testDB
CALENDAR
CUSTOMERS
COMPANIES
SALARY

SQL Server – Listing Tables

SQL Server does not provide SHOW TABLE command in an SQL Server. Instead, we can use the “SELECT” statement to retrieve information about tables in a database. We have three different commands to use with the SELECT statement to list all the tables in a database −

• sys.tables
• information_schema.tables
• sysobjects

The SYS.TABLES View

Following is the syntax to list down all the tables in SQL using the SYS.TABLES view −

SELECT*FROM SYS.TABLES;

Following is the output of the above query −

name	object_id	principal_id	schema_id
CUSTOMER	4195065	NULL	1
ORDERS	68195293	NULL	1
COMPANIES	100195407	NULL	1
SALARY	2107154552	NULL	1

The INFORMATION_SCHEMA.TABLES View

Following is the syntax to list down all the tables in SQL using the INFORMATION_SCHEMA.TABLES view −

SELECT table_name, table_type FROM INFORMATION_SCHEMA.TABLES;

Following is the output of the above query −

table_name	table_type
CUSTOMER	BASE TABLE
ORDERS	BASE TABLE
COMPANIES	BASE TABLE
SALARY	BASE TABLE

The SYSOBJECTS View

You can use SYSOBJECTS view to retrieve the information of all the objects created in SQL Server database, including stored procedures, views, system tables and user-defined tables. Following is the basic syntax of using sysobjects view −

SELECT name, id, xtype FROM sysobjects WHERE xtype ='U';

Click here to see the different value of xtype

This will produce following result −

name	id	xtype
CUSTOMER	4195065	U
ORDERS	68195293	U
COMPANIES	100195407	U
SALARY	2107154552	U

Oracle – Listing Tables

There are following three SQL SELECT statements which you can use to list down the tables available in Oracle.

Listing ALL Tables

Following is the SQL SELECT statement which will list down all the available tables in an Oracle Database.

SELECT owner, table_name FROM ALL_TABLES

Listing DBA Tables

Following is the SQL SELECT statement which will list down all the DBA related tables in an Oracle Database.

SELECT owner, table_name FROM DBA_TABLES

Listing USER Tables

Following is the SQL SELECT statement which will list down all the USER created tables in an Oracle Database.

SELECT owner, table_name FROM USER_TABLES

Listing ALL Views

Following is the SQL SELECT statement which will list down all the views available in an Oracle Database.

SELECT view_name FROM ALL_VIEWS;

This tutorial will teach you how to use SQL to create tables in RDBMS. We use CREATE TABLE command to create a Table in a Database.

In RDBMS, Database tables are used to store the data in the form of some structures (fields and records). Here, a field is a column defining the type of data to be stored in a table and record is a row containing actual data. In simple words, we can say a Table is a combination of rows and columns.

SQL provides various queries to interact with the data in a convenient way. We can use SQL statements to create and delete tables, inserting, updating and deleting data in these tables.

For a more detail on different concepts related to RDBMS please check RDBMS Concepts tutorial.

The SQL CREATE TABLE Statement

SQL provides the CREATE TABLE statement to create a new table in a given database. An SQL query to create a table must define the structure of a table. The structure consists of the name of a table and names of columns in the table with each column’s data type. Note that each table must be uniquely named in a database.

Syntax

CREATE TABLE statement is used to create a new table in a database. −

CREATETABLE table_name(
   column1 datatype,
   column2 datatype,
   column3 datatype,.....
   columnN datatype,PRIMARYKEY( one or more columns));

Here are the key points-

• CREATE TABLE is the keyword telling the database system what you want to do. In this case, you want to create a new table. The unique name or identifier for the table follows the CREATE TABLE statement.
• The column parameters (e.g. column1, column2, column3, etc.) specify the names of the columns of the table.
• The datatype parameter specifies the type of data the column can hold (e.g. integer, varchar, string, etc.).
• PRIMARY KEY constraint uniquely identifies each record in a table. Primary keys must contain UNIQUE values, and cannot contain NULL values.

Example: Creating Table in SQL

CREATETABLE CUSTOMERS(
   ID          INTNOTNULL,
   NAME        VARCHAR(20)NOTNULL,
   AGE         INTNOTNULL,
   ADDRESS     CHAR(25),
   SALARY      DECIMAL(18,2),PRIMARYKEY(ID));

Here are the key points-

• The following code block is an example, which creates a CUSTOMERS table with column name ID, NAME, AGE, ADDRESS and, SALARY and ID as a primary key.
• NOT NULL are the constraints showing that these fields cannot be NULL while creating records in this table.

Verification

Once your table is created, you can check if it has been created successfully or not. You can use SQL DESC table_name command to list down the description of the table as follows:

DESC CUSTOMERS;

This will display the structure of the table created: column names, their respective data types, constraints (if any) etc.

Field	Type	Null	Key	Default	Extra
ID	int(11)	NO	PRI	NULL
NAME	varchar(20)	NO		NULL
AGE	int(11)	NO		NULL
ADDRESS	char(25)	YES		NULL
SALARY	decimal(18,2)	YES		NULL

Now, you have CUSTOMERS table available in your database which you can use to store the required information related to customers.

SQL CREATE TABLE IF NOT EXISTS

Consider a situation where you will try to create a table which already exists, in such situation MySQL will throw the following error.

ERROR 1050 (42S01): Table 'CUSTOMERS' already exists

So to avoid such error we can use SQL command CREATE TABLE IF NOT EXISTS to create a table.

Syntax

Following is the basic syntax of a CREATE TABLE IF NOT EXISTS statement −

CREATETABLEIFNOTEXISTS table_name(
   column1 datatype,
   column2 datatype,
   column3 datatype,.....
   columnN datatype,PRIMARYKEY( one or more columns));

Example: Creating Table if Not Exists

The following SQL command will create the CUSTOMERS table only when there is no table exists with the same name otherwise it will exit without any error.

CREATETABLEIFNOTEXISTS CUSTOMERS(
   ID          INTNOTNULL,
   NAME        VARCHAR(20)NOTNULL,
   AGE         INTNOTNULL,
   ADDRESS     CHAR(25),
   SALARY      DECIMAL(18,2),PRIMARYKEY(ID));

Creating a Table from an Existing Table

Instead of creating a new table every time, one can also copy an existing table and its contents including its structure, into a new table. This can be done using a combination of the CREATE TABLE statement and the SELECT statement. Since its structure is copied, the new table will have the same column definitions as the original table. Furthermore, the new table would be populated using the existing values from the old table.

Syntax

The basic syntax for creating a table from another table is as follows −

CREATETABLE NEW_TABLE_NAME ASSELECT[column1, column2...columnN]FROM EXISTING_TABLE_NAME
WHERE Condition;

Here, column1, column2… are the fields of the existing table and the same would be used to create fields of the new table.

Example: Creating Table from an Existing Table

Following is an example, which would create a table SALARY using the CUSTOMERS table and having the fields customer ID and customer SALARY −

CREATETABLE SALARY ASSELECT ID, SALARY
FROM CUSTOMERS;

This will create a new table SALARY which will have the following structure −

Field	Type	Null	Key	Default	Extra
ID	int(11)	NO	PRI	NULL
SALARY	decimal(18,2)	YES		NULL