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