Giorgio,
InnoDB only implements MATCH SIMPLE. MySQL/InnoDB ignores the MATCH
clause that you specify in the foreign key constraint definition.
http://www.contrib.andrew.cmu.edu/~shadow/sql/sql1992.txt
"
A referential constraint is satisfied if one of the following con-
ditions is true, depending on the <match option> specified in the
<referential constraint definition>:
- If no <match type> was specified then, for each row R1 of the
referencing table, either at least one of the values of the
referencing columns in R1 shall be a null value, or the
value of
each referencing column in R1 shall be equal to the value
of the
corresponding referenced column in some row of the referenced
table.
- If MATCH FULL was specified then, for each row R1 of the refer-
encing table, either the value of every referencing column
in R1
shall be a null value, or the value of every referencing column
in R1 shall not be null and there shall be some row R2 of the
referenced table such that the value of each referencing col-
umn in R1 is equal to the value of the corresponding referenced
column in R2.
42 Database Language SQL
X3H2-92-154/DBL CBR-002
4.10 Integrity
constraints
- If MATCH PARTIAL was specified then, for each row R1 of the
referencing table, there shall be some row R2 of the refer-
enced table such that the value of each referencing column in
R1 is either null or is equal to the value of the corresponding
referenced column in R2.
"
Best regards,
Heikki
Oracle Corp./Innobase Oy
InnoDB - transactions, row level locking, and foreign keys for MySQL
InnoDB Hot Backup - a hot backup tool for InnoDB which also backs up
MyISAM tables
http://www.innodb.com/order.php
...............
Hi,
I was looking at the CREATE TABLE syntax and I saw this reference
definition
for InnoDB tables:
reference_definition:
REFERENCES tbl_name [(index_col_name,...)]
[MATCH FULL | MATCH PARTIAL | MATCH SIMPLE]
[ON DELETE reference_option]
[ON UPDATE reference_option]
reference_option:
RESTRICT | CASCADE | SET NULL | NO ACTION
Well, I pretty undestood all but I can't understand 2 things about MATCH
option:
1. Which of three options (SIMPLE, FULL, PARTIAL) are implemented in the
current production version of MySQL? Reading around, I think SIMPLE and
FULL
are supported. What about PARTIAL? Is it supported?
2. About MATCH PARTIAL. I could not find a good explanation on how it
works. I
found here http://forge.mysql.com/wiki/ForeignKeySupport the
explanations on
MATCH SIMPLE and MATCH FULL:
--------------QUOTED-------------
Handling of the the MATCH clause -- The HLS specifies that support for
MATCH
SIMPLE only is required. Since it is very easy to support MATCH FULL if we
already have support for MATCH SIMPLE, MATCH FULL should perhaps be
added to
the list of requirements as an optional feature. These types of the MATCH
clause differ only in case of checks that are done during insertion into a
referencing table.
So they can be implemented by a simple check which is to be done at the
beggining of the trigger which is responsible for processing of a
foreign key
check on insertion:
* if the match type is SIMPLE and one or more foreign key columns in the
row
being inserted is NULL, then regard the constraint as satisified. Otherwise
continue with checking.
* if the match type for the foreign key constraint is FULL and all
foreign key
columns in the row being inserted are NULL, then treat the constraint as
satisified. If at least one of columns in the foreign key is NULL, and at
least one of them is not NULL, treat the foreign key constraint as failed.
Otherwise (if all columns in the foreign key are not NULL), continue with
checking.
Handling of foreign key constraints with MATCH PARTIAL is more complex and
support for it is not required in the HLS.
------------END QUOTED-------------
But there's no explanation about MATCH PARTIAL.
So, what about MATCH PARTIAL. Is it supported and how does it works?
Thanks for any infos.
Giorgio
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
