Is it correct (acceptable), to name the table that holds the comments
made on suppliers: suppliers_comments, both plurals? I could have
named the table simply comments, but then it would have interfered
with other tables.
You can, But before RENAME of any tables name stop here & see the MySQL blog documentation here Before renaming a table, you should evaluate the impact thoroughly. For example, you should investigate which applications are using the table. If the name of the table changes, so the application code that refers to the table name needs to be changed as well. In addition, you must manually adjust other database objects such as views, stored procedures, triggers, foreign key constraints, etc., that reference to the table.
MySQL RENAME TABLE examples
First, we create a new database named hr that consists of two tables: employees and departments.
To create the hr database
CREATE DATABASE IF NOT EXISTS hr;
To create departments & employees tables
CREATE TABLE departments (
department_id INT AUTO_INCREMENT PRIMARY KEY,
CREATE TABLE employees (
id int AUTO_INCREMENT primary key,
first_name varchar(50) not null,
last_name varchar(50) not null,
department_id int not null,
FOREIGN KEY (department_id)
REFERENCES departments (department_id)
Second, we insert sample data into both employees and departments tables:
INSERT INTO departments(dept_name)
INSERT INTO employees(first_name,last_name,department_id)
Third, we review our data in the departments and employees tables:
id, first_name, last_name, department_id
Renaming a table referenced by a view
If the table that you are going to rename is referenced by a view, the view will become invalid if you rename the table, and you have to adjust the view manually.
For example, we create a view named v_employee_info based on the employees and departments tables as follows:
CREATE VIEW v_employee_info as
id, first_name, last_name, dept_name
departments USING (department_id);
The views use the inner join clause to join departments and employees tables.
The following SELECT statement returns all data from the v_employee_info view.
Now we rename the employees to people table and query data from the v_employee_info view again.
RENAME TABLE employees TO people;
MySQL returns the following error message:
Error Code: 1356. View 'hr.v_employee_info' references invalid
table(s) or column(s) or function(s) or definer/invoker of view lack
rights to use them
We can use the CHECK TABLE statement to check the status of the v_employee_info view as follows:
CHECK TABLE v_employee_info;
We need to manually change the
v_employee_info view so that it refers to the people table instead of the employees table.
Renaming a table that referenced by a stored procedure
In case the table that you are going to rename is referenced by a stored procedure, you have to manually adjust it like you did with the view.
Note: Here I have taken all codes & pictures from the
mysqltutorial.org to clarify the RENAME of table.
After edit about the Naming Conventions for MySQL
- Case sensitivity is set when a server is installed. By default, the
names of database objects are case sensitive on UNIX and not case
sensitive on Windows. For example, the names CUSTOMER and Customer
are different on a case-sensitive server.
- Database names must be unique. For each user within a database, names
of database objects must be unique across all users. For example, if
a database contains a department table that User A created, no other
user can create a department table in the same database.
- A name cannot be a reserved word in MySQL unless you enclose the name
in quotation marks. See
the MySQL documentation for more information about reserved words.
Mr. Max Vernon already said that here Database names are completely up to the person creating them. Having said that, it makes sense to use some kind of sane naming convention.
For further your ref here