MariaDB Rename View [Complete tutorial]

In this MariaDB tutorial, we will look at how to rename a view in MariaDB and we will also look at several examples related to it. There are lists of the topic that comes under discussion:

• MariaDB rename view
• MariaDB rename view example
• MariaDB rename view error
• MariaDB rename view exists
• MariaDB rename view foreign key
• MariaDB rename view name
• MariaDB rename view primary key

MariaDB Rename View

In this section, we will understand how to the RENAME VIEW in MariaDB by using the ALTER TABLE statement and we will discuss its syntax and an example.

Both the table and the view have the same domain in MariaDB. As a result, we can rename a view using the RENAME TABLE query.

Here, is the fundamental syntax to rename a view by the following query:

SYNTAX:

ALTER TABLE TABLE_NAME RENAME VIEW_NAME TO NEW_VIEW_NAME;

The syntax explanation:

• After the RENAME TABLE keyword, we’ll specify the view name we want to alter.
• Then, after the TO keyword, define the view’s new name.

Another technique to rename a view is to use the DROP VIEW and CREATE VIEW statements in that order.

• To begin, use the SHOW CREATE VIEW statement in MariaDB to retrieve the CREATE VIEW statement.
• After that, create a view with the CREATE VIEW statement and save it to a file.
• Then, using the DROP VIEW statement, delete the view.
• Then, in the CREATE VIEW statement, change the name of the view.
• Execute the CREATE VIEW statement to create a new view at the end.

NOTE:

We can migrate a view from one database to another by utilising the DROP VIEW and CREATE VIEW statements in that order.

For this example, we’ll build a new view called LAST_NAME:

CREATE VIEW LAST_NAME AS 
SELECT * FROM company WHERE 
EMP_LASTNAME LIKE "%s";

In this query, we have created a view as LAST_NAME on the SELECT query by using the CREATE VIEW statement.

The sample example to rename a view by using the ALTER TABLE statement is given below:

EXAMPLE:

RENAME TABLE LAST_NAME TO EMPLOYEE_LASTNAME;

SHOW FULL TABLES WHERE TABLE_TYPE='VIEW';

In this query, we have renamed the view from LAST_NAME to EMPLOYEE_LASTNAME by using the RENAME TABLE statement. After that, if you want to view the new name then use the SHOW FULL TABLES statement.

Read: MariaDB Drop Table

MariaDB Rename View Example

In this sub-topic, we will understand how to rename the view and which is explained with the help of another example.

Let’s say we wish to rename the view COMPANY_EMPLASTNAME from EMPLOYEE_LASTNAME. For the view definition, we’ll use the SHOW CREATE VIEW statement:

SHOW CREATE VIEW EMPLOYEE_LASTNAME;

Using the SHOW CREATE VIEW statement, we can duplicate the statement from the CREATE VIEW column in the above query.

The sample example to rename view by using the DROP VIEW and CREATE VIEW statement is given below:

EXAMPLE:

DROP VIEW EMPLOYEE_LASTNAME;

CREATE VIEW COMPANY_EMP_LASTNAME AS 
SELECT * FROM COMPANY 
WHERE EMP_LASTNAME LIKE "%s";

SHOW FULL TABLES WHERE TABLE_TYPE='VIEW';

This is another method to rename a view by using the DROP VIEW and CREATE VIEW statements. In the first query, we have used the DROP VIEW statement to drop the old view_name as EMPLOYEE_LASTNAME.

And then in the second query, we have used the CREATE VIEW statement to create a new view_name as COMPANY_EMP_LASTNAME on the same query of the SELECT statement in the COMPANY table.

If we want to check whether the name of the view has been changed or not then use the SHOW FULL TABLES statement.

Read: How to Drop Column from MariaDB Table

MariaDB rename view error

In this section, we will discover how to solve a general error related to renaming a view in MariaDB and we will try to illustrate this error using an example.

The sample example to rename view error is given below:

EXAMPLE:

CREATE VIEW FIRST_NAME AS 
SELECT * FROM company
WHERE GENDER='MALE';

RENAME TABLE LAST_NAME TO EMPLOYEE_LASTNAME;

SHOW FULL TABLES WHERE TABLE_TYPE='VIEW';

As we see in the above query, we have got an error in the second query. So, normally error arises due to syntax or documentation aren’t up to mark in the MariaDB.

In this next section, we will discuss how to handle the above-mentioned error.

Read: MariaDB Queries – Detailed Guide

MariaDB Rename View Exists

In this sub-topic, we will discover how to rename the view with the IF EXISTS clause in MariaDB. And it is explained with the help of syntax and an example.

When the MariaDB IF EXISTS condition is applied to an outer query, it is considered to be “met” if at least one result is provided. It can be used in a SELECT, INSERT, UPDATE or DELETE statement.

The syntax to rename view with the IF EXISTS clause by using the following query:

SYNTAX:

REANME TABLE IF EXISTS existing_tbl_name TO new_tbl_name;

Let’s understand how IF EXISTS clause in MariaDB helps to handle the error messages related to the existence of a view.

RENAME TABLES IF EXISTS LAST_NAME TO EMPLOYEE_lastname;

In the above query, we are trying to rename a view named LAST_NAME to EMPLOYEE_lastname. But, again, the LAST_NAME view does not exist in the database. However, as we are using the IF EXISTS clause with the RENAME TABLE statement, it will return a warning in place of an error.

Now, the proper solution to the above warning is to specify the exact existing view name. So, next, we will illustrate the use of the IF EXISTS clause with a correct view name.

EXAMPLE:

CREATE VIEW LASTNAME AS 
SELECT * FROM company
WHERE GENDER='MALE';

RENAME TABLES LASTNAME TO emp_lastname ;

SHOW FULL TABLES WHERE TABLE_TYPE='VIEW';

In the above query, there was a syntax error in the RENAME TABLES statement. In the previous topic query, the TABLES were written as RENAME TABLE statement due to which dialogue box came and said that “TABLE.LAST_NAME DOESN’T EXIST”.

Read: MariaDB IF Tutorial

MariaDB rename view foreign key

In this section, we will discuss how to rename the view having a foreign key column in the MariaDB and it is explained with the help of an example.

An example to rename the view of the foreign key column in the MariaDB is given below:

EXAMPLE:

CREATE VIEW empID AS 
SELECT * FROM company
WHERE emp_id>=5;

RENAME TABLES empID TO FK_employeeID;

SHOW FULL TABLES WHERE TABLE_TYPE='VIEW';       

In the first query, we have created a view as empID on the query by using the CREATE VIEW statement. Then, due to some reason the user wanted to change the view_name from empID to the FK_employeeID, we will use the RENAME TABLES statement.

If we want to check whether the view_name has been changed or not then use the SHOW FULL TABLES statement.

Read: MariaDB Foreign Key + Examples

MariaDB rename view name

In the sub-topic, we want to rename the view as Name in the statement and it is explained with the help of an example.

The sample example to rename view as Name by using the following query:

EXAMPLE:

CREATE VIEW EMPLOYEE_FIRSTNAME AS 
SELECT * FROM company
WHERE EMP_FIRSTNAME LIKE '%s';

RENAME TABLES EMPLOYEE_FIRSTNAME TO NAME;

SHOW FULL TABLES WHERE TABLE_TYPE='view';

In the first query, we have created a view as EMPLOYEE_FIRSTNAME on the query that we want to execute. And in the second query, the user wants to change the view_name from EMPLOYEE_FIRSTNAME to NAME for that we use the RENAME TABLES statement.

If we want to check the new view_name then we will use the SHOW FULL TABLES statement.

Read: MariaDB Truncate Table + Examples

MariaDB rename view primary key

In this section, we will know how to rename the view of the primary key column and which is explained with the help of an example.

EXAMPLE:

SELECT * FROM company;
CREATE VIEW COMPANYID AS 
SELECT * FROM company 
WHERE GENDER='BIGENDER';

RENAME TABLES COMPANYID TO PK_COMPANY_ID;

SHOW FULL TABLES WHERE TABLE_TYPE='view';

In the preceding query, we have created a view_name as COMPANYID on the primary key of the column as COMPANY_ID column of the COMPANY table by using the CREATE VIEW statement.

Suppose the user wants to change the view_name from COMPANYID to PK_COMPANY_ID then we will use the RENAME TABLES statement.

If we aren’t sure whether the new view_name has been changed or not then we will use the SHOW FULL TABLES statement with the WHERE condition. In the condition we are trying to see the new view_name for that we have used the table_type=view.

Also, take a look at some more MariaDB tutorials.

• MariaDB Rename Column + Examples
• MariaDB Insert Into Select
• MariaDB Create Sequence + Examples
• How to Create Function in MariaDB
• MariaDB JSON Function + Examples
• MariaDB Median – Complete Tutorial
• MariaDB Reserved Words – Helpful Tutorial

In this MariaDB tutorial, we have discussed how to rename a view in MariaDB using various examples. There are lists of the topic that comes under discussion:

• MariaDB rename view
• MariaDB rename view example
• MariaDB rename view error
• MariaDB rename view exists
• MariaDB rename view foreign key
• MariaDB rename view name
• MariaDB rename view primary key