|
Home > Archive > MySQL ODBC Connector > January 2006 > MySQL ignores foreign key relationships between tables?
You are viewing an archived Text-only version of the thread.
To view this thread in it's original format and/or if you want to reply to
this thread please [click here]
| Author |
MySQL ignores foreign key relationships between tables?
|
|
| Ferindo Middleton Jr 2006-01-28, 8:23 pm |
| I have two tables, registration & schedules, that look like this:
CREATE TABLE registration (
id SERIAL NOT NULL UNIQUE,
firstname VARCHAR(256) NOT NULL,
middlename TEXT,
lastname VARCHAR(256),
suffix TEXT,
schedule_id INTEGER REFERENCES schedules(id),
);
CREATE TABLE schedules (
id SERIAL NOT NULL
UNIQUE,
start_date DATE NOT NULL,
end_date DATE NOT NULL,
);
The registration table above references the the schedules table via the
schedule_id. Why does MySQL allow a row created in the schedules table
be DELETED if it has a matching schedule_id in the registration table.
These two tables share a relationship based on registration.schedule_id
& schedules.id. I've tried this same syntax in PostgreSQL and it doesn't
allow the schedules.id record to be deleted without first removing any
records in the registration table which carry a matching schedule_id
record. Isn't that the point of a relational database?- TO CHECK
RELATIONSHIPS between tables and enforce that those relationships aren't
broken? I find it disappointing that MySQL ignores this relationship.
Ferindo
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql? unsub...sie.nctu.edu.tw
| |
| Paul DuBois 2006-01-28, 8:23 pm |
| At 17:56 -0500 1/28/06, Ferindo Middleton Jr wrote:
>I have two tables, registration & schedules, that look like this:
>CREATE TABLE registration (
> id SERIAL NOT NULL UNIQUE,
> firstname VARCHAR(256) NOT NULL,
> middlename TEXT,
> lastname VARCHAR(256),
> suffix TEXT,
> schedule_id INTEGER REFERENCES schedules(id),
> );
>
>CREATE TABLE schedules (
> id SERIAL NOT NULL UNIQUE,
> start_date DATE NOT NULL,
> end_date DATE NOT NULL,
> );
>
>The registration table above references the the schedules table via
>the schedule_id. Why does MySQL allow a row created in the schedules
>table be DELETED if it has a matching schedule_id in the
>registration table. These two tables share a relationship based on
>registration.schedule_id & schedules.id. I've tried this same syntax
>in PostgreSQL and it doesn't allow the schedules.id record to be
>deleted without first removing any records in the registration table
>which carry a matching schedule_id record. Isn't that the point of a
>relational database?- TO CHECK RELATIONSHIPS between tables and
>enforce that those relationships aren't broken? I find it
>disappointing that MySQL ignores this relationship.
Add ENGINE = InnoDB to the end of your table definitions.
Foreign keys are supported only for InnoDB tables in MySQL.
--
Paul DuBois, MySQL Documentation Team
Madison, Wisconsin, USA
MySQL AB, www.mysql.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql? unsub...sie.nctu.edu.tw
| |
| Ferindo Middleton Jr 2006-01-28, 8:23 pm |
| Paul DuBois wrote:
> At 17:56 -0500 1/28/06, Ferindo Middleton Jr wrote:
>
> Add ENGINE = InnoDB to the end of your table definitions.
> Foreign keys are supported only for InnoDB tables in MySQL.
>
I am using InnoDB. I use MySQL Administrator and InnoDB is what it says
all my tables are already using so it must have chosen that by default
or something. Does this mean that I shouldn't have been able to delete
records from my schedules table above that had a foreign key in the
registration table? Thanks.
Ferindo
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql? unsub...sie.nctu.edu.tw
| |
| Ferindo Middleton Jr 2006-01-28, 8:23 pm |
| Ferindo Middleton Jr wrote:
> Paul DuBois wrote:
> I am using InnoDB. I use MySQL Administrator and InnoDB is what it
> says all my tables are already using so it must have chosen that by
> default or something. Does this mean that I shouldn't have been able
> to delete records from my schedules table above that had a foreign key
> in the registration table? Thanks.
>
> Ferindo
>
>
Paul,
I discovered that this foreign key constraint wasn't present in these
tables anymore due to my own action. You see, I had backed up my
database before using MySQL Administrator, not knowing that is was
backing up such tables constructs as foreign keys, etc. So the database
I'm looking at today isn't the same database I originally created with
the same constraints... I'm going to stop using MySQL Administrator...
using it seems somewhat misleading and it made me think that the tables
sand the constraints I made on them were still present. Thanks.
Ferindo
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql? unsub...sie.nctu.edu.tw
| |
| Ferindo Middleton Jr 2006-01-29, 8:23 pm |
| Ferindo Middleton Jr wrote:
> Ferindo Middleton Jr wrote:
> Paul,
>
> I discovered that this foreign key constraint wasn't present in these
> tables anymore due to my own action. You see, I had backed up my
> database before using MySQL Administrator, not knowing that is was
> backing up such tables constructs as foreign keys, etc. So the
> database I'm looking at today isn't the same database I originally
> created with the same constraints... I'm going to stop using MySQL
> Administrator... using it seems somewhat misleading and it made me
> think that the tables sand the constraints I made on them were still
> present. Thanks.
>
> Ferindo
I take it back. I imported the data in my database above without using
the MySQL Administrator backup utility and first re-CREATEing the db
tables in my database.... But still MySQL still allows for
cross-referenced records between my schedules table and the registration
table schedule id field to be deleted. Why do you think this is
happening. Is this yet another feature that MySQL doesn't really support
yet? Is MySQL totally ignoring the REFERENCES part of the schedule_id
field from my registration table above. I've determined that I'm using
InnoDB so why isn't it working?
Ferindo
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql? unsub...sie.nctu.edu.tw
| |
| Paul DuBois 2006-01-29, 8:23 pm |
| At 18:03 -0500 1/29/06, Ferindo Middleton Jr wrote:
>Ferindo Middleton Jr wrote:
>I take it back. I imported the data in my database above without
>using the MySQL Administrator backup utility and first re-CREATEing
>the db tables in my database.... But still MySQL still allows for
>cross-referenced records between my schedules table and the
>registration table schedule id field to be deleted. Why do you think
>this is happening. Is this yet another feature that MySQL doesn't
>really support yet? Is MySQL totally ignoring the REFERENCES part of
>the schedule_id field from my registration table above. I've
>determined that I'm using InnoDB so why isn't it working?
>
>Ferindo
Looking at:
http://dev.mysql.com/doc/refman/5.0...onstraints.html
I see no examples that are missing FOREIGN KEY.
What happens if you change your table definition to change:
schedule_id INTEGER REFERENCES schedules(id)
To:
schedule_id INTEGER,
FOREIGN KEY (schedule_id) REFERENCES schedules(id)
Also, SERIAL is an alias for a BIGINT type, so you'll probably need to make
schedule_id a BIGINT.
--
Paul DuBois, MySQL Documentation Team
Madison, Wisconsin, USA
MySQL AB, www.mysql.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql? unsub...sie.nctu.edu.tw
| |
| Ferindo Middleton Jr 2006-01-30, 8:24 pm |
| Paul DuBois wrote:
> At 18:03 -0500 1/29/06, Ferindo Middleton Jr wrote:
>
> Looking at:
>
> http://dev.mysql.com/doc/refman/5.0...onstraints.html
>
>
> I see no examples that are missing FOREIGN KEY.
>
> What happens if you change your table definition to change:
>
> schedule_id INTEGER REFERENCES schedules(id)
>
> To:
>
> schedule_id INTEGER,
> FOREIGN KEY (schedule_id) REFERENCES schedules(id)
>
>
> Also, SERIAL is an alias for a BIGINT type, so you'll probably need to
> make
> schedule_id a BIGINT.
>
Thanks Paul,
However, I tried changing my table definition in a test db like you
suggest above to say:
schedule_id BIGINT,
FOREIGN KEY (schedule_id) REFERENCES schedules(id)
.... but I get this error message still which seems to imply that I'm not
forming this foreign key constraint correctly:
ERROR 1005 (HY000): Can't create table '.\test\registration.frm'
(errno: 150)
any ideas what I'm doing wrong?
Ferindo
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql? unsub...sie.nctu.edu.tw
| |
| Paul DuBois 2006-01-31, 3:23 am |
| At 20:41 -0500 1/30/06, Ferindo Middleton Jr wrote:
>Paul DuBois wrote:
>Thanks Paul,
>
>However, I tried changing my table definition in a test db like you
>suggest above to say:
>schedule_id BIGINT,
>FOREIGN KEY (schedule_id) REFERENCES schedules(id)
>
>... but I get this error message still which seems to imply that I'm
>not forming this foreign key constraint correctly:
>ERROR 1005 (HY000): Can't create table '.\test\registration.frm'
>(errno: 150)
>
>any ideas what I'm doing wrong?
I think so. But first, a tip: When you get an error like that from InnoDB,
try SHOW ENGINE INNODB STATUS (or just SHOW INNODB STATUS in older versions
of MySQL). Part of the output of this statement will likely contain more
detail about the error.
In this case, the error is my fault. :-) The referenced column and the
referencing column must have the same data type, and I said that SERIAL
was an alias for a BIGINT column. But it's really a BIGINT UNSIGNED column,
so schedule_id has to be BIGINT UNSIGNED as well. And since SERIAL is also
NOT NULL, you might as well make schedule_id NOT NULL, too.
These definitions worked for me:
CREATE TABLE schedules
(
id SERIAL NOT NULL UNIQUE,
start_date DATE NOT NULL,
end_date DATE NOT NULL
) ENGINE = InnoDB;
CREATE TABLE registration
(
id SERIAL NOT NULL UNIQUE,
firstname VARCHAR(256) NOT NULL,
middlename TEXT,
lastname VARCHAR(256),
suffix TEXT,
schedule_id BIGINT UNSIGNED NOT NULL,
FOREIGN KEY (schedule_id) REFERENCES schedules(id)
) ENGINE = InnoDB;
And they prevent entry of registration rows that don't match a schedules.id
value, and deletion of schedules rows that would orphan registration rows.
Test that with these statements:
insert into schedules values();
insert into registration (schedule_id) values(1);
# following statement should fail
insert into registration (schedule_id) values(3);
# following statement should fail
delete from schedules where id = 1;
Result for the statements that fail:
ERROR 1452 (23000): Cannot add or update a child row: a foreign key
constraint fails (`test/registration`, CONSTRAINT
`registration_ibfk_1
` FOREIGN KEY (`schedule_id`) REFERENCES
`schedules` (`id`))
ERROR 1451 (23000): Cannot delete or update a parent row: a foreign
key constraint fails (`test/registration`, CONSTRAINT
`registration_ibfk_1
` FOREIGN KEY (`schedule_id`) REFERENCES
`schedules` (`id`))
--
Paul DuBois, MySQL Documentation Team
Madison, Wisconsin, USA
MySQL AB, www.mysql.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql? unsub...sie.nctu.edu.tw
| |
| Ferindo Middleton Jr 2006-01-31, 8:23 pm |
| Paul DuBois wrote:
> At 20:41 -0500 1/30/06, Ferindo Middleton Jr wrote:
>
> I think so. But first, a tip: When you get an error like that from
> InnoDB,
> try SHOW ENGINE INNODB STATUS (or just SHOW INNODB STATUS in older
> versions
> of MySQL). Part of the output of this statement will likely contain more
> detail about the error.
>
> In this case, the error is my fault. :-) The referenced column and the
> referencing column must have the same data type, and I said that SERIAL
> was an alias for a BIGINT column. But it's really a BIGINT UNSIGNED
> column,
> so schedule_id has to be BIGINT UNSIGNED as well. And since SERIAL is
> also
> NOT NULL, you might as well make schedule_id NOT NULL, too.
>
> These definitions worked for me:
>
> CREATE TABLE schedules
> (
> id SERIAL NOT NULL UNIQUE,
> start_date DATE NOT NULL,
> end_date DATE NOT NULL
> ) ENGINE = InnoDB;
>
> CREATE TABLE registration
> (
> id SERIAL NOT NULL UNIQUE,
> firstname VARCHAR(256) NOT NULL,
> middlename TEXT,
> lastname VARCHAR(256),
> suffix TEXT,
> schedule_id BIGINT UNSIGNED NOT NULL,
> FOREIGN KEY (schedule_id) REFERENCES schedules(id)
> ) ENGINE = InnoDB;
>
> And they prevent entry of registration rows that don't match a
> schedules.id
> value, and deletion of schedules rows that would orphan registration
> rows.
>
> Test that with these statements:
>
> insert into schedules values();
> insert into registration (schedule_id) values(1);
> # following statement should fail
> insert into registration (schedule_id) values(3);
> # following statement should fail
> delete from schedules where id = 1;
>
> Result for the statements that fail:
>
> ERROR 1452 (23000): Cannot add or update a child row: a foreign key
> constraint fails (`test/registration`, CONSTRAINT
> `registration_ibfk_1
` FOREIGN KEY (`schedule_id`) REFERENCES
> `schedules` (`id`))
>
> ERROR 1451 (23000): Cannot delete or update a parent row: a foreign
> key constraint fails (`test/registration`, CONSTRAINT
> `registration_ibfk_1
` FOREIGN KEY (`schedule_id`) REFERENCES
> `schedules` (`id`))
>
Thanks, Paul. The definitions for the tables you provided above worked
for me too. I tested it and even implemented these definitions of
the tables in a copy of the production environment of my application and
it works good. I suddenly feel allot better about using MySQL... One
less thing I have to write a lot of code to prevent. Thanks, again. I
really appreciate your guidance and help with this. Knowing how to
properly implement foreign keys in MySQL will help the systems I put
together operate better. Thanks.
Ferindo
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql? unsub...sie.nctu.edu.tw
|
|
|
|
|