CODEWITHSUNDEEP
mysqlsql-view
kivagant
kivagant

Reputation: 1957

How to prevent table column changing if a view with this column exist?

I have a table and corresponding view.

CREATE TABLE IF NOT EXISTS `test` (
  `id` mediumint(8) unsigned NOT NULL,
  `test` tinyint(3) unsigned NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
INSERT INTO `test` (`id`, `test`) VALUES (DEFAULT, 1);
INSERT INTO `test` (`id`, `test`) VALUES (DEFAULT, 2);

CREATE OR REPLACE
    ALGORITHM=UNDEFINED SQL SECURITY INVOKER
VIEW `test_view` AS
    SELECT `id`, `test` from `test`;

I can change table structure, for example:

ALTER TABLE `test` CHANGE `test` `test_renamed` TINYINT(3) UNSIGNED NOT NULL;

And all next queries to view will be broken:

SELECT * FROM test_view;
#1356 - View 'test_view' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them

How to prevent table columns changing if view used them?

UPDATE: I have Percona Server 5.6.32-78.0

Upvotes: 2

Views: 501

Answers (1)

Matijs
Matijs

Reputation: 2553

What you cannot do is tell your database system to refuse any changes to columns or tables used in views.

You can put a test in your code for the use of a table or even column in any view by writing a query on the information_schema.views table. However, it is up to you and your users to perform the test, mysql will not enforce it.

For this reason I usually use a script containing all views to recreate them after I changed the database structure. Of course sometimes one of these queries will fail after a change, but then those are the exact views you need to change.

You can then rename the new column name to the old column name or whatever you want to do to keep your old views usable.

Upvotes: 2

Related Questions