#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    May 2013
    Posts
    3
    Rep Power
    0

    CREATE TABLE with CHECK to avoid duplicate insert


    Hello

    I want to check for conflict between fields while insert. I want to use CHECK on CREATE TABLE for this. for example:

    Code:
    CREATE TABLE checkDup( 
    id INT(3) AUTO_INCREMENT, 
    val1 INT(3), 
    val2 INT(3),
    CHECK (val2 NOT IN (val1)) 
    )

    How do i get the values from val1? I cant use "SELECT val1 FROM checkDup", because the table dont exists yet.
    Pls help, i didnt find any solution yet.

    Thx
  2. #2
  3. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,118
    Rep Power
    4274
    how can one integer be "in" another integer?

    oh, and by the way, mysql does not support CHECK constraints
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  4. #3
  5. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    May 2013
    Posts
    3
    Rep Power
    0
    yes, perhaps i didnt discribe clear. if i want to insert a 10 to val2, the CHECK constraint should look to all existent values of val1. and dont allow insert if there is a 10 in value-list of val1.

    ok so i cant test it anyway, only have mysql to test this.

    but what could be the solution? just for the theory.
  6. #4
  7. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,118
    Rep Power
    4274
    Originally Posted by dislozierter
    but what could be the solution? just for the theory.
    a complete redesign

    please explain what these numbers actually represent
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  8. #5
  9. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    May 2013
    Posts
    3
    Rep Power
    0
    for sure, it is not a practical thing.

    this is a theoretical question from a test, and i struggle with the solution.
    i dont have to use CHECK but one of the constraints is needed.
    i dont see any solution with the other constraints (NOT NULL, PRIMARA KEY...).
    CHECK comes closest to the task, but it wont work with refrences on the own (not yet build) table, at least mysql gives a error because of the undefined table if i use "CHECK (val2 in (SELECT val1 FROM checkDup))".

    perhaps its just mysql which cant handle that?
  10. #6
  11. No Profile Picture
    Contributing User
    Devshed Frequenter (2500 - 2999 posts)

    Join Date
    Oct 2003
    Location
    Germany
    Posts
    2,781
    Rep Power
    348
    MySQL does not support check constraints.

    But even if it did, it wouldn't work, because check constraint only validate values for a single row, not for the whole table.
    I will not read nor answer questions where the SQL code is messy and not formatted properly using [code] tags.
    http://forums.devshed.com/misc.php?do=bbcode#code

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

IMN logo majestic logo threadwatch logo seochat tools logo