Results 1 to 6 of 6
  1. #1
    Join Date
    Oct 2010
    Location
    Orlando, FL
    Posts
    311
    Provided Answers: 1

    Unanswered: How Can I Force Specific Values in a Column

    I am building a test database to understand how constraints / triggers / & foreign keys work...

    I have a table called 'zooproducts' and in that table, I have:

    Code:
    zoo=# \d zooproducts
                                     Table "public.zooproducts"
     Column  |          Type          |                        Modifiers
    ---------+------------------------+----------------------------------------------------------
     id      | integer                | not null default nextval('zooproducts_id_seq'::regclass)
     model   | character varying(20)  | not null
     color   | character varying(10)  | not null
     size    | smallint               | not null
     comment | character varying(100) |
    Indexes:
        "zooproducts_pkey" PRIMARY KEY, btree (id)
        "zooproducts_model_key" UNIQUE CONSTRAINT, btree (model)
    Now mainly trying to focus on size of each board. I don't want someone adding in bogus values for 'size' field. I want to make sure that the sizes are only of those in which I offer:

    34", 35", 36", 38", 40", 42", 44", & 46" long decks.

    Now how can I enforce that only valid values are input into the table. I don't want someone entering 200" deck since that value is obviously bogus. Can someone please help me engineer a way I can enforce the available size options above when entering values into my 'public.zooproducts.size' field?

    Not sure if I need a trigger (which I've never used or done before) or if I should create a separate table called 'zoosizes':

    Code:
    zoo=# \d zoosizes
                              Table "public.zoosizes"
     Column |   Type   |                       Modifiers
    --------+----------+-------------------------------------------------------
     id     | integer  | not null default nextval('zoosizes_id_seq'::regclass)
     sizes  | smallint | not null
    Indexes:
        "zoosizes_pkey" PRIMARY KEY, btree (id)
    
    zoo=# SELECT * FROM zoosizes ORDER BY id;
     id | sizes
    ----+-------
      1 |    34
      2 |    35
      3 |    36
      4 |    37
      5 |    38
      6 |    40
      7 |    42
      8 |    44
      9 |    46
    (9 rows)
    and then make some kind of FOREIGN KEY from zooproducts.size > zoosizes.sizes.

    Can someone please recommend the best / easiest way to engineer a simple problem in ANSI SQL on how I can force customers / users to enter the correct sizes for my decks that exist versus a value of -32768 to +32767.

    Thank you so much!

  2. #2
    Join Date
    Nov 2003
    Posts
    2,933
    Provided Answers: 12
    Use a check constraint:

    Code:
    alter table zoosizes 
          add constraint valid_size 
          check (sizes in (34, 35, 36, 38, 40, 42, 44, 46));
    I will not read nor answer questions where the SQL code is messy and not formatted properly using [code] tags: http://www.dbforums.com/misc.php?do=bbcode#code

    Tips for good questions:

    http://tkyte.blogspot.de/2005/06/how...questions.html
    http://wiki.postgresql.org/wiki/SlowQueryQuestions
    http://catb.org/esr/faqs/smart-questions.html

  3. #3
    Join Date
    Dec 2008
    Location
    At work...
    Posts
    92
    In SQL there are usually several solutions to each problem, some better and some not that good.

    As Shammat suggested a check constraint can be used. However, if you know that the different board sizes you offer may change once in a while, I'd suggest to use the foreign key solution instead.

    Triggers can also be used, but thats nothing I'd recommend. Less flexible (as check constraint), but also unexessary complex.

  4. #4
    Join Date
    Nov 2003
    Posts
    2,933
    Provided Answers: 12
    Quote Originally Posted by JarlH View Post
    As Shammat suggested a check constraint can be used. However, if you know that the different board sizes you offer may change once in a while, I'd suggest to use the foreign key solution instead.
    you probably still want the check constraint on the referenced table though.
    I will not read nor answer questions where the SQL code is messy and not formatted properly using [code] tags: http://www.dbforums.com/misc.php?do=bbcode#code

    Tips for good questions:

    http://tkyte.blogspot.de/2005/06/how...questions.html
    http://wiki.postgresql.org/wiki/SlowQueryQuestions
    http://catb.org/esr/faqs/smart-questions.html

  5. #5
    Join Date
    Oct 2010
    Location
    Orlando, FL
    Posts
    311
    Provided Answers: 1
    So you're suggesting I completely DROP the 'zoosizes' table which exist just to make sure that only correct sizes are input into zooproducts & replace it with the 'check constraint', correct? Just want to make sure I am following this correctly that I would no longer need the 'zoosizes' table then, correct?

  6. #6
    Join Date
    Oct 2010
    Location
    Orlando, FL
    Posts
    311
    Provided Answers: 1
    I'm going to try both - the F.K. & the CHECK CONSTRAINT in my database:

    Code:
    zoo=# \d zooproducts
                                     Table "public.zooproducts"
     Column  |          Type          |                        Modifiers
    ---------+------------------------+----------------------------------------------------------
     id      | integer                | not null default nextval('zooproducts_id_seq'::regclass)
     model   | character varying(20)  | not null
     color   | character varying(10)  | not null
     size    | smallint               | not null
     comment | character varying(100) |
    Indexes:
        "zooproducts_pkey" PRIMARY KEY, btree (id)
        "zooproducts_model_key" UNIQUE CONSTRAINT, btree (model)
    Foreign-key constraints:
        "zooproducts_size_fkey" FOREIGN KEY (size) REFERENCES zoosizes(sizes)
    which appears to have worked:

    Code:
    zoo=# INSERT INTO zooproducts
    zoo-# (model, color, size, comment)
    zoo-# VALUES
    zoo-# (
    zoo(# 'Albec12',
    zoo(# 'Green',
    zoo(# '55',
    zoo(# 'New 2013 Board'
    zoo(# );
    ERROR:  insert or update on table "zooproducts" violates foreign key constraint "zooproducts_size_fkey"
    DETAIL:  Key (size)=(55) is not present in table "zoosizes".

Posting Permissions

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