CODEWITHSUNDEEP
mysql
besciualex
besciualex

Reputation: 1892

Create a table who's name must be a primary key in another table

Is there a way I can force my MySQL database to allow creation of a new table with name table_xyz, only if a record with table_xyz exists in another table?

Basically I want my table name to be a foreign key.

e.g. allowed_tables structure:

table_id | other columns |
--------------------------
table_xyz| bla bla bla   |
--------------------------

Because I have a record with table_xyz in allowed_tables, I can create a table with this name. If I delete the record, the table should automatically be deleted. The same must happen if I change the table name.

Edit 1:

The table allowed_structures actually is called loans. It contains loan_id, and other columns required on the contract. After I insert this record, I create a table with the name < loan_id > ( a record which must exists in loans table). If I update/ delete a record with < loan_id > in loans table, I want that table name to be changed automatically, not via a PHP script. Currently is done via a PHP script.

I need to know which is the best option to do this and why. Thanks!

Upvotes: 0

Views: 145

Answers (1)

Lukasz Szozda
Lukasz Szozda

Reputation: 175884

This was originally written for SQL Server, but concept is same for every RDBMS.

Yes it is achievable, but Curse and Blessing Dynamic SQL by Erland Sommarskog

CREATE TABLE @tbl

The desire here is to create a table of which the name is determined at run-time.

If we just look at the arguments against using dynamic SQL in stored procedures, few of them are really applicable here. If a stored procedure has a static CREATE TABLE in it, the user who runs the procedure must have permissions to create tables, so dynamic SQL will not change anything. Plan caching obviously has nothing to do with it. Etc.

Nevertheless: Why? Why would you want to do this? If you are creating tables on the fly in your application, you have missed some fundamentals about database design. In a relational database, the set of tables and columns are supposed to be constant. They may change with the installation of new versions, but not during run-time.

EDIT:

In SQL Server I would use trigger for source table (FOR INSERT/UPDATE/DELETE) and using Dynamic-SQL I can achieve exactly what you want, but still I think this is madness.

If you want to go this path check if MySQL supports the same.

Upvotes: 3

Related Questions