Paul DuBois wrote:
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`))
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/[EMAIL PROTECTED]
