NAME
DBIO::MSSQL::DDL - Generate MSSQL DDL from DBIO Result classes
VERSION
version 0.900007
DESCRIPTION
DBIO::MSSQL::DDL generates a MSSQL DDL script from a DBIO::Schema class hierarchy. It is the desired-state side of the test-deploy-and- compare strategy used by DBIO::MSSQL::Deploy.
my $ddl = DBIO::MSSQL::DDL->install_ddl($schema_class_or_instance);
The output is plain SQL, suitable for executing one statement at a time against a fresh MSSQL database. Emits CREATE TABLE (inline columns, primary key, unique) and CREATE INDEX, and the foreign keys of the belongs_to relationships as ALTER TABLE ... ADD CONSTRAINT (see below).
Foreign keys
Every belongs_to over plain foreign.col => self.col pairs becomes one FOREIGN KEY constraint named FK_<table>_<columns>, written after all CREATE TABLE statements, with the on_delete and on_update of the relationship (CASCADE, SET NULL, SET DEFAULT, NO ACTION; RESTRICT is written as NO ACTION, which SQL Server checks immediately as well; any other word is refused). Two relationships over the same columns with the same target and actions are one constraint; with another target or other actions they are refused. A target that is a view or inline SQL is skipped (not refused: belongs_to sets is_foreign_key_constraint to 1 itself, so a key to a view that was not asked for cannot be told from one that was, and the shared test schema of the family has one; say is_foreign_key_constraint => 0 to make that explicit); a table outside the deploy is refused; is_foreign_key_constraint => 0 writes none. A condition that is no plain column pair is skipped. Column names are written quoted, so a name with a blank or a hyphen works.
A name over 128 characters (the most SQL Server holds) is cut to 128 and ends in 8 hex digits of the SHA-1 of the full name (_constraint_name, the one function that names FK_ and UQ_ constraints).
Cascades SQL Server refuses
SQL Server rejects a foreign key that "may cause cycles or multiple cascade paths" (Msg 1785). The driver refuses these before the server is asked, naming table and relationship (_check_cascade_paths, also run by DBIO::MSSQL::Diff over the target model when it adds a key). A key whose action is CASCADE, SET NULL or SET DEFAULT passes the DELETE or UPDATE of its parent on to its child, and is refused when it references its own table, when a chain of such keys comes back to a key or to the table it started on, or when two different chains reach the same table. NO ACTION (and RESTRICT, which is written as NO ACTION) is never refused. The rule was measured on SQL Server 2022 (t/95-ddl-refusals-names-live.t), including what it lets through: two keys to one parent when one is NO ACTION, DELETE CASCADE on one key and UPDATE CASCADE on the other, a cycle of SET NULL keys, a diamond whose legs are SET NULL on columns nothing references.
Table names and schemas
A table or view name with a dot, schema.table, is made in that schema: CREATE TABLE schema.table, both parts quoted on their own. A schema the classes name and the database lacks is created, by a CREATE SCHEMA that is a statement of its own, written before the first table (SQL Server wants it first in its batch; the statements run one by one). dbo.t is the table t of dbo, written without the schema, as DBIO::MSSQL::Introspect keys it. Indexes, unique constraints, primary and foreign keys of such a table are made in its schema and named after the bare table (PK_t, FK_t_col, UQ_t_col): SQL Server keeps constraint names once per schema, so one.t and two.t may both have a PK_t. A foreign key may cross the border in both directions. A name with two or more dots (a database-qualified one) or an empty part is refused: the driver cannot create objects in another database. A schema is never dropped, also when no class names it any more.
Names in one namespace
SQL Server keeps the names of constraints and of objects (tables, views) in one namespace per schema (Msg 2714). A constraint named like a table or view of the deploy -- FK_..., UQ_... or a unique name the declaration chose -- is refused before the server is asked, by install_ddl and by the diff over its target model, per schema. The shared test schema of core declares one (add_unique_constraint(artist => ...) on the table artist). Names are compared as written. A virtual view is not deployed and takes no name.
Types and defaults
A parameter of a standard type spelling (character varying(10)) must be a length or max, and must agree with size when both are given; anything else is refused. A true/false default on bit is written 1/0; a number with an exponent (1e5) on an integer type or bit is refused.
Unique constraints
Every unique constraint of the result source is written as CONSTRAINT <name> UNIQUE (...) with the name of the declaration (unique => [ name => [cols] ]; core names an undeclared one <table>_<columns>, and that one is written as UQ_<table>_<columns> because a table can have the name <table>_<columns> -- the table user with unique(email) and a table user_email -- and SQL Server holds constraint and table names in one namespace; a name the declaration chose is kept). Unnamed, the server makes up a name per database and the diff of two databases sees two different indexes. SQL Server holds constraint names once per schema: the same name on two tables is refused.
Indexes
CREATE INDEX comes from two declarations: mssql_indexes (DBIO::MSSQL::Result) and the index declaration of the result source that idx of DBIO::Cake and indices write (index_declarations, ADR 0061 in core). The latter gives name, columns and unique, and its own driver => { mssql => { kind => 'clustered' | 'nonclustered', where => '...' } } block gives the index kind and the filter; an empty mssql => {} is the plain index. Where both declarations use the same name, the canonical declaration wins. An entry that carries only another driver's block, an unknown key in the own block and a kind other than clustered or nonclustered are refused with an exception naming the index, never written as a plainer one. So are an index type other than unique and any options of the declaration: SQL Server has no counterpart written here, and they are not dropped silently.
An index over exactly the columns, in that order, of the primary key or of a unique constraint of the same table is not written, plain or declared unique: the server keeps an index for those, and a second one is a duplicate. One with kind => 'clustered' is written: it fixes the physical order of the table, which the nonclustered constraint index does not. But a table with a primary key has its clustered index already (the key is CLUSTERED as written here), and SQL Server allows one per table (Msg 1902): a declared clustered index on such a table, and a second one on any table, are refused with an exception naming table and index, before the server sees them. A clustered index on a table without a primary key stays valid.
where (the same key in a mssql_indexes entry and in the own block of the canonical declaration) makes a filtered index: CREATE [UNIQUE] INDEX ... (cols) WHERE <predicate>, the predicate written as given (SQL Server accepts only simple comparisons joined by AND, IN and IS [NOT] NULL there; it parses the predicate, the driver does not). An empty or non-string where and a filter on a clustered index (the server refuses that) are refused with an exception naming the index. A filtered index over exactly the columns of the key or of a unique constraint is written, being another object than the one the server keeps (an unfiltered one is still left out). DBIO::MSSQL::Diff::Index compares and replaces on a changed filter. The diff builds its desired model from this DDL (through DBIO::MSSQL::Deploy) and refuses the same.
METHODS
install_ddl
my $ddl = DBIO::MSSQL::DDL->install_ddl($schema);
Returns the full installation DDL as a single string.
AUTHOR
DBIO & DBIx::Class Authors
COPYRIGHT AND LICENSE
Copyright (C) 2026 DBIO Authors Portions Copyright (C) 2005-2025 DBIx::Class Authors Based on DBIx::Class, heavily modified.
This is free software; you can redistribute it and/or modify it under the same terms as the Perl 5 programming language system itself.