Bug #23720 There is no way to defer schema checking on CREATE VIEW
Submitted: 27 Oct 2006 14:09 Modified: 7 Oct 2008 7:07
Reporter: Baron Schwartz (Basic Quality Contributor) Email Updates:
Status: Verified Impact on me:
Category:MySQL Server: Views Severity:S4 (Feature request)
Version:5.0.21-log OS:Any (All)
Assigned to: CPU Architecture:Any
Tags: qc, view check

[27 Oct 2006 14:09] Baron Schwartz
Some option to allow creating views even when the base tables don't exist would be helpful.  Similar functionality exists with foreign keys (SET FOREIGN_KEY_CHECKS=0).

Without this functionality, it is hard to save a script that can recreate completely a database(s) and all its tables and views.  Views that refer to tables that haven't been created yet can't be created.  Views that refer to other views that haven't been created yet can't be created.

The worst case is when scripting a set of databases that have views referring to tables in other databases.  If database A has view v_A referring to tables in database B, you can't create its views until database B and its tables are created.  But if database B also has vw_B referring to tables in database A, neither database can be scripted fully without the other already existing.

The only solution I know of right now is to script the databases, then their tables, then their views last.  So my schema to set up and tear down a test server must set up all the databases and their tables before setting up any views.

My prior experience with this is with MS SQL Server, when creating a stored procedure.  It would use early binding if possible; if the sproc referred to a table that existed, it would do schema checking and complain if, for example, I referred to a nonexistent column.  If the table didn't exist, it would defer checking and use late binding.  This is heplful for cases such as when procedures might refer to temporary tables.

How to repeat:
Feature request.

Suggested fix:
I don't know what standard SQL says about this.  Perhaps there can be an option to the CREATE VIEW statement, such as WITH LATE BINDING or DEFER SCHEMA CHECKS or something similar (I would prefer this over a SET option, personally).
[7 Oct 2008 7:07] Valeriy Kravchuk
Thank you for a reasonable feature request. Oracle also has similar feature (FORCE option) that allows to create view even if base table(s) does not exist.