Results 1 to 8 of 8
  1. #1
    Join Date
    Aug 2007
    Location
    Auckland New Zealand
    Posts
    120
    Provided Answers: 1

    Unanswered: Array of Distinct Values for a Column

    I Have a column, which has only two or maybe three (but only two at the moment), possible values, which if I make into a FK setup will just add to the large amount of Joins on most Queries, I would rather set the values as either

    ShiftType IN ('Column','Floor')

    I Have a few Columns that would require this, how do I set this up in the table editor in Management Studio?

  2. #2
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Code:
    CREATE TABLE have_you_considered_check_constraints (
       shift_type char(10)
         CONSTRAINT ck_shift_type CHECK (shift_type IN 'Column', 'Floor')
    )
    This will limit the column to only those values in the constraint (remember this will also allow null values unless you specify the nullability of the column!)
    George
    Home | Blog

  3. #3
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by Nate1
    ... which if I make into a FK setup will just add to the large amount of Joins on most Queries
    this is a common misconception, fostered by the rampant overuse of surrogate keys

    make it a FK to this table --

    CREATE TABLE ShiftTypes
    ( ShiftType VARCHAR(12) NOT NULL PRIMARY KEY );
    INSERT INTO ShiftTypes VALUES ('Column');
    INSERT INTO ShiftTypes VALUES ('Floor');

    now when you use an FK pointing to this table in some other table, you get the full benefits of relational integrity and yet you don't actually have to join to this table!!! whodathunkit!!

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

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    george, your constraints will of course work, but what happens if a third shift type is added -- you will have to do maintenance on all your umpteen tables at the DDL level

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

  5. #5
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Exactsolutely Rudy
    George
    Home | Blog

  6. #6
    Join Date
    Aug 2007
    Location
    Auckland New Zealand
    Posts
    120
    Provided Answers: 1

    OK So NO Joins

    So if I use a FK it will not effect performance in a Negative way? Why?

  7. #7
    Join Date
    Aug 2007
    Location
    Auckland New Zealand
    Posts
    120
    Provided Answers: 1
    SO
    Inner Join tblShiftType tST ON tsT.PK = stk.FK

    isn't Required?

  8. #8
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    think about what you're asking

    you have a column, and in that column you will find values 'Column' and 'Floor'

    you have the full assurance (by relational integrity) that those are "valid" because they reference a primary key

    why would you join to the table that has the primary key if that's all that it has?

    you don't need the ShiftType table to tell you that the name of the 'Floor' value is 'Floor'

    the join is simply not necessary

    BTW, plz do not stick "tbl" into the name of the table -- or else, if you feel you must, then you should also stick "col" into the name of every column

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

Posting Permissions

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