CODEWITHSUNDEEP
mysql
Silencer310
Silencer310

Reputation: 1090

MySQL multiple tables of the same name in the same database

I have a database, and a set of tables. The scenario is that, two different departments use the exact same table structure and the server code/frontend, but the data for the departments is completely different.

The best thing would be to have two different databases, with the exact structure and different data.

However, if I would try to implement this in just 1 database, would it be possible? I cannot change the table names, as it would require changing the entire code which queries the tables. Is there any way to create sub-folders/directories in a single database?

I searched, and there probably isn't. But maybe there is a workaround someone can suggest?

Upvotes: 1

Views: 6406

Answers (2)

Jai
Jai

Reputation: 61

Stop the Mysql Server and restart it again, This must solve the issue. Windows

mysqladmin -u root -p shutdown

Linux

/etc/init.d/mysqld stop or service mysqld stop or service mysql stop

Upvotes: 0

Bill Karwin
Bill Karwin

Reputation: 562330

Read about Identifier Qualifiers. You can have tables of the same name only if they are in separate databases, and you use the database name as a qualifier.

SELECT * FROM db1.sametablename;
SELECT * FROM db2.sametablename;

You can join tables across databases in the same query:

SELECT * FROM db1.sametablename JOIN db2.sametablename;

You can even declare foreign key constraints across databases:

ALTER TABLE db1.sametablename ADD FOREIGN KEY (col1) 
  REFERENCES db2.sametablename (col1);

Databases are basically a namespace for tables and other objects.

Upvotes: 1

Related Questions