use of Check constraint in data validation rules

arpita_ece07 · #1 (**permalink**) 03-14-08, 00:51

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.....

tianmingqing · #2 (**permalink**) 03-14-08, 11:09

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?

arpita_ece07 · #3 (**permalink**) 03-17-08, 03:46

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.

koriba · #4 (**permalink**) 03-18-08, 13:10

If your relationship is one2one handle the table as one. i.e. make one table

Pat Phelan · #5 (**permalink**) 03-18-08, 13:30

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