Hello,
Dima asked me to give my opinion about the matter.
* Marc Alff <marc.alff@stripped> [08/02/08 02:18]:
First of all, I believe we do not need to have different SQL
grammar depending on foreign_key_all_engines switch.
Details see below.
> 1)
>
> According to the HLS, the syntax for constraints is
>
> [ CONSTRAINT constraint_name ] /* NOTE 1 */
>
> In the code, this is implemented by the rule opt_constraint.
>
> opt_constraint is broken, and can generate the keyword CONSTRAINT alone,
> without a constraint name
opt_constraint rule is used in other places, and omitting
constraint name seems to be legal and documented:
http://dev.mysql.com/doc/refman/5.1/en/create-table.html
Suggestion: no change.
> Using it in :
> opt_constraint FOREIGN KEY_SYM
> leads to accepting the following syntax:
> CONSTRAINT FOREIGN KEY ...
> (unverified, found by reading code only)
>
> This syntax is illegal according to the HLS.
>
> 2)
>
> According to the HLS, the syntax can contain NO ACTION:
>
> Table constraints are separate clauses in CREATE TABLE or ALTER TABLE.
>
> [ CONSTRAINT constraint_name ] /* NOTE 1 */
>
> FOREIGN KEY [constraint_name] (fk_column list)
> REFERENCES pk_table_name (pk_column list) /* NOTE 2 */
> [ MATCH FULL | MATCH PARTIAL | MATCH SIMPLE ] /* NOTE 3 */
> [ ON UPDATE { CASCADE | SET NULL | SET DEFAULT | RESTRICT | NO ACTION } ]
> [ ON DELETE { CASCADE | SET NULL | SET DEFAULT | RESTRICT | NO ACTION } ]
>
> According to the LLD, the syntax can not contain NO ACTION:
>
> FOREIGN KEY CLAUSE IN 'CREATE TABLE'. The syntax is:
> [CONSTRAINT constraint_name]
> FOREIGN KEY [constraint_name] (child column list)
> REFERENCES parent_table_name (parent column list)
> [ MATCH SIMPLE | FULL ]
> [ ON UPDATE CASCADE | RESTRICT | SET NULL | SET DEFAULT ]
> [ ON DELETE CASCADE | RESTRICT | SET NULL | SET DEFAULT ]
>
> Beside, usage of "{}" from the HLS is more accurate for the ON UPDATE /
> ON DELETE clauses,
> the similar syntax in the LLD is misleading.
Fixed by now, I believe.
>
> 3)
>
> The HLS and LLD don't agree on MATCH PARTIAL either
Ditto.
> 4)
>
> Assuming the HLS is correct, the MATCH clause can happen at most 1 time,
> and should happen before ON UPDATE / ON DELETE.
> The ON UPDATE and ON UPDATE delete clauses should appear each at most 1
> time also.
>
> In the code, the rule opt_on_delete_list can produce any option multiple
> times,
> and in any order, so that the following syntax is accepted (I tested it):
>
> FOREIGN KEY (s2) REFERENCES t1 (s3)
> ON UPDATE CASCADE
> MATCH FULL
> ON UPDATE RESTRICT
> MATCH PARTIAL
>
> That's obviously illegal.
Suggestion: report a bug and fix in 6.0.
Only one MATCH clause should be allowed, and it should precede ON
UPDATE/ON DELETE clauses.
Rationale: this is how it is in the standard and how it is
documented in our own manual:
http://dev.mysql.com/doc/refman/5.1/en/create-table.html
--
-- Konstantin Osipov Software Developer, Moscow, Russia
-- MySQL AB, www.mysql.com The best DATABASE COMPANY in the GALAXY
