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 −

SHOW TABLES;

Example

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

USE testDB;

SHOW TABLES;

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

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 −

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 −

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

Value	Meaning
AF	Aggregate function (CLR)
C	CHECK constraint
D	Default or DEFAULT constraint
F	FOREIGN KEY constraint
L	Log
FN	Scalar function
FS	Assembly (CLR) scalar-function
FT	Assembly (CLR) table-valued function
IF	In-lined table-function
IT	Internal table
P	Stored procedure
PC	Assembly (CLR) stored-procedure
PK	PRIMARY KEY constraint (type is K)
RF	Replication filter stored procedure
S	System table
SN	Synonym
SQ	Service queue
TA	Assembly (CLR) DML trigger
TF	Table function
TR	SQL DML Trigger
TT	Table type
U	User table
UQ	UNIQUE constraint (type is K)
V	View
X	Extended stored procedure

This will produce following result −

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;

There can be several reasons to rename a database name. One of the reasons could be to avoid naming conflicts or to separate different types of data into different databases. Another reason can be to arrange them in an organized way which makes them more descriptive and easier to manage.

The ALTER DATABASE…MODIFY Statement

The ALTER DATABASE…MODIFY statement in SQL is used to rename the name of an existing database name in SQL Database Server. Please note that this command does not work in MySQL database.

Syntax

Following is the syntax of the ALTER DATABASE…MODIFY command −

ALTER DATABASE OldDatabaseName MODIFY NAME = NewDatabaseName;

Example

Following is the SQL command in SQL Server to rename the database testDB to tutorialsDB:

ALTER DATABASE testDB MODIFY NAME = tutorialsDB ;

Rename Database using Dump and Reimport

If you are willing to rename a database name in , then simple way is to dump the complete database in an SQL file and then re-import it into a new database. This is three step process which we will follow in this tutorial:

Step 1 – Dump Old Database

Consider you want to rename testDB database to tutorialsDB. So first we will dump it in a simple SQL file using MySQL mysqldump command. This operation will be performed from command line and will require a database user name and password, preferably admin privilege.

$ mysqldump -u username -p"password" -R testDB > testDB.sql

We are using the -p flag immediately followed by our password to connect to the database with no space between. The -R is required to tell mysqldump to copy stored procedures and functions along with the normal data from the database.

Step 2 – Create New Database

Next step is to using mysqladmin prompt command as follows:

$ mysqladmin -u username -p"password" create tutorialsDB;

Step 3 – Import Old Database

The final step is to import old database into new database as follwing:

$ mysql -u username -p"password" tutorialsDB < testDB.sql;

Step 4 – Verification (Optional)

Now you can verify the changes by listing down all the available databases:

SHOW DATABASES;

Output

The output will be displayed as −

Step 5 – Verification (Optional)

Once you are satisfied with your changes, you can delete your old database as follows:

DROP DATABASE testDB;

Rename Database in SQL using RENAME DATABASE…TO (obsoleted)

SQL provides a simple RENAME DATABASE…TO statement to rename an existing database. If you want to rename a database, make sure there is no active transaction in progress otherwise the complete operation might halt once you rename the database.

Note: The RENAME DATABASE…TO is obsoleted.

Syntax

Following is the syntax of the RENAME DATABASE…TO statement −

RENAME DATABASE OldDatabaseName TO NewDatabaseName;

Example

Before renaming a database, let us list down all the available databases −

SHOW DATABASES;

The output will be displayed as −

Now, issue the following command to rename the database testDB to tutorialsDB:

RENAME DATABASE testDB TO tutorialsDB;

There used to be a simple RENAME DATABASE command in older versions of MySQL which was intended to rename database but RENAME DATABASE command has been removed from all newer versions to avoid security risks.

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 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.

TRUNCATE TABLE 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 −

CREATE TABLE CUSTOMERS (
   ID INT NOT NULL,
   NAME VARCHAR (20) NOT NULL,
   AGE INT NOT NULL,
   ADDRESS CHAR (25),
   SALARY DECIMAL (18, 2),
   PRIMARY KEY (ID)
);

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

INSERT INTO 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 −

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

TRUNCATE TABLE 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.

TRUNCATE vs DROP

Unlike TRUNCATE that resets the table structure, 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 −

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−

CREATE TABLE new_table SELECT * FROM original_table;

Example

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

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

CREATE TABLE 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 −

CREATE TABLE new_table LIKE original_table;

Example

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

CREATE TABLE 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 −

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 −

CREATE TABLE new_table LIKE original_table;
INSERT INTO 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.

CREATE TABLE 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.

INSERT INTO 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.

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 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 −

RENAME TABLE 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 with the name CUSTOMERS which contains the personal details of customers including their name, age, address and salary etc. as shown below −

CREATE TABLE CUSTOMERS (
   ID INT NOT NULL,
   NAME VARCHAR (20) NOT NULL,
   AGE INT NOT NULL,
   ADDRESS CHAR (25),
   SALARY DECIMAL (18, 2),
   PRIMARY KEY (ID)
);

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

INSERT INTO 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 −

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

RENAME TABLE 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 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 −

ALTER TABLE 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.

ALTER TABLE BUYERS RENAME TO 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:

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 −

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 , , 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 to ensure that you have a restore point; in case anything goes wrong during the renaming process.

In this SQL Backup Database tutorial, we will explain how we can take a backup of a database in and . It is very important and basic development practice to have a backup of the database in case the original is corrupted or lost due to power surges or disk crashes etc. By practicing this, the database can be recovered as it was before the failure.

SQL Backup Database Statement

In SQL, the BACKUP DATABASE statement is used to create database backups.

Syntax

Following is the syntax of SQL Backup Database Statement −

BACKUP DATABASE database_name
TO DISK = ''filepath''
GO

Here, the SQL command creates a backup file of the database_name database.

Types of Backups in SQL

In SQL, there are three types of database backups available. These are:

• Full Backup
• Differential Backup
• Transaction Log (T-log) backup

SQL Full Backup

A full backup is a complete backup of an SQL server database.

Syntax

Following is the syntax of SQL Full Backup −

BACKUP DATABASE database_name
TO medium = ''filepath''
GO

Here, database_name is the name of the database, medium refers to the storage medium such as disk, tape or url.

SQL Differential Backup

In Sql, you can also backup only the new changes by using the WITH DIFFERENTIAL command.

Syntax

Following is the syntax of Sql Differential Backup −

BACKUP DATABASE my_db
TO medium = ''filepath''
WITH DIFFERENTIAL;
GO

Here, database_name is the name of the database, medium refers to storage device such as disk, tape or url.

Transaction Log (T-log) backup

A transaction log backup includes all the transactions since the last transaction log backup. BACKUP LOG comnmand is used to perfom the Transaction Log backup.

Syntax

Following is the syntax of Sql transaction log backup −

BACKUP LOG database_name
TO medium = ''filepath
GO

Here, database_name is the name of the database, medium refers to storage device such as disk

Restore Database From Backup

To restore a backup file in Database, we can use the RESTORE DATABASE command.

Syntax

Following is the syntax of Restore Database From Backup −

RESTORE DATABASE database_name
FROM DISK = ''filepath
GO

Here, database_name is the name of the database, medium refers to disk, tape or url.

MySQL and MS SQL Database Backup and Restore

Here is the process to create backup in MySQL and MS Sql databases.

Backup MySQL Database

MySQL mysqldump command can be used to take complete backup of a given database. This operation will be performed from command line and will require database user name and password, preferably admin privilege.

$ mysqldump -u username -p"password" -R testDB > testDB.sql

We are using the -p flag immediately followed by our password to connect to the database with no space between. The -R is required to tell mysqldump to copy stored procedures and functions along with the normal data from the database.

Depending on the database size, above command may take sometime to create a final output file testDB.sql. Once command is completed, you will have a complete database dump in testDB.sql file which you can keep safe anywhere you like. Later this file can be used to restore the database.

Restore MySQL Database

If we have a database dump then we can use the following two step process to restore our database. First step is to create our new database using mysqladmin prompt command as follows:

$ mysqladmin -u username -p"password" create tutorialsDB;

The next step is to import old database into new database shown below :

$ mysql -u username -p"password" tutorialsDB < testDB.sql;

If you want to keep your database name same as the old one then you will have to drop old database and then re-create it before importing old data into this database, but make sure you don”t have any data in this database which you do not want to loose.

Backup MS SQL Database

If you are working with MS SQL Server then to create a backup for an existing database, SQL provides us with a simple SQL BACKUP DATABASE command.

Syntax

Following is the syntax of the BACKUP DATABASE command in SQL −

BACKUP DATABASE database_name
TO DISK = ''filepath''
GO

Example

Following is an example to create a backup file for the database testDB on D drive.

SQL> BACKUP DATABASE testDB
TO DISK = ''D:testDB.bak''
GO

To perform a backup or restore you should have admin sysadmin privileges. You should also back up the database onto a different disk other than the actual database. Even if the disk crashes, we will not lose our backup file along with the database.

Output

When we execute the above query, the output is obtained as follows −

Processed 344 pages for database ''testDB'', file ''testDB'' on file 1.
Processed 2 pages for database ''testDB'', file ''testDB_log'' on file 1.
BACKUP DATABASE successfully processed 346 pages in 0.011 seconds (245.383 MB/sec).

Restore MS SQL Database

If you have a proper backup of an MS SQL database then youc an easily restore it when needed.

Syntax

Following is the syntax of the RESTORE DATABASE command in SQL −

RESTORE DATABASE database_name
FROM DISK = ''filepath''
[WITH REPLACE]
GO

Here WITH REPLACE option can be given if you want to overwrite the existing database.

Example

Following is an example to restore a database from a backup file testDB.bak available on D drive.

SQL> RESTORE DATABASE testDB
FROM DISK = ''D:testDB.bak''
WITH REPLACE
GO

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 −

DROP TABLE table_name;

Example

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

CREATE TABLE CUSTOMERS(
   ID          INT NOT NULL,
   NAME        VARCHAR (20) NOT NULL,
   AGE         INT NOT NULL,
   ADDRESS     CHAR (25),
   SALARY      DECIMAL (18, 2),
   PRIMARY KEY (ID)
);

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

DESC Table

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

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

DROP TABLE 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 −

DROP TABLE [IF EXISTS] 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 −

DROP TABLE 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 −

DROP TABLE IF EXISTS 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

DROP TEMPORARY TABLE TEMP_TABLE;

Example

Following is an example to delete a temporary table CUSTOMERS.

DROP TEMPORARY TABLE CUSTOMERS;

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 −

CREATE TEMPORARY TABLE table_name(
   column1 datatype,
   column2 datatype,
   column3 datatype,
   .....
   columnN datatype,
   PRIMARY KEY( one or more columns )
);

Example

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

CREATE TEMPORARY TABLE CUSTOMERS(
   ID   INT              NOT NULL,
   NAME VARCHAR (20)     NOT NULL,
   AGE  INT              NOT NULL,
   ADDRESS  CHAR (25) ,
   SALARY   DECIMAL (18, 2),
   PRIMARY KEY (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 −

INSERT INTO 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 −

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:

DROP TEMPORARY TABLE table_name;

Example

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

DROP TEMPORARY TABLE 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 −

CREATE TABLE #table_name(
   column1 datatype,
   column2 datatype,
   column3 datatype,
   .....
   columnN datatype,
   PRIMARY KEY( one or more columns )
);

Example of the Local Temporary Tables

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

CREATE TABLE #CUSTOMERS(
   ID   INT              NOT NULL,
   NAME VARCHAR (20)     NOT NULL,
   AGE  INT              NOT NULL,
   ADDRESS  CHAR (25) ,
   SALARY   DECIMAL (18, 2),
   PRIMARY KEY (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 −

CREATE TABLE ##table_name(
   column1 datatype,
   column2 datatype,
   column3 datatype,
   .....
   columnN datatype,
   PRIMARY KEY( one or more columns )
);

Example of the Global Temporary Tables

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

CREATE TABLE ##Buyers(
   ID   INT              NOT NULL,
   NAME VARCHAR (20)     NOT NULL,
   AGE  INT              NOT NULL,
   ADDRESS  CHAR (25) ,
   SALARY   DECIMAL (18, 2),
   PRIMARY KEY (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.

DROP TABLE #Customers;

Whereas, following query removes the global temporary table Buyers.

DROP TABLE ##Buyers;

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

Syntax

Following is the basic syntax of an ALTER TABLE command −

ALTER TABLE 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 −

ALTER TABLE table_name ADD column_name datatype;

Example

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

ALTER TABLE 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 −

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.

ALTER TABLE table_name DROP COLUMN column_name;

Example

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

ALTER TABLE CUSTOMERS DROP COLUMN 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.

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 −

ALTER TABLE table_name
ADD INDEX index_name [index_type]

Example

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

ALTER TABLE CUSTOMERS ADD INDEX 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 −

ALTER TABLE table_name DROP INDEX index_name;

Example

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

ALTER TABLE CUSTOMERS DROP INDEX 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 −

ALTER TABLE table_name
ADD CONSTRAINT constraint_name
PRIMARY KEY (column1, column2...);

Example

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

CREATE TABLE EMPLOYEES(
   ID          INT NOT NULL,
   NAME        VARCHAR (20) NOT NULL,
   AGE         INT NOT NULL,
   ADDRESS     CHAR (25),
   SALARY      DECIMAL (18, 2)
);

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

ALTER TABLE EMPLOYEES
ADD CONSTRAINT MyPrimaryKey
PRIMARY KEY(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.

ALTER TABLE − DROP PRIMARY KEY

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

ALTER TABLE table_name DROP PRIMARY KEY;

Example

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

ALTER TABLE EMPLOYEES DROP PRIMARY KEY;

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 −

ALTER TABLE table_name
ADD CONSTRAINT constraint_name
UNIQUE(column1, column2...);

Example

Following query adds UNIQUE constraint to the table CUSTOMERS −

ALTER TABLE EMPLOYEES ADD CONSTRAINT 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 −

ALTER TABLE table_name DROP CONSTRAINT constraint_name;

Example

Following query adds UNIQUE constraint to the table CUSTOMERS −

ALTER TABLE EMPLOYEES DROP CONSTRAINT 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 −

ALTER TABLE table_name
RENAME COLUMN old_column_name to new_column_name;

Example

Following query renames NAME column in table CUSTOMERS −

ALTER TABLE CUSTOMERS RENAME COLUMN 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

ALTER TABLE table_name ALTER COLUMN column_name datatype;

MySQL Syntax

ALTER TABLE table_name MODIFY COLUMN column_name datatype;

Oracle Syntax

ALTER TABLE table_name MODIFY COLUMN column_name datatype;

Example

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

ALTER TABLE CUSTOMERS MODIFY COLUMN ID DECIMAL(18, 4);

This will produce the following output −

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

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.

CREATE TABLE 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:

CREATE TABLE CUSTOMERS (
   ID INT NOT NULL,
   NAME VARCHAR (20) NOT NULL,
   AGE INT NOT NULL,
   ADDRESS CHAR (25),
   SALARY DECIMAL (18, 2)
);

Check further detail on

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.

CREATE TABLE CUSTOMERS (
   ID INT NOT NULL UNIQUE,
   NAME VARCHAR (20) NOT NULL,
   AGE INT NOT NULL,
   ADDRESS CHAR (25),
   SALARY DECIMAL (18, 2)
);

Check further detail on

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.

CREATE TABLE CUSTOMERS (
   ID INT NOT NULL UNIQUE,
   NAME VARCHAR (20) DEFAULT ''Not Available'',
   AGE INT NOT NULL,
   ADDRESS CHAR (25),
   SALARY DECIMAL (18, 2)
);

Check further detail on

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.

CREATE TABLE CUSTOMERS(
   ID          INT NOT NULL,
   NAME        VARCHAR (20) NOT NULL,
   AGE         INT NOT NULL,
   ADDRESS     CHAR (25),
   SALARY      DECIMAL (18, 2),
   PRIMARY KEY (ID)
);

Check further detail on

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 −

CREATE TABLE ORDERS (
   ID INT NOT NULL,
   DATE DATETIME,
   CUSTOMER_ID INT FOREIGN KEY REFERENCES CUSTOMERS(ID),
   AMOUNT DECIMAL,
   PRIMARY KEY (ID)
);

Check further detail on

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.

CREATE TABLE CUSTOMERS(
   ID          INT NOT NULL,
   NAME        VARCHAR (20) NOT NULL,
   AGE         INT NOT NULL CHECK(AGE>=18),
   ADDRESS     CHAR (25),
   SALARY      DECIMAL (18, 2),
   PRIMARY KEY (ID)
);

Check further detail on

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.

CREATE INDEX idx_age ON CUSTOMERS ( AGE );

Check further detail on

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.

ALTER TABLE CUSTOMERS DROP CONSTRAINT PRIMARY KEY;

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.