How to alter INFORMATION_SCHEMA or add triggers or foreign keys to it?

Question

I'm trying to create some meta-data to extend mysql functionality but I can't create tables in the database INFORMATION_SCHEMA. I thought that I could just create another database and have my metadata in there but I need some foreign keys from my tables to some tables in the INFORMATION_SCHEMA DB. Nevertheless, I get errors when trying to create them. Then I thought I could create a trigger to get notified of changes but since triggers are associated to a table and I can't alter that database, I can't create triggers either.

Specifically I have some tables that references to information_schema.schemata(schema_name) and to information_schema.schemata(columns) and some others. I want to have those foreign key so I can use ON UPDATE CASCADE ON DELETE CASCADE or otherwise I'll have some rows in my tables referencing to nothing and I can't allow that.

I'm using mariaDB 5.5.30 which uses MySql 5.3.

I wonder if a materialized view on a view would work. Than you could create indexes/keys off of the materialized view... — xQbert, May 11 '13 at 22:13

score 2 · Answer 1 · answered May 11 '13 at 22:08

2

INFORMATION_SCHEMA tables are actually views whose contents is automatically maintained by the MySQL server.

The manual gives more information:

Inside INFORMATION_SCHEMA there are several read-only tables. They are actually views, not base tables, so there are no files associated with them, and you cannot set triggers on them. Also, there is no database directory with that name.

Although you can select INFORMATION_SCHEMA as the default database with a USE statement, you can only read the contents of tables, not perform INSERT, UPDATE, or DELETE operations on them.

answered May 11 '13 at 22:08

Jocelyn

11,209
10
43
60

Thanks for that info. Do you know how can I "observe" when something change in those views? – Bishma Stornelli May 11 '13 at 22:55
I think you need to add triggers to the real tables you want to monitor, since INFORMATION_SCHEMA tables do not accept triggers. – Jocelyn May 11 '13 at 22:57

score 1 · Answer 2 · answered Dec 09 '14 at 12:32

1

They are not really views but temporary tables, that's why you don't see folders. show create view views; ERROR 1347 (HY000): 'information_schema./tmp/#sql_2ac_0' is not VIEW

answered Dec 09 '14 at 12:32

Sergiu Dan Ilie

11
1

How to alter INFORMATION_SCHEMA or add triggers or foreign keys to it?

2 Answers2

Linked