+- CodeIgniter Forums (https://forum.codeigniter.com)
+-- Forum: Archived Discussions (https://forum.codeigniter.com/forumdisplay.php?fid=20)
+--- Forum: Archived Development & Programming (https://forum.codeigniter.com/forumdisplay.php?fid=23)
+--- Thread: MySQL question (/showthread.php?tid=4244)
MySQL question - El Forum - 11-14-2007
[eluser]rvent[/eluser]
Hello i am new to CI, but i have been using RoR for a while. I am migrating an application i had made and at the same time i am improving a lot of things with it.
Anyways. I have this table named "Messages" and a second table named "MsgAuthor" and both go like this:
MsgAuthor table:
Code:
CREATE TABLE `MsgAuthor` (
`Author` varchar(10) NOT NULL,
`AuthorID` int(3) NOT NULL auto_increment,
PRIMARY KEY (`AuthorID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;Messages Table:
Code:
CREATE TABLE `Messages` (
`Message` text,
`MessageID` int(20) NOT NULL auto_increment,
`AuthorID` int(3) NOT NULL,
PRIMARY KEY (`MessageID`),
INDEX (`AuthorID`),
FOREIGN KEY (`AuthorID`) REFERENCES `messageauthor` (`AuthorID`) ON UPDATE NO ACTION ON DELETE NO ACTION
) ENGINE=InnoDB DEFAULT CHARSET=utf8;The problem is that when i do: "INSERT INTO Messages(Message, AuthorID) VALUES('hello', 1)" i get an the following error:
Code:
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`smt_test/Messages`, CONSTRAINT `Messages_ibfk_1` FOREIGN KEY (`AuthorID`) REFERENCES `MsgAuthor` (`AuthorID`) ON DELETE NO ACTION ON UPDATE NO ACTION)The table MsgAuthor has a record with ID 1.... But for some reason i cant add the new record to the Messages table.
What i want is to have the MsgAuthor contain all 5 Authors and whenever a new Message is created in the Messages Table it mus be associated with any of the existing Authors. This mean that there would be several messages created by 1 Author...
How could i accomplish this... Am i looking at it all wrong..?
I was also looking at the sql script that ships with some CMS and i noticed that they do not use any FOREIGN KEYS on any of their table. Why is that...?
Thanks for any ideas..
MySQL question - El Forum - 11-14-2007
[eluser]commissioner[/eluser]
this may or may not be your problem....
FOREIGN KEY (`AuthorID`) REFERENCES `messageauthor` (`AuthorID`) ON UPDATE NO ACTION ON DELETE NO ACTIO...
should the table reference be 'MsgAuthor' ('AuthorID')?
While referential integrity seems to be perceived as "good design", I have never been a fan. Perhaps it is because I have developed in databases before RI was available, but I find some of it's behaviors too restrictive. In almost all of our db designs (that is what I have done for a living for 15+ years) we choose not to employ db-level RI, and instead implement integrity through the application.