I'm having a problem with a relational table that is accumulating duplicate entries. They're benign, but I don't want the table growing.
I can't see why, from reading my code. (If anybody wants to dig right into the code, it's available from prattle.sourceforge.com).
I think the real problem is that MySQL doesn't actually implement a relational model -- there are no referential integrity checks. Added to this is the fact that I don't know what I'm doing. SQL and I have only a passing acquaintance.
My data model has several tables.
prattle_users is, not surprisingly, a table of users
prattle_messages is a table of messages
prattle_rooms is a table of rooms
There is a one-to-many relationship between room and messages. Easy to implement in the messages table.
There is a many-to-many relationship between users and messages. I don't want to keep track of all of those relations.
The detail that I want to track is "last message read in a given room."
So I have implemented a table:
CREATE TABLE prattle_hiwater (
uid int(11) DEFAULT '0' NOT NULL,
roomid int(11) DEFAULT '0' NOT NULL,
messageid int(11) DEFAULT '0' NOT NULL,
KEY uid (uid, roomid)
);
All of these values actually are foreign keys. Any given uid, roomid, and messageid will exist multiple times within a prattle_hiwater column. However, there should be no row where all three values duplicate those of another row.
Is there a way to enforce this within MySQL?
I can't see why, from reading my code. (If anybody wants to dig right into the code, it's available from prattle.sourceforge.com).
I think the real problem is that MySQL doesn't actually implement a relational model -- there are no referential integrity checks. Added to this is the fact that I don't know what I'm doing. SQL and I have only a passing acquaintance.
My data model has several tables.
prattle_users is, not surprisingly, a table of users
prattle_messages is a table of messages
prattle_rooms is a table of rooms
There is a one-to-many relationship between room and messages. Easy to implement in the messages table.
There is a many-to-many relationship between users and messages. I don't want to keep track of all of those relations.
The detail that I want to track is "last message read in a given room."
So I have implemented a table:
CREATE TABLE prattle_hiwater (
uid int(11) DEFAULT '0' NOT NULL,
roomid int(11) DEFAULT '0' NOT NULL,
messageid int(11) DEFAULT '0' NOT NULL,
KEY uid (uid, roomid)
);
All of these values actually are foreign keys. Any given uid, roomid, and messageid will exist multiple times within a prattle_hiwater column. However, there should be no row where all three values duplicate those of another row.
Is there a way to enforce this within MySQL?