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