CODEWITHSUNDEEP
mysqldatabaseview
Roshan Wijesena
Roshan Wijesena

Reputation: 3136

View and table in same name is it possible

Is it possible to create a mysql view and table in same name

for example i have a table hs_hr_employee i want create a view as a same name

create VIEW hs_hr_employee AS SELECT * from hs_hr_employee;

I m getting following error

#1050 - Table 'hs_hr_employee' already exists

Any help Thankful

Regards

Upvotes: 17

Views: 21355

Answers (4)

Sahil Raj
Sahil Raj

Reputation: 21

Not only in mysql server but in any sqldialect, tables and views in the same schema can't be with the same name as it will lead to ambiguity when you fire a select query like from where to select the columns either from table or view.

Upvotes: 0

fardin
fardin

Reputation: 1

Yes, if you create it under a different schema.

for example

dbo.Customers

user.customers (this could be a view)

You can call it by including the schema or if you call it from a user that is assigned the schema.

This is for MS SQL server.

Upvotes: 0

Pacerier
Pacerier

Reputation: 89623

As stated, you can't do it with views, but you can with temporary tables.

If you create a temporary table with the same name as an actual table, the temporary table will shadow (hide) the actual table. This means that you can't access the actual table until you have dropped the temporary table:

mysql> create table t select 1; # actual table t
Query OK, 1 row affected (0.58 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> create temporary table t select*from t; # temp table t
Query OK, 1 row affected (0.53 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> insert t select 2; # t refers to the temp table
Query OK, 1 row affected (0.06 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> select*from t; # temp table
+---+
| 1 |
+---+
| 1 |
| 2 |
+---+
2 rows in set (0.00 sec)

mysql> drop table t; # temp table
Query OK, 0 rows affected (0.06 sec)

mysql> show tables like "t"; # verify that actual table still exists. show tables will not show temporary tables
+--------------------+
| Tables_in_test (t) |
+--------------------+
| t                  |
+--------------------+
1 row in set (0.00 sec)

mysql>

mysql> select*from t; # now t refers to the actual table
+---+
| 1 |
+---+
| 1 |
+---+
1 row in set (0.00 sec)

mysql> drop table t; # actual table
Query OK, 0 rows affected (0.14 sec)

mysql>

However, temporary tables are gone (even if you do not drop them) once your session is disconnected. You'll need to re-create them every time you connect.

Upvotes: 5

Haim Evgi
Haim Evgi

Reputation: 125486

you can't , give to view different name like

hs_hr_employee_view

from manual

Within a database, base tables and views share the same namespace, so a base table and a view cannot have the same name.

Upvotes: 12

Related Questions