Results 1 to 13 of 13
  1. #1
    Join Date
    Jan 2004
    Location
    madras-india
    Posts
    1

    DB Design (Constraints)

    Hi,

    I am new to DB design. I have a question to ask.

    How to design about constraints for a table, i am residing a db for performance.

    My designer tells me to issue a select and find the no of rows for all fields , if it is less than 5 rows (values) this should be a check constraints.

    sql> select distinct col1 from table1

    col1
    ---
    1
    2
    3
    4

    this is a check contraints , he request me to create a constaint for this.

    what does he means??

    Thanks in adv
    thirumaran
    Hyderabad-India

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    the CHECK constraint goes into the CREATE/ALTER TABLE statements
    Code:
    alter table table1
    add constraint no_more_than_4_values
    check ( 4 <= (select distinct col1 from table1) )
    or something like that
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    What database (SQL) engine are you using. I can only think of two (DB2 and PostgreSQL) that will support this kind of constraint because it involves more than one row.

    -PatP

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    are you asking me? i primarily use mysql

    thirumaran, if what i gave you doesn't work, you may inform your designer that you'll have to write a trigger
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    May 2003
    Posts
    13
    CREATE TABLE Entity1 (
    Number int NOT NULL
    CONSTRAINT Check
    CHECK (Number IN (1, 2, 3, 4))
    )
    go

  6. #6
    Join Date
    Mar 2001
    Location
    Lexington, KY
    Posts
    606
    What I've seen used (which is kind of neat) is a virtual view which is used against.

    e.g.
    CREATE VIEW your_check_view AS
    SELECT 1 somecol
    UNION
    SELECT 2
    UNION
    SELECT 3
    UNION
    etc.

    then you have
    CREATE TABLE yourtable (
    foo integer not null references your_check_view( somecol )
    )
    Thanks,

    Matt

  7. #7
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    looking back on what i wrote, i made a booboo, it should say

    check ( 4 <= (select count(distinct col1) from table1) )

    matt, your solution is nice, if you can assume that the 4 rows will have some kind of identity field which is numbered 1 through 4

    in general, if the values of col1 are 'curly','larry','moe', and now you try to add two more, it should accept one of them (the 4th) but not another (the 5th)
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  8. #8
    Join Date
    Mar 2001
    Location
    Lexington, KY
    Posts
    606
    Perhaps I misread the question. I took the following:
    "My designer tells me to issue a select and find the no of rows for all fields , if it is less than 5 rows (values) this should be a check constraints."

    to mean that if you have a table that is only five rows (or fewer) then you should drop the table and in referring tables use a hard-coded check constraint.

    e.g.
    create table bob references joe( joecol )

    table joe
    ----------
    1
    2
    3
    4

    becomes

    create table bob check in (1, 2, 3, 4)

    I don't necessarily agree that this approach is a 'best practice' because you have to check constraint every table that refers to it -- and if you ever add one more then you have to change every single one.

    But really, if your table is that small then it would probably fit on a single page and be in memory virtually all of the time -- so there would be precious little difference between a check constraint and a table lookup.
    Thanks,

    Matt

  9. #9
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Matt,

    Yes, that's how I read the requirement too. It seems that the designer has come up with a "rule of thumb" (ROT) that any foreign key with less than 5 distinct values should be replaced by a check constraint. What a silly idea! Surely the choice of whether to use a check constraint or a table depends on more than just the number of values - it depends on whether the values form a fixed domain or a variable set of values. For example, if one column was DEPARTMENT_NO and we currently only had 4 departments, it would be perverse to make this a check constraint. Or maybe we have 30 departments, but only 4 of them have created records in the Budget table, ...

  10. #10
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    that's obviously not what i got from the original requirement

    it says "find the no of rows for all fields , if it is less than 5 rows (values) this should be a check constraints"

    i took this to mean that the table can have no more than 4 rows in it

    can't do that with a FK constraint
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  11. #11
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    I think that we can safely say that until we understand the question, we have precious little chance of answering it. Maybe thirumaran will return and explain what they want/need in more detail, but without that I think we're beating a dead horse!

    -PatP

  12. #12
    Join Date
    Jun 2004
    Posts
    6

    Diff b/w trigger and Stored procedure

    Dear All please let me know
    Diff b/w trigger and Stored procedure.
    Thanks in advance

  13. #13
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    These are simplified definitions, but good enough for this purpose.

    A stored procedure is a bit of code stored in the database for later execution.

    A trigger is a stored procedure that runs automatically in response to an event, such as deleting a row from a table.

    -PatP

Posting Permissions

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