Re: Foreign key constraints - Known issues ?
>I just wanted to know whether there are any known issues in defining and using Foreign key constraints in MySQL 4 and >MySQL 5. >To be specific, are there any issues on using ON DELETE CASCADE and ON UPDATE CASCADE? > >Would there be any performance issues when we define Foreign key constraints? > >We dont define Foreign Key constraints here. But at the conceptual level we know that there are foreign keys. But we do not >create foreign keys at the physical level. Is this right? No, that is not "right", cause it means anyone (or any mistake in your software) can still bugger up your data. Make sure to install your constraints, it is able to save you countless hours in the future. Martijn Tonies Database Workbench - development tool for MySQL, and more! Upscene Productions http://www.upscene.com My thoughts: http://blog.upscene.com/martijn/ Database development questions? Check the forum! http://www.databasedevelopmentforum.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: foreign key constraints
Sergei, Check out Paul DuBois' book MySQL - The Definitive Guide (2nd edition). It has a few good chapters that discuss foreign key constraints. --bmansell Brian E. Mansell MySQL Professional On Thu, 22 Jul 2004 13:06:07 -0700, Sergei Skarupo <[EMAIL PROTECTED]> wrote: > Hi everyone, > > I'd like to learn more about foreign key constraints... > > I read this section of the manual... > > http://dev.mysql.com/doc/mysql/en/InnoDB_foreign_key_constraints.html > > Where can I find a more detailed description with some examples? > > Thanks, > > Sergei > > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Foreign Key Constraints
> > This makes perfectly sense. > > > > So, once again I dare to ask: what's the problem with NULLable > > Foreign Keys? It works fine :-) > > > > (now, who was it that said that FKs should be entered/exist > > always?) > > > > With regards, > > Here is the issue... > > If you go back to what he was doing this insert doesn't work.. > > INSERT INTO inno3(PK_Col, Child_Col) VALUES (NULL,1) > > He was trying to use a Primary key as his foreign key to inno2, (in this > example of what he was doing). You can't have a null PK as we all know. IT > wasn't the foreign key part that was hanging him up.. aha, the PK could be a problem yes. PKs cannot be NULL. That must be it. > I think we were on two different tracks and that could have been my fault. :-) With regards, Martijn Tonies Database Workbench - developer tool for InterBase, Firebird, MySQL & MS SQL Server. Upscene Productions http://www.upscene.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Foreign Key Constraints
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On Friday 28 May 2004 11:50 am, Martijn Tonies wrote: > This makes perfectly sense. > > So, once again I dare to ask: what's the problem with NULLable > Foreign Keys? It works fine :-) > > (now, who was it that said that FKs should be entered/exist > always?) He needs to send his create table commands and so forth.. He must of defined books.borrowid as some sort of PK or some kind of not nullable INDex/column... Thats my guess. - -- DM Advice: If they split up, giggle insanely. -BEGIN PGP SIGNATURE- Version: GnuPG v1.2.4 (GNU/Linux) iD8DBQFAt2++ld4MRA3gEwYRAhUyAKDbS1i26zUwk0p6ifWvanGLO49TDQCg1cJl 7JYbD3T1c6Rp16Cvew2JEk4= =GJmF -END PGP SIGNATURE- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Foreign Key Constraints
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On Friday 28 May 2004 11:50 am, Martijn Tonies wrote: > This makes perfectly sense. > > So, once again I dare to ask: what's the problem with NULLable > Foreign Keys? It works fine :-) > > (now, who was it that said that FKs should be entered/exist > always?) > > With regards, Here is the issue... If you go back to what he was doing this insert doesn't work.. INSERT INTO inno3(PK_Col, Child_Col) VALUES (NULL,1) He was trying to use a Primary key as his foreign key to inno2, (in this example of what he was doing). You can't have a null PK as we all know. IT wasn't the foreign key part that was hanging him up.. I think we were on two different tracks and that could have been my fault. Jeff - -- Give me Liberty or give me... well, whatever you think is best for society. -BEGIN PGP SIGNATURE- Version: GnuPG v1.2.4 (GNU/Linux) iD8DBQFAt28Ald4MRA3gEwYRAk5/AKCId5Bbpgdh6brzxC8H8E70k8tLrgCfUR8N 44Oq/W0lvwdt798hyi5F/6Q= =/8Iu -END PGP SIGNATURE- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Foreign Key Constraints
Hi, > > Martijn Tonies wrote: > > > >> Hi Jeff, > >> > > > > > > In this example, inno3.PK_Col references inno2.Child_Col, so the 2nd and > > 3rd statements are failing because they try to set inno3.PK_Col to > > values not present in inno2.Child_Col. The NULLs are irrelevant. Woops, right Michael - got that one wrong. > > Michael > > Perhaps this is what you meant? > > CREATE TABLE inno2 ( >PK_ColInteger NOT NULL DEFAULT 0, >Child_Col Integer, >PRIMARY KEY (PK_Col) > ) TYPE=InnoDB ; > > CREATE INDEX I_Inno2_ChildCol > ON inno2(Child_Col); > > CREATE TABLE inno3 ( >PK_ColInteger NOT NULL DEFAULT 0, >Child_Col Integer, >PRIMARY KEY (PK_Col) > ) TYPE=InnoDB ; > > CREATE INDEX I_Inno3_ChildCol > ON inno3(Child_Col); > > ALTER TABLE inno3 ADD FOREIGN KEY (Child_Col) >REFERENCES inno2 (Child_Col) >ON DELETE NO ACTION >ON UPDATE NO ACTION; > > INSERT INTO inno2(PK_Col, Child_Col) VALUES (1, NULL); > INSERT INTO inno2(PK_Col, Child_Col) VALUES (2, NULL); > INSERT INTO inno2(PK_Col, Child_Col) VALUES (3, 1); > > INSERT INTO inno3(PK_Col, Child_Col) VALUES (1, NULL); > INSERT INTO inno3(PK_Col, Child_Col) VALUES (2, NULL); > INSERT INTO inno3(PK_Col, Child_Col) VALUES (3, NULL); > > mysql> SELECT * FROM inno3; > ++---+ > | PK_Col | Child_Col | > ++---+ > | 1 | NULL | > | 2 | NULL | > | 3 | NULL | > ++---+ > 3 rows in set (0.05 sec) This makes perfectly sense. So, once again I dare to ask: what's the problem with NULLable Foreign Keys? It works fine :-) (now, who was it that said that FKs should be entered/exist always?) With regards, Martijn Tonies Database Workbench - developer tool for InterBase, Firebird, MySQL & MS SQL Server. Upscene Productions http://www.upscene.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Foreign Key Constraints
Michael Stassen wrote: Martijn Tonies wrote: Hi Jeff, In this example, inno3.PK_Col references inno2.Child_Col, so the 2nd and 3rd statements are failing because they try to set inno3.PK_Col to values not present in inno2.Child_Col. The NULLs are irrelevant. Michael Perhaps this is what you meant? CREATE TABLE inno2 ( PK_ColInteger NOT NULL DEFAULT 0, Child_Col Integer, PRIMARY KEY (PK_Col) ) TYPE=InnoDB ; CREATE INDEX I_Inno2_ChildCol ON inno2(Child_Col); CREATE TABLE inno3 ( PK_ColInteger NOT NULL DEFAULT 0, Child_Col Integer, PRIMARY KEY (PK_Col) ) TYPE=InnoDB ; CREATE INDEX I_Inno3_ChildCol ON inno3(Child_Col); ALTER TABLE inno3 ADD FOREIGN KEY (Child_Col) REFERENCES inno2 (Child_Col) ON DELETE NO ACTION ON UPDATE NO ACTION; INSERT INTO inno2(PK_Col, Child_Col) VALUES (1, NULL); INSERT INTO inno2(PK_Col, Child_Col) VALUES (2, NULL); INSERT INTO inno2(PK_Col, Child_Col) VALUES (3, 1); INSERT INTO inno3(PK_Col, Child_Col) VALUES (1, NULL); INSERT INTO inno3(PK_Col, Child_Col) VALUES (2, NULL); INSERT INTO inno3(PK_Col, Child_Col) VALUES (3, NULL); mysql> SELECT * FROM inno3; ++---+ | PK_Col | Child_Col | ++---+ | 1 | NULL | | 2 | NULL | | 3 | NULL | ++---+ 3 rows in set (0.05 sec) Michael -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Foreign Key Constraints
Martijn Tonies wrote: Hi Jeff, CREATE TABLE inno2 ( PK_Col Integer NOT NULL DEFAULT 0, Child_Col Integer, PRIMARY KEY (PK_Col) ) TYPE=InnoDB ; CREATE INDEX I_Inno2_ChildCol ON inno2(Child_Col); CREATE TABLE inno3 ( PK_Col Integer NOT NULL DEFAULT 0, Child_Col Integer, PRIMARY KEY (PK_Col) ) TYPE=InnoDB ; ALTER TABLE inno3 ADD FOREIGN KEY (Pk_Col) REFERENCES inno2 (Child_Col) ON DELETE NO ACTION ON UPDATE NO ACTION; INSERT INTO inno2(PK_Col, Child_Col) VALUES (1, NULL); INSERT INTO inno2(PK_Col, Child_Col) VALUES (2, NULL); INSERT INTO inno2(PK_Col, Child_Col) VALUES (3, 1); INSERT INTO inno3(PK_Col, Child_Col) VALUES (1, NULL); INSERT INTO inno3(PK_Col, Child_Col) VALUES (2, NULL); INSERT INTO inno3(PK_Col, Child_Col) VALUES (3, NULL); select * from inno2; The actual way he was doing it was above.. I am going to have look into this more since as you can see, this worked and considering I do not have a id 2 or 3.. it should have failed.. so something isn't right.. The entire point behind foreign keys is for constraints.. Its been awhile since I have done foreign keys on mysql... Indeed, inserting (2, NULL) fails - while it shouldn't. A FK should only be enforced if there's a value. Not when it's NULL (for the FK columns). This is true for all other database engines that I know. How else can you create tables with either a relationship to another table or no relationship? IMO, all INSERTs you wrote should succeed. With regards, Martijn Tonies In this example, inno3.PK_Col references inno2.Child_Col, so the 2nd and 3rd statements are failing because they try to set inno3.PK_Col to values not present in inno2.Child_Col. The NULLs are irrelevant. Michael -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Foreign Key Constraints
Hi Jeff, > > ok - I've checked. > > > > > > > Why not? What's wrong with this: > > > > > > > > > > BORROWER > > > > > BorrowerID > > > > > > > > > > BOOKS > > > > > BookID > > > > > BorrowerID (nullable) > > > > > > > > > > FK from Books.BorrowerID to Borrower.BorrowerID > > > > > > > > > > I haven't checked, but this _should_ be possible. > > > > > > > > > > With regards, > > > > > > > > Its a foreign key, you can not null foreign keys.. Thats the problem. > > > > That's not true. Here's what I got: > > > > CREATE TABLE inno1 ( > > PK_ColInteger NOT NULL DEFAULT 0, > > Child_Col Integer, > > PRIMARY KEY ( > > PK_Col > > ) > > ) TYPE=InnoDB ROW_FORMAT=fixed DEFAULT; > > > > CREATE INDEX I_Inno1_ChildCol > > ON inno1(Child_Col); > > > > ALTER TABLE inno1 ADD FOREIGN KEY (Child_Col) > > REFERENCES inno1 (PK_Col) > > ON DELETE NO ACTION > > ON UPDATE NO ACTION; > > > > > > After that, I inserted data: > > INSERT INTO inno1(PK_Col, Child_Col) VALUES (1, NULL); > > INSERT INTO inno1(PK_Col, Child_Col) VALUES (2, NULL); > > INSERT INTO inno1(PK_Col, Child_Col) VALUES (3, 1); > > > > > > Now, can someone explain what the problem with NULLable FKs is? > > CREATE TABLE inno2 ( > PK_Col Integer NOT NULL DEFAULT 0, > Child_Col Integer, > PRIMARY KEY (PK_Col) > ) TYPE=InnoDB ; > > CREATE INDEX I_Inno2_ChildCol > ON inno2(Child_Col); > > CREATE TABLE inno3 ( > PK_Col Integer NOT NULL DEFAULT 0, > Child_Col Integer, > PRIMARY KEY (PK_Col) > ) TYPE=InnoDB ; > > ALTER TABLE inno3 ADD FOREIGN KEY (Pk_Col) > REFERENCES inno2 (Child_Col) > ON DELETE NO ACTION > ON UPDATE NO ACTION; > > INSERT INTO inno2(PK_Col, Child_Col) VALUES (1, NULL); > INSERT INTO inno2(PK_Col, Child_Col) VALUES (2, NULL); > INSERT INTO inno2(PK_Col, Child_Col) VALUES (3, 1); > > INSERT INTO inno3(PK_Col, Child_Col) VALUES (1, NULL); > INSERT INTO inno3(PK_Col, Child_Col) VALUES (2, NULL); > INSERT INTO inno3(PK_Col, Child_Col) VALUES (3, NULL); > > select * >from inno2; > > The actual way he was doing it was above.. I am going to have look into this > more since as you can see, this worked and considering I do not have a id 2 > or 3.. it should have failed.. so something isn't right.. The entire point > behind foreign keys is for constraints.. Its been awhile since I have done > foreign keys on mysql... Indeed, inserting (2, NULL) fails - while it shouldn't. A FK should only be enforced if there's a value. Not when it's NULL (for the FK columns). This is true for all other database engines that I know. How else can you create tables with either a relationship to another table or no relationship? IMO, all INSERTs you wrote should succeed. With regards, Martijn Tonies Database Workbench - developer tool for InterBase, Firebird, MySQL & MS SQL Server. Upscene Productions http://www.upscene.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Foreign Key Constraints
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On Friday 28 May 2004 02:57 am, Martijn Tonies wrote: > Hi, > > ok - I've checked. > > > > > Why not? What's wrong with this: > > > > > > > > BORROWER > > > > BorrowerID > > > > > > > > BOOKS > > > > BookID > > > > BorrowerID (nullable) > > > > > > > > FK from Books.BorrowerID to Borrower.BorrowerID > > > > > > > > I haven't checked, but this _should_ be possible. > > > > > > > > With regards, > > > > > > Its a foreign key, you can not null foreign keys.. Thats the problem. > > That's not true. Here's what I got: > > CREATE TABLE inno1 ( > PK_ColInteger NOT NULL DEFAULT 0, > Child_Col Integer, > PRIMARY KEY ( > PK_Col > ) > ) TYPE=InnoDB ROW_FORMAT=fixed DEFAULT; > > CREATE INDEX I_Inno1_ChildCol > ON inno1(Child_Col); > > ALTER TABLE inno1 ADD FOREIGN KEY (Child_Col) > REFERENCES inno1 (PK_Col) > ON DELETE NO ACTION > ON UPDATE NO ACTION; > > > After that, I inserted data: > INSERT INTO inno1(PK_Col, Child_Col) VALUES (1, NULL); > INSERT INTO inno1(PK_Col, Child_Col) VALUES (2, NULL); > INSERT INTO inno1(PK_Col, Child_Col) VALUES (3, 1); > > > Now, can someone explain what the problem with NULLable FKs is? > > With regards, > > Martijn Tonies > Database Workbench - developer tool for InterBase, Firebird, MySQL & MS SQL > Server. > Upscene Productions > http://www.upscene.com CREATE TABLE inno2 ( PK_Col Integer NOT NULL DEFAULT 0, Child_Col Integer, PRIMARY KEY (PK_Col) ) TYPE=InnoDB ; CREATE INDEX I_Inno2_ChildCol ON inno2(Child_Col); CREATE TABLE inno3 ( PK_Col Integer NOT NULL DEFAULT 0, Child_Col Integer, PRIMARY KEY (PK_Col) ) TYPE=InnoDB ; ALTER TABLE inno3 ADD FOREIGN KEY (Pk_Col) REFERENCES inno2 (Child_Col) ON DELETE NO ACTION ON UPDATE NO ACTION; INSERT INTO inno2(PK_Col, Child_Col) VALUES (1, NULL); INSERT INTO inno2(PK_Col, Child_Col) VALUES (2, NULL); INSERT INTO inno2(PK_Col, Child_Col) VALUES (3, 1); INSERT INTO inno3(PK_Col, Child_Col) VALUES (1, NULL); INSERT INTO inno3(PK_Col, Child_Col) VALUES (2, NULL); INSERT INTO inno3(PK_Col, Child_Col) VALUES (3, NULL); select * from inno2; The actual way he was doing it was above.. I am going to have look into this more since as you can see, this worked and considering I do not have a id 2 or 3.. it should have failed.. so something isn't right.. The entire point behind foreign keys is for constraints.. Its been awhile since I have done foreign keys on mysql... - -- Enough research will tend to support your theory. -BEGIN PGP SIGNATURE- Version: GnuPG v1.2.4 (GNU/Linux) iD8DBQFAt1Ruld4MRA3gEwYRAvWEAKCcCqIsKLIPZk3od7Vn8z3rA9zAbACfZYhL 4VQLUYacl2HR9rmaBZC/pvw= =yiUm -END PGP SIGNATURE- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Foreign Key Constraints
Hi, ok - I've checked. > > > Why not? What's wrong with this: > > > > > > BORROWER > > > BorrowerID > > > > > > BOOKS > > > BookID > > > BorrowerID (nullable) > > > > > > FK from Books.BorrowerID to Borrower.BorrowerID > > > > > > I haven't checked, but this _should_ be possible. > > > > > > With regards, > > > > Its a foreign key, you can not null foreign keys.. Thats the problem. That's not true. Here's what I got: CREATE TABLE inno1 ( PK_ColInteger NOT NULL DEFAULT 0, Child_Col Integer, PRIMARY KEY ( PK_Col ) ) TYPE=InnoDB ROW_FORMAT=fixed DEFAULT; CREATE INDEX I_Inno1_ChildCol ON inno1(Child_Col); ALTER TABLE inno1 ADD FOREIGN KEY (Child_Col) REFERENCES inno1 (PK_Col) ON DELETE NO ACTION ON UPDATE NO ACTION; After that, I inserted data: INSERT INTO inno1(PK_Col, Child_Col) VALUES (1, NULL); INSERT INTO inno1(PK_Col, Child_Col) VALUES (2, NULL); INSERT INTO inno1(PK_Col, Child_Col) VALUES (3, 1); Now, can someone explain what the problem with NULLable FKs is? With regards, Martijn Tonies Database Workbench - developer tool for InterBase, Firebird, MySQL & MS SQL Server. Upscene Productions http://www.upscene.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Foreign Key Constraints
Hi, > > Why not? What's wrong with this: > > > > BORROWER > > BorrowerID > > > > BOOKS > > BookID > > BorrowerID (nullable) > > > > FK from Books.BorrowerID to Borrower.BorrowerID > > > > I haven't checked, but this _should_ be possible. > > > > With regards, > > Its a foreign key, you can not null foreign keys.. Thats the problem. If this really is the case with MySQL, then this is the only database engine that I know that doesn't allow this. With regards, Martijn Tonies Database Workbench - developer tool for InterBase, Firebird, MySQL & MS SQL Server. Upscene Productions http://www.upscene.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Foreign Key Constraints
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On Thursday 27 May 2004 03:00 am, Martijn Tonies wrote: > Why not? What's wrong with this: > > BORROWER > BorrowerID > > BOOKS > BookID > BorrowerID (nullable) > > FK from Books.BorrowerID to Borrower.BorrowerID > > I haven't checked, but this _should_ be possible. > > With regards, Its a foreign key, you can not null foreign keys.. Thats the problem. - -- Practice safe sin. -BEGIN PGP SIGNATURE- Version: GnuPG v1.2.4 (GNU/Linux) iD8DBQFAtfoTld4MRA3gEwYRApVcAJ9RNWF9qZrresicBlct3TP2fpEL1wCg3xIx VER2P5MGzLgUqLRc7JZLFys= =qVNw -END PGP SIGNATURE- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Foreign Key Constraints
You could create a special "borrower" account to signify that it is not loaned out and assign that to the book. If this is for a library system (multiple branches) you could create one account for each branch. That way you would know where the book is at all times, "borrowed" or not ;-) Shawn Green Database Administrator Unimin Corporation - Spruce Pine <[EMAIL PROTECTED] m> To: [EMAIL PROTECTED] cc: 05/27/2004 12:22 Fax to: AM Subject: Foreign Key Constraints Please respond to kyuubi Hi, I am trying to use the foreign key constraints from InnoDB and creating indexes is a requirement for foreign key. The problem is that by creating index for my foreign key, it does not allow my foreign key to have null or blank values which my records will have. For eg. a BorrowerID is a foreign key on a Book table, but when the book is not borrowed, the BorrowerID will be null and I can't seem to import the data containing null values for the foreign key. Is there a way to solve this? Thanks. Msg sent via Spymac Mail - http://www.spymac.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Foreign Key Constraints
Hi, > On Wednesday 26 May 2004 11:22 pm, [EMAIL PROTECTED] wrote: > > Hi, I am trying to use the foreign key constraints from InnoDB > > and creating indexes is a requirement for foreign key. > > The problem is that by creating index for my foreign key, > > it does not allow my foreign key to have null or blank values which my > > records will have. For eg. a BorrowerID is a foreign key on a Book table, > > but when the book is not borrowed, the BorrowerID will be null and I can't > > seem to import the data containing null values for the foreign key. Is > > there a way to solve this? > > Thanks. > > I am not to sure how its possible to fix it.. Thinking about this.. I would > have a bookid which is never null. Considering no matter if the book is out > or not, you have that book. I then would have a borrow table, lets say, as > the foreign key would be bookid.. I date borrowed, and date returned would be > how I would know if its out or not.. > > Just thinking off the top of my head as there probably is a better way to do > it. > > I am sure there are other ways to do this.. Foreign keys can't be null. Why not? What's wrong with this: BORROWER BorrowerID BOOKS BookID BorrowerID (nullable) FK from Books.BorrowerID to Borrower.BorrowerID I haven't checked, but this _should_ be possible. With regards, Martijn Tonies Database Workbench - developer tool for InterBase, Firebird, MySQL & MS SQL Server. Upscene Productions http://www.upscene.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Foreign Key Constraints
[EMAIL PROTECTED] wrote: Hi, I am trying to use the foreign key constraints from InnoDB and creating indexes is a requirement for foreign key. The problem is that by creating index for my foreign key, it does not allow my foreign key to have null or blank values which my records will have. For eg. a BorrowerID is a foreign key on a Book table, but when the book is not borrowed, the BorrowerID will be null and I can't seem to import the data containing null values for the foreign key. Is there a way to solve this? Thanks. If you have a foreign key constraint that requires the foreign key field to be populated then you effectively have a "MANY TO ONE" with Min of 1 and Max of 1 relationship between the book table and the borrower table -- thus you cannot have a book without a borrower. That is a logical problem, and the one you are describing. Separate the BorrowerID from table `book`. Make a table called, oh, `book_borrower` and put BookID BorrowerID DateOut DateDue DateReturned ... Then you can query for borrowed books using a join like: SELECT a.BookID, b.BorrowerID, c.FullName from ( book a inner join book_borrower b on a.BookID = b.BookID ) inner join borrower c on b.BorrowerID = c.BorrowerID WHERE b.DateReturned is null Available books could be found: SELECT a.BookID from book a left join book_borrower b on a.BookID=b.BookID WHERE b.BookID is null and b.DateReturned is null (Which says show me all the book.BookID that fail to have a non-returned book in the book_borrower table. Usually I put the "and b.DateReturned is null" with the join statement, instead of the WHERE clause.) Now you can have a book without a borrower and can easily track borrowing history for books and borrowers. Be sure to index the fields you'll be using as selection criteria! HTH, Robert J Taylor [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Foreign Key Constraints
[EMAIL PROTECTED] wrote: Hi, I am trying to use the foreign key constraints from InnoDB and creating indexes is a requirement for foreign key. The problem is that by creating index for my foreign key, it does not allow my foreign key to have null or blank values which my records will have. For eg. a BorrowerID is a foreign key on a Book table, but when the book is not borrowed, the BorrowerID will be null and I can't seem to import the data containing null values for the foreign key. Is there a way to solve this? Thanks. I think you have missed the point of a foreign key constraint. It is to ensure referential integrity. A constraint stops you doing things, ie entering a value that does not exist in another table. Or create a borrower called NO-ONE and always change the borrower to this when a book is returned. Or just use an outer join on an indexed field when doing reports is easiest and no constraint. Colin Bull -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Foreign Key Constraints
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On Wednesday 26 May 2004 11:22 pm, [EMAIL PROTECTED] wrote: > Hi, I am trying to use the foreign key constraints from InnoDB > and creating indexes is a requirement for foreign key. > The problem is that by creating index for my foreign key, > it does not allow my foreign key to have null or blank values which my > records will have. For eg. a BorrowerID is a foreign key on a Book table, > but when the book is not borrowed, the BorrowerID will be null and I can't > seem to import the data containing null values for the foreign key. Is > there a way to solve this? > Thanks. I am not to sure how its possible to fix it.. Thinking about this.. I would have a bookid which is never null. Considering no matter if the book is out or not, you have that book. I then would have a borrow table, lets say, as the foreign key would be bookid.. I date borrowed, and date returned would be how I would know if its out or not.. Just thinking off the top of my head as there probably is a better way to do it. I am sure there are other ways to do this.. Foreign keys can't be null. Jeff - -- Calm down--It's only ones and zeroes. -BEGIN PGP SIGNATURE- Version: GnuPG v1.2.4 (GNU/Linux) iD8DBQFAtW9lld4MRA3gEwYRAkaAAKCtA0+GlFLRifkWDdH661LIF7gaVwCdHM8V zxbQk96yn0v3dTTBD9F4gWM= =JLfz -END PGP SIGNATURE- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: foreign key constraints are driving me crazy!
In fact it seems to be just to opposite. I toggled the default values to be null and allowed for the null option and "poof" it worked! I don't really understand this very well. Wish I did. > > Some key in the table is null when it shouldnt be, or the type of the join > keys isnt the same. i have had issues when importing data from a dump so > i've had to do a FORIEGN_KEY_CHECKS=0 > > > > So I have made a table called 'uid' where on uid is the only field in > > the table. Then I make another table called 'users' where there are > > two fields, 'uid1' and 'uid2' and both uid values have foreign key > > constraints referencing the 'uid' field in the uid table. > > > > No problem, tables are created and are all empty. > > > > Then I try inserting a uid varchar value of 'test' into the uid > > table... > > > > ok works, > > > > Then I try inserting a uid1 and uid2 varchar value of 'test' into the > > users table... > > > > I get a foreign key constraint error or 1216: > > > > # > constraint fails> > > 1216 > > > > > > Does anyone know what I am doing wrong? > > > > Thanks, > > -Cere > > > > > > -- > > MySQL General Mailing List > > For list archives: http://lists.mysql.com/mysql > > To unsubscribe: > > http://lists.mysql.com/[EMAIL PROTECTED] > > > > - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - Cere Davis Unix Systems Administrator - CSDE [EMAIL PROTECTED] ph: 206.685.5346 https://staff.washington.edu/cere GnuPG Key http://staff.washington.edu/cere/gpgkey.txt Key fingerprint = B63C 2361 3B9B 8599 ECC9 D061 3E48 A832 F455 9E7FA -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: foreign key constraints are driving me crazy!
Some key in the table is null when it shouldnt be, or the type of the join keys isnt the same. i have had issues when importing data from a dump so i've had to do a FORIEGN_KEY_CHECKS=0 > > So I have made a table called 'uid' where on uid is the only field in > the table. Then I make another table called 'users' where there are > two fields, 'uid1' and 'uid2' and both uid values have foreign key > constraints referencing the 'uid' field in the uid table. > > No problem, tables are created and are all empty. > > Then I try inserting a uid varchar value of 'test' into the uid > table... > > ok works, > > Then I try inserting a uid1 and uid2 varchar value of 'test' into the > users table... > > I get a foreign key constraint error or 1216: > > # constraint fails> > 1216 > > > Does anyone know what I am doing wrong? > > Thanks, > -Cere > > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe: > http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: foreign key constraints
At 18:22 + 10/8/03, Croniser Brian Contr AFRL/IFGB wrote:
Here is the script that creates the database.
create table object_attrib (obj_record_id INTEGER(7) AUTO_INCREMENT NOT
NULL,
PRIMARY KEY (obj_record_id),
INDEX (rel_obj_int_id),
FOREIGN KEY (rel_obj_int_id)
REFERENCES obj_id_internals (obj_int_id)
ON DELETE RESTRICT ON UPDATE CASCADE,
INDEX (rel_os_id),
FOREIGN KEY (rel_os_id)
REFERENCES os_product (os_id)
ON DELETE RESTRICT ON UPDATE CASCADE,
INDEX (rel_db_id),
FOREIGN KEY (rel_db_id)
REFERENCES db_product (db_id)
ON DELETE RESTRICT ON UPDATE CASCADE,
rel_obj_int_id INTEGER(7) NOT NULL, rel_os_id INTEGER(7) NOT NULL,
rel_db_id INTEGER(7) NOT NULL, obj_path VARCHAR(255) NOT NULL,
obj_name VARCHAR(255) NOT NULL, obj_4n6_purpose BLOB NULL,
obj_info_source BLOB NULL, obj_comments BLOB NULL,
obj_md5_value VARCHAR(43) NOT NULL, obj_stat_dyn VARCHAR(8) NULL,
obj_byte_size VARCHAR(16) NOT NULL, obj_category VARCHAR(255) NULL,
obj_rec_last_update DATE NOT NULL, obj_rec_orgin DATE NOT NULL) TYPE =
INNODB;
Here are the lines of perl code that I am using.
open(INPUT,$filename) || die "Check to see if $filename is a valid file.\n";
$sth = $dbh->prepare(q{INSERT INTO
object_attrib(obj_path,obj_name,obj_byte_size,obj_md5_value) VALUES
(?,?,?,?)}) || die $dbh->errstr;
while ()
{
chomp;
($obj_path,$obj_name,$obj_byte_size,$obj_md5_value) = split /,/;
$sth->execute($obj_path,$obj_name,$obj_byte_size,$obj_md5_value) || die
$dbh->errstr;
}
close INPUT;
$dbh->disconnect;
Can someone tell me why I get the following error:
Cannot add or update a child row: a foreign key constraint fails.
Thanks,
Brian Croniser
You're not assigning an explicit value to any of your foreign
keys. Apparently the default value (0) is not present in one or
the other of the referenced keys in the parent table?
--
Paul DuBois, Senior Technical Writer
Madison, Wisconsin, USA
MySQL AB, www.mysql.com
Are you MySQL certified? http://www.mysql.com/certification/
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: - Foreign key constraints
maxim, Tuesday, May 28, 2002, 4:47:13 PM, you wrote: m> Question. I try to understand with " 4.3 Foreign key constraints " m> InnoDB Engine in MySQL-Max-3.23.50/MySQL-4.0.1. m> I use MySQL Max (InnoDB) 3.23.49 for Win2000 m> I have created two tables: m> CREATE TABLE parent (id INT NOT NULL, m> PRIMARY KEY (id)) TYPE=INNODB; m> CREATE TABLE child (id INT, parent_id INT, m> INDEX par_ind (parent_id), m> FOREIGN KEY (parent_id) REFERENCES parent (id) m> ON DELETE CASCADE m> ) TYPE=INNODB; m> As I have understood, this design in cascade allows to delete record m> child table at removal(distance) of the line connected to it(her) parent table. m> Or in other words to organize cascade removal(distance) of a line in child m> table at removal(distance) of the connected line in parent table. m> Problem in the following. At attempt of removal(distance): m> delete from parent where id = "1"; m> There is a mistake: Error: Cannot delete a parent row: a foreign key m> constraint: fails. ON DELETE CASCADE have worked since 3.23.50, 3.23.49 doesn't support ON DELETE CASCADE. So, you should first delete rows from child table that have parent_id=1 and then delete row from parent table. m> Probably it is connected with blocking? I do not understand. How to remove m> a line from parent table so that the line child table has in cascade left m> connected on a key parent_id? -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Egor Egorov / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net <___/ www.mysql.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: FOREIGN KEY Constraints
Hi, Yes, you are right but be aware with the delete cascade feature, maybe in the way?. Regards - Original Message - From: Carl Schmidt <[EMAIL PROTECTED]> To: <[EMAIL PROTECTED]> Sent: Sunday, April 07, 2002 7:16 PM Subject: FOREIGN KEY Constraints > From the mysql docs, it looks like you can only use foreign keys if your > tables are type InnoDB. Is this correct? > > Carl > > > - > Before posting, please check: >http://www.mysql.com/manual.php (the manual) >http://lists.mysql.com/ (the list archive) > > To request this thread, e-mail <[EMAIL PROTECTED]> > To unsubscribe, e-mail <[EMAIL PROTECTED]> > Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php > - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: FOREIGN KEY Constraints
Carl, Monday, April 08, 2002, 3:16:26 AM, you wrote: CS> From the mysql docs, it looks like you can only use foreign keys if your CS> tables are type InnoDB. Is this correct? Yes, you are right. In MyISAM tables you can use REFERENCE clause, but it does nothing. CS> Carl -- For technical support contracts, goto https://order.mysql.com/ This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Victoria Reznichenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net <___/ www.mysql.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
