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