none
placement of constraints in DDL

    Pregunta

  • I am aware that constraints can be part of the CREATE TABLE statement and they can also be in the ALTER TABLE statement.  In SSMS if a user right clicks on a table and does a "script table as --> create to --> new query editor window" the primary key constraints appear as part of the CREATE TABLE statement while foreign keys, check constraints and default constraints show up in ALTER TABLE statements.  In some DDL generating software packages, constraints appear only in ALTER TABLE statements.  I am wondering two things:

    1. Why does software generating DDL place constraints in ALTER TABLE statements at all--why wouldn't it just put all constraints into CREATE TABLE?

    2. In practise, what is your preference for placement of constraints in DDL and why?

    domingo, 15 de abril de 2012 1:21

Respuestas

  • This is mainly a matter of convenience and readability.

    In our shop, we put defaults, CHECK constraints, primary key and UNIQUE constraints in the .tbl files which holds the CREATE TABLE statement.

    However, we put foreign keys in a separate .fkey file. The reason for this is that it makes it simple to build the database. First run all .tbl files, and then run .fkey files, and they can be run in alphabetic order or whatever. With the foreign keys in the .tbl files, the load tool would need to figure out the dependency order.

    If I type up a table defnition for a demo, I put all constraints in the CREATE TABLE definition for clarity.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    domingo, 15 de abril de 2012 9:59
  • 1) The software is reading and formatting the schema information tables. This is the simplest way to do this for a machine

    2) Put them in the CREATE TABLE statements so that a human can read and maintain the code. In psychology and typesetting. this is the Law of Proximity. 


    --CELKO-- Books in Celko Series for Morgan-Kaufmann Publishing: Analytics and OLAP in SQL / Data and Databases: Concepts in Practice Data / Measurements and Standards in SQL SQL for Smarties / SQL Programming Style / SQL Puzzles and Answers / Thinking in Sets / Trees and Hierarchies in SQL

    • Marcado como respuesta Knot domingo, 15 de abril de 2012 17:29
    domingo, 15 de abril de 2012 2:49

Todas las respuestas

  • I prefer creating constraints using CREATE TABLE statement. 

    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog

    domingo, 15 de abril de 2012 2:13
    Moderador
  • 1) The software is reading and formatting the schema information tables. This is the simplest way to do this for a machine

    2) Put them in the CREATE TABLE statements so that a human can read and maintain the code. In psychology and typesetting. this is the Law of Proximity. 


    --CELKO-- Books in Celko Series for Morgan-Kaufmann Publishing: Analytics and OLAP in SQL / Data and Databases: Concepts in Practice Data / Measurements and Standards in SQL SQL for Smarties / SQL Programming Style / SQL Puzzles and Answers / Thinking in Sets / Trees and Hierarchies in SQL

    • Marcado como respuesta Knot domingo, 15 de abril de 2012 17:29
    domingo, 15 de abril de 2012 2:49
  • It is easier to read if constraints are included within CREATE TABLE.

    Simple banking database example with CREATE TABLEs:

    http://www.sqlusa.com/bestpractices2005/bankdatabase/


    Kalman Toth SQL SERVER & BI TRAINING

    domingo, 15 de abril de 2012 7:18
    Moderador
  • This is mainly a matter of convenience and readability.

    In our shop, we put defaults, CHECK constraints, primary key and UNIQUE constraints in the .tbl files which holds the CREATE TABLE statement.

    However, we put foreign keys in a separate .fkey file. The reason for this is that it makes it simple to build the database. First run all .tbl files, and then run .fkey files, and they can be run in alphabetic order or whatever. With the foreign keys in the .tbl files, the load tool would need to figure out the dependency order.

    If I type up a table defnition for a demo, I put all constraints in the CREATE TABLE definition for clarity.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    domingo, 15 de abril de 2012 9:59
  • Erland - Can you explain a bit more about .tbl files and .fkey files?  Do .tbl and .fkey refer to the file extension?  Are they specific to a vendor's SQL package?
    domingo, 15 de abril de 2012 12:59
  • Yes, .tbl and .fkey are different file extentions that we use. You could say that it is a convention that we use, but it is supported by the build/load tool we use. (Which is available on my web site
    http://www.sommarskog.se/AbaPerls/index.html)


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    domingo, 15 de abril de 2012 17:17