Bug #19173 Foreign Key reference integrity

• Submitted: 18 Apr 2006 18:19
• Modified: 24 Feb 2007 19:39
• Reporter: Barry Quiel
• Status: Verified
• Category: MySQL Server: DML
• Severity: S4 (Feature request)
• Version: 5.0.20
• OS: Linux (Linux)
• CPU Architecture: Any

[18 Apr 2006 18:19] Barry Quiel

Description:
I have created 2 tables:
CREATE TABLE unix_group
(
gid integer(5),
name varchar(8) NOT NULL,
passwd varchar(34) NOT NULL DEFAULT 'x',
CONSTRAINT gid_pk PRIMARY KEY (gid),
CONSTRAINT name_uk UNIQUE (name)
)

create table unix_group_member
(
gid integer(5),
uname varchar(8),
expire_after date NOT NULL DEFAULT '2020-12-31',
CONSTRAINT gid_group_fk FOREIGN KEY (gid) REFERENCES unix_group (gid),
CONSTRAINT uname_group_fk FOREIGN KEY (uname) REFERENCES unix_acct (uname)
)

I can insert into unix_group_member any gid without it existing in unix-group.  This violates the foreign key constraint.

This issue also existed in 5.0.18

How to repeat:
With the Unix_group table empty:
insert into unix_group_member values(206,'s',DEFAULT); will violate the constraint every time.

[18 Apr 2006 19:03] MySQL Verification Team

Thank you for the bug report.

Are you using InnoDB table engine as default engine? Otherwise please
read:

http://dev.mysql.com/doc/refman/5.0/en/create-table.html

InnoDB tables support checking of foreign key constraints. See Section 14.2, “The InnoDB Storage Engine”. Note that the FOREIGN KEY syntax in InnoDB is more restrictive than the syntax presented for the CREATE TABLE statement at the beginning of this section: The columns of the referenced table must always be explicitly named. InnoDB supports both ON DELETE and ON UPDATE actions on foreign keys. For the precise syntax, see Section 14.2.6.4, “FOREIGN KEY Constraints”. 

For other storage engines, MySQL Server parses and ignores the FOREIGN KEY and REFERENCES syntax in CREATE TABLE statements. The CHECK clause is parsed but ignored by all storage engines. See Section 1.9.5.5, “Foreign Keys”. 

Thanks in advance.

[19 Apr 2006 18:24] Barry Quiel

The InnoDB foreign key constraints do work correctly.  

The final database engine will be NDBCLUSTER.  But when creating tables the default is MyISAM.  I think its a bit odd that these engines would choose to ignore the foreign key constraints.

I have downgraded this from S1 to S4 in the hopes that the inforcement of foreign key contstraints will be added to all the storage engines in the next release of 5.X

[24 Feb 2007 19:39] MySQL Verification Team

Thank you for the bug report.