Results 1 to 7 of 7
  1. #1
    Join Date
    Mar 2008
    Posts
    5

    Unanswered: use of Check constraint in data validation rules

    I have two table:
    NodeTable
    MetaNOdeTable

    NodeTable
    ----------------------------
    NodeId :1
    NodeType:Tools (This NodeId & NodeType are column)

    MetaNodeTable
    -------------------------
    NodeId:1
    SubType: Process (This NodeId & SubType are column)


    Here NodeId ofboth the table are the primary key of that table.There is no FOREIGN Key between this two table.
    But there is a validation rules between this two table...i.e. the SubType of MetaNodeTable depends on the NodeType of NodeTable
    Example: If the NodeType of NodeTable is "Tools" the the SubType of MetaNodeTable will be "Process".

    My quwestion is how I can put this validation at the time of data load.Can check constraints work for this?How can I put a constraint for this validation which?
    I have to put the validation such a way that it will show the constraint violation if I try to insert any wrong data.....

    Please help me.....I am waiting for ur reply.....
    Last edited by arpita_ece07; 03-14-08 at 01:56.

  2. #2
    Join Date
    Feb 2005
    Location
    Utah
    Posts
    73
    It seems like you are trying to create an implicit relationship between Nodes and MetaNodes. In that case, the best thing to do is make that relationship explicit by using foreign keys. You should not be relating the primary keys of two tables. You need to look at your tables and decide which is true

    1. Nodes have one or more MetaNodes.
    OR
    2. MetaNodes have one or more Nodes.
    OR
    3. Nodes have one or more MetaNodes and MetaNodes have one or more Nodes.
    4. Nodes have one and only one MetaNode and MetaNodes have one and only one Node.

    Each of these relationships will require a different structure for your tables, so what kind of relationship do you have between the tables?
    Dandy
    Aspiring Database Dwarf

  3. #3
    Join Date
    Mar 2008
    Posts
    5
    ONe to One relationship is there between NodeTable and MetaNodeTable because only one record can be there in the MetaNodeTable which is corresponding to NodeTable.

  4. #4
    Join Date
    Mar 2008
    Location
    Windhoek Namibia
    Posts
    13
    If your relationship is one2one handle the table as one. i.e. make one table
    Wayne Philip - openaxon.com

  5. #5
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Just be careful with the CHECK constraint, I don't think you can get it to work.. You can check here for the full details, but:
    Quote Originally Posted by My SQL 6.0 Documentation
    The CHECK clause is parsed but ignored by all storage engines
    I've heard that there is an option that can be set to forewarn a user about syntax that MySQL will happily parse but never execute/enforce, but I can't find that option anywhere.

    -PatP

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    server sql mode: http://dev.mysql.com/doc/refman/5.0/...-sql-mode.html

    didn't see anything about the silent CHECK clause, though

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

  7. #7
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Whats a metanode?
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

Posting Permissions

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