At 20:41 -0500 1/30/06, Ferindo Middleton Jr wrote:
Paul DuBois wrote:
At 18:03 -0500 1/29/06, Ferindo Middleton Jr wrote:
Ferindo Middleton Jr wrote:
Ferindo Middleton Jr wrote:
Paul DuBois wrote:
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.
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
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/en/innodb-foreign-key-constraints.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?
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/[EMAIL PROTECTED]
