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?
Ferindo
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
