0

I have a web application that has multiple tables. These tables and the values in their columns are stores in mysql tables with each mysql table representing one table in the application. Let's say the following is what I in mysql database have:

table1 with column1_1, column1_2, column1_3
table2 with column2_1, column2_2
table3 with column3_1, column3_2, column3_3, column3_4

I have a new requirement wherein I should be able to allow a user to make comments on any of the columns of any of the tables. For this I am creating a mysql table named 'comments' with following fields:

id
table_name (name of table on which comment is being made)
column_name (name of the column on which comment is being made)

Currently, table_name and column_name are varchar. I am wondering if there is a way to restrict table_name column to taking values of the currently existing table names viz. table1, table2 and table3 and also be able to refer to those tables using the entry in table_name. Similarly, for column_name, I want to be able to refer to column present in the other tables. Is there a way to do this?

Vaibhav Raj
  • 1
  • 2

1 Answers1

0

On MySQL, you can get the names of the tables in a schema by using the following query:

SELECT table_name FROM information_schema.tables
WHERE table_schema = 'your_database_name';

You can also retrieve the names of the columns in a table with the query:

SELECT column_name
FROM information_schema.columns
WHERE table_schema = 'your_database_name' AND table_name = 'your_table';

According to MySQL's documentation, CHECK constraints cannot contain user-defined functions or subqueries (among other things). Instead of using a CHECK constraint, what you can do is write a trigger that checks whether or not the inserted table_name is valid, and, if so, check if the column_name belongs to that table.

Palmiro
  • 28
  • 4