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.

Many a times you face a situation where you need to list down all the available databases. MySQL provides a convenient way to list down all the databases using SHOW DATABASES command where as there is no particular command in MS SQL Server to show or list the databases but, you can use the SELECT…FROM command as a work around list down available databases.

List Databases using SQL

The SQL SHOW DATABASES statement is used to list down all the available databases in MySQL database. You can use SHOW SCHEMAS as an alternate command for SHOW DATABASES.

Syntax

Following is the syntax of SQL SHOW DATABASES to list down all the available databases in MySQL −

SHOW DATABASES [LIKE ''pattern'' | WHERE expr] ;

We can use LIKE or WHERE clause along with SHOW DATABASES to filter out a list of databases.

Example

Following is an example to list down all the available databases.

SHOW DATABASES;

The output will be displayed as follows. This output depends on the number of databases available in the system −

Following is an example to list down all the databases whose name starts with test.

SHOW DATABASES LIKE ''test%

The output will be displayed as follows −

The SHOW SCHEMAS Statement

You can use the SHOW SCHEMAS statement as an alternate for the SHOW DATABASES statement.

Syntax

Following is the syntax of the SQL SHOW SCHEMAS statement to list down all the available databases in MySQL −

SHOW SCHEMAS [LIKE ''pattern'' | WHERE expr] ;

We can use LIKE or WHERE clause along with SHOW SCHEMAS to filter out a list of databases.

Example

Following is an example to list down all the available databases.

SHOW SCHEMAS;

The output will be displayed as follows. This output depends on the number of databases available in the system −

Following is an example to list down all the databases whose name starts with test.

SHOW SCHEMAS LIKE ''test%

The output will be displayed as follows −

The SELECT…FROM Statement

If you are working with MS SQL Server then you can use the SELECT…FROM statement to list down all the available databases as shown below.

SQL> SELECT * FROM sys.databases;

Output

If we execute the above query, it returns a table that lists down all the databases and associated information about the databases.

The EXEC sp_databases Statement

If you are using MS SQL Server then you can use the following EXEC sp_databases statement to list down all the databases −

SQL> EXEC sp_databases;

Output

This will display the same result as we got from SELECT…FROM statement.

To work with a database in SQL, we need to first select the database we want to work with. After selecting the database, we can perform various operations on it such as creating tables, inserting data, updating data, and deleting data.

The USE DATABASE Statement

The SQL USE DATABASE statement is used to select a database from a list of databases available in the system. Once a database is selected, we can perform various operations on it such as , , updating data, and .

Syntax

Following is the syntax of the USE DATABASE statement in SQL −

USE DatabaseName;

Here, the DatabaseName is the name of the database that we want to select. The database name is always unique within the RDBMS.

Example

First of all we will create a database using the following SQL CREATE DATABASE query −

CREATE DATABASE testDB;

Now, we can list all the available databases as follws −

SHOW DATABASES;

The output will be displayed as −

Example: Select/Switch Database

Following query is used to select/switch the current database to testDB −

USE testDB;

Output

Database changed

Once we finish switching to the database testDB we can perform operations such as creating a table, and inserting data in that table as shown below −.

CREATE TABLE CALENDAR(MONTHS DATE NOT NULL);

Now, let us insert some records in the CALENDAR table using SQL INSERT statements as shown in the query below −

INSERT INTO CALENDAR(MONTHS) VALUES(''2023-01-01'');
INSERT INTO CALENDAR(MONTHS) VALUES(''2023-02-01'');
INSERT INTO CALENDAR(MONTHS) VALUES(''2023-03-01'');
INSERT INTO CALENDAR(MONTHS) VALUES(''2023-04-01'');
INSERT INTO CALENDAR(MONTHS) VALUES(''2023-12-01'');

Let”s verify the operation by listing all the records from CALENDAR table using SQL SELECT statement as shown below −

SELECT * FROM CALENDAR;

Output

The output will be displayed as −

Selecting a Non Existing Database

An attempt to select a non-existent database will result in an error. In the following query we are trying to switch to the database which does not exist −

Example

USE unknownDatabase;

Output

On executing the above query, the output will be displayed as −

ERROR 1049 (42000): Unknown database ''unknownDatabase''

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.

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

CREATE TABLE table_name(
   column1 datatype,
   column2 datatype,
   column3 datatype,
   .....
   columnN datatype,
   PRIMARY KEY( 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

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

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.

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

CREATE TABLE IF NOT EXISTS table_name(
   column1 datatype,
   column2 datatype,
   column3 datatype,
   .....
   columnN datatype,
   PRIMARY KEY( 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.

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

CREATE TABLE NEW_TABLE_NAME AS
SELECT [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 −

CREATE TABLE SALARY AS
SELECT ID, SALARY
FROM CUSTOMERS;

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

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