jian he <jian.universal...@gmail.com> writes: > On Sun, Dec 1, 2024 at 1:53 PM Tom Lane <t...@sss.pgh.pa.us> wrote: >> (I'd be curious to know how other major implementations handle >> this. Are we the only implementation that ever read the spec >> that way?)
> quote from > https://learn.microsoft.com/en-us/sql/t-sql/statements/create-schema-transact-sql?view=sql-server-ver16 > <<>> > CREATE SCHEMA can create a schema, the tables and views it contains, and > GRANT, > REVOKE, or DENY permissions on any securable in a single statement. This > statement must be executed as a separate batch. Objects created by the CREATE > SCHEMA statement are created inside the schema that is being created. > Securables to be created by CREATE SCHEMA can be listed in any order, except > for > views that reference other views. In that case, the referenced view must be > created before the view that references it. > Therefore, a GRANT statement can grant permission on an object before the > object > itself is created, or a CREATE VIEW statement can appear before the CREATE > TABLE > statements that create the tables referenced by the view. Also, CREATE TABLE > statements can declare foreign keys to tables that are defined later in the > CREATE SCHEMA statement. > <<>> Interesting. But I suspect this tells us more about SQL Server's internal implementation of DDL actions than about spec requirements. I looked at DB2's reference page: https://www.ibm.com/docs/en/db2/11.5?topic=statements-create-schema It doesn't have much of anything explicit on this topic, but they do give an example showing that you can create two tables with mutually referencing foreign keys, which means they postpone FK constraint creation till the end. There's also this interesting tidbit: "Unqualified object names in any SQL statement within the CREATE SCHEMA statement are implicitly qualified by the name of the created schema." which eliminates some of the is-that-an-external-reference-or-a- forward-reference ambiguities I was concerned about yesterday. That ship sailed decades ago for us, however. I'm also interested to note that like SQL Server, DB2 has strict limits on the types of objects that can be created, much narrower than what the spec suggests. For DB2 it's: CREATE TABLE statement, excluding typed tables and materialized query tables CREATE VIEW statement, excluding typed views CREATE INDEX statement COMMENT statement GRANT statement That suggests, even though they don't say so, that they're trying to do forward-reference removal; there'd be little reason for the restriction otherwise. MySQL doesn't have CREATE SCHEMA (it's a synonym for CREATE DATABASE), so nothing to be learned there. Whether or not the standard has an opinion on this topic, it's pretty clear that real implementations are all over the place and have plenty of ad-hoc restrictions. I'm still thinking that "let's forget all that and do the subcommands in order" is a win for sanity and explainability. regards, tom lane
