Re: SQL99 CREATE TABLE ... (LIKE parent_table)

> > If you consider LIKE is allowed to inherit an IDENTIFIER, which is a
> > sequence based column with the intent that it will be a primary key,
> > then allowing inheritance of CONSTRAINTS (via an optional flag INCLUDING
> > CONSTRAINTS -- default is per spec) only makes sense.
>
> Hm. Exactly what will LIKE do with a serial column, and will it be
> sensible? (I'd bet not, unless you put in some special cases...)

Going with the assumption that SERIAL is another way of specifying a
IDENTITY column (auto-generated value by sequence as described in SQL
2k3) the default behaviour is described as copying the column name and
datatype by default.

If INCLUDING DEFAULTS is specified, then I would argue that the
automatically created SERIAL default should *not* be copied into the new
table as the spec treats GENERATORS and DEFAULTS as two independent
concepts. This is shown by the 'ALWAYS GENERATED' flag which indicates
the generated value may not be overridden, unlike a default.

If INCLUDING IDENTITY is specified (which I don't intend to do at this
time as our SERIALs are different enough from IDENTITIES to make this
somewhat confusing), then it appears that the sequence should be
duplicated but have the same settings as the one on the parent table.
Including the same START WITH value. Not the current value of the
sequence, but the value it was initially started at.

That said, if the user creates a SEQUENCE and adds a default value of
NEXT VALUE FOR <sequence>, then it should be copied if INCLUDING
DEFAULTS is specified -- and is not an IDENTITY of the column, so would
be ignored by INCLUDING IDENTITY.

SQL99 has none of the above inclusions. For Serials default behaviour
would be to copy the int4 or int8 datatype, the NOT NULL constraint, as
well as the column name. Defaults and all other information are
ignored.

--
Rod Taylor <rbt(at)rbt(dot)ca>

PGP Key: http://www.rbt.ca/rbtpub.asc