Page 1 of 2 12 LastLast
Results 1 to 15 of 18
  1. #1
    Join Date
    Mar 2008
    Posts
    52

    Style: Specifying constraints, In-line vs Out-of-line (CREATE TABLE vs ALTER TABLE)?

    Two styles for defining constraints:

    In-line:
    Code:
    CREATE TABLE fruit
    (
      fruit_id INT IDENTITY(1,1)
          CONSTRAINT PK_fruit PRIMARY KEY CLUSTERED,
      fruit_name NVARCHAR(50),
      fruit_type INT
          CONSTRAINT FK_fruit_fruit_types FOREIGN KEY
          REFERENCES fruit_types (fruit_type_id) ON UPDATE CASCADE,
      date_created DATETIME DEFAULT CURRENT_TIMESTAMP
    )
    Out-of-line:
    Code:
    CREATE TABLE fruit
    (
      fruit_id INT,
      fruit_name NVARCHAR(50),
      fruit_type_id INT,
      date_created DATETIME
    )
    
    ALTER TABLE fruit ALTER COLUMN fruit_id INT NOT NULL
    
    ALTER TABLE fruit ADD
      CONSTRAINT PK_fruit PRIMARY KEY CLUSTERED (fruit_id),
      CONSTRAINT FK_fruit_fruit_types FOREIGN KEY (fruit_type_id)
          REFERENCES fruit_types (fruit_type_id),
      CONSTRAINT DF_fruit_date_created DEFAULT
          CURRENT_TIMESTAMP FOR date_created
    Which style do you prefer and why?

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    I'd say on the list of importing decisions in my life, that links somewhere above "Paper or plastic?" but below "Venti or Grande'?".
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  3. #3
    Join Date
    Mar 2008
    Posts
    52
    What about canvas bags?? To me it's an issue of layout and readability, which IMO are important issues.

  4. #4
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Quote Originally Posted by tmpuzer
    To me it's an issue of layout and readability, which IMO are important issues.
    For DDL scripts that are only executed once, when the Database is created or for updated?
    I hope you're not creating these tables and indexes on the fly in your application code.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  5. #5
    Join Date
    Mar 2008
    Posts
    52
    For DDL scripts (not application code) executed once on database creation.

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by tmpuzer
    To me it's an issue of layout and readability, which IMO are important issues.
    okay, fair point

    so, which one do you prefer?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  7. #7
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    I put everything that I can in create table statement (basically everything except non-unique indexes).

    Keeps my code compact and together. But yeah - I would not call it a religious dogma.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  8. #8
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    why not the non-unique indexes, poots?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  9. #9
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Because unique indexes can be (implicitly) added as constraints. Non unique indexes are not constraints so cannot be added as part of the create table statement. T-SQL I'm talking about here - I would imagine (but don't know since I am one of Bill's b*tches) that most RDBMSs are the same.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  10. #10
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    they aren't

    which is why adding all indexes via ALTER is more portable

    for most people, e.g. yourself, this matters little, as you operate within a single conceptual DBA silo of thought, and never port to heterogeneous platforms

    eh
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  11. #11
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Which RDMBSs enforce unique constraints by a mechanism other than indexes? TMK all the major ones do.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  12. #12
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    far as i know, yes, they all do
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  13. #13
    Join Date
    Mar 2008
    Posts
    52
    Quote Originally Posted by r937
    so, which one do you prefer?
    I prefer specifying the constraints in-line (inside the create table statement), as it keeps the definition all together as one unit. But I was wondering what motivation people had for the out-of-line style. One thing I don't like about the in-line option is I feel it clutters the create table statement so it's harder to see at a glance what columns the table has.

  14. #14
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Nope - that isn't necessary. This is my create table template. I keep ALL constraints at the bottom, in strict order (PK, Alt Key, check, foreign key), except defaults - but only because I can't move these. It is also more consistent - if your key is composite you can't declare it as part of the column definition anyway.

    Code:
    IF NOT EXISTS (SELECT NULL FROM sys.schemas WHERE name = 's_name') BEGIN
        EXEC ('CREATE SCHEMA s_name')
    END
    
    IF EXISTS (SELECT NULL FROM sys.tables WHERE object_id = OBJECT_ID('s_name.t_name')) BEGIN
        DROP TABLE s_name.t_name
    END
    
    CREATE TABLE s_name.t_name
        (
            c_name        INT IDENTITY    NOT NULL
            , c_2_name    VARCHAR(10)   NULL            CONSTRAINT df_c_2_name DEFAULT ('def val')
            , CONSTRAINT pk_t_name_u_c                 PRIMARY KEY CLUSTERED (c_name) WITH (FILLFACTOR = 100)
            , CONSTRAINT ix_t_name_c_2_name_u_nc  UNIQUE NONCLUSTERED (c_2_name) WITH (FILLFACTOR = 100)
            , CONSTRAINT ck_t_name_c_2_name         CHECK (c_2_name IN('def val')) 
            , CONSTRAINT fk_t_name_t_2_name          FOREIGN KEY (c_2_name) REFERENCES s_name.t_2_name (c_2_name) --ON DELETE CASCADE
        )
    GO
    Testimonial:
    pootle flump
    ur codings are working excelent.

  15. #15
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    This whole in-depth discussion of how to format DDL statements reminds me of one of Shakespeare's plays.

    Much ado about nothing.

    <Insert rim shot here>
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •