The Forums Are Now Closed!

The content will remain as a historical reference, thank you.

  • Home
  • Everything Else
  • Personal Computing

good sql reference?

By drugfreeboy on June 28, 2009 4:29:41 AM from Demigod Forums Demigod Forums

Anyone know of a good sql reference online that doesn't require any money from me? Specifically, I'm using MySql (version 4. something I think), and I'm working with this visual MySql editor called "Navicat Lite" that I found using google. Anyone ever heard of it? Is it stable and good to work with?

Not gonna go into detail about the issue I'm having since this isn't actually a tech forum, but... I set up some foreign key constraints...and now when I  try to enter data into my tables they keep giving me errors and stuff about failing the foreign key constraints. I'm jsut making entirely new rows too, I'm not deleting anything or changing any existing rows (to my knowledge).

W3schools was a bit too general, I need something with a little more detail on foreign keys by the looks of it...anyone got some nice links for me??  

+6 Karma | 5 Replies
Reply #1
kryo
  • Member No.662,525
  • Join Date05/2003
  • Karma+376
June 28, 2009 12:36:56 PM from GalCiv II Forums GalCiv II Forums

Google and the MySQL online reference should take care of most anything. Never heard of that app myself, I generally just use PhpMyAdmin.

 

I try to enter data into my tables they keep giving me errors and stuff about failing the foreign key constraints

Most likely you're trying to insert rows whose value you're constraining doesn't exisit in the linked table that the key originates in already. Ex:

Item Table:
Item_ID
Item_Description
Item_Price

Sales_Order Table:
Order_ID
Order_Item_ID
Order_Item_Qty

 

If you set up a foreign key constraint between Order_Item_ID and Item_ID, then you can't insert a new row into Sales_Order unless the Order_Item_ID value you give already has an existing entry in the Item table.

Reply #2
drugfreeboy
  • Member No.3,620,063
  • Join Date05/2009
  • Karma+6
June 28, 2009 3:46:43 PM from Demigod Forums Demigod Forums

Well PhpMyAdmin was my first choice, but I'm connecting to the database remotely. I'm doing this for a group I'm in, and the person who's actually in charge of the website didn't want to give me all the passwords and stuff to log in to the actual web host's site manager interface. If I had that then I could use PhpMyAdmin because then it would think I'm working locally. Atleast, I did try to use PhpMyAdmin and couldn't seem to do it. I will admit that this is my first time working on a sql project by myself and maybe I'm just missing something though.

And you're probly right about me inserting rows whose foreign key doesn't yet exist issue. I'll have to go try to insert some stuff again, but in different orders this time. 

Reply #3
Hack78
  • Member No.3,129,708
  • Join Date04/2008
  • Karma+50
June 28, 2009 8:21:42 PM from Sins of a Solar Empire Forums Sins of a Solar Empire Forums

Foreign Keys are uses to ensure you don't add data to a table that doesn't link to a parent table (this would create orphan records). Using Kyro's example you can't have a sales order until you have an item to link it to.

What this means is you need to populate your refererence data first before adding your other data. By this I mean you need to add things like country or states before setting up addresses, or items for sale before you add sales orders or setup suppliers before you setup items for sale etc...

My suggestion is to print out the database schema so you can see the relationships. If you can see them on paper, you can work out what is dependant on something else and work your way backwards to where you need to start.

One thing to remember too is that FK are there to protect your data integrity. However you should technically NEVER get a FK error as your application should ensure that everything is correct with the data BEFORE you submit the INSERT or UPDATE. Also be aware of cascading deletes. If your FK are setup with that option, deleting a parent row will remove ALL your child data. If you DON'T have that option, then you cannot perform the delete without deleting the child records first...

Reply #5
drugfreeboy
  • Member No.3,620,063
  • Join Date05/2009
  • Karma+6
June 29, 2009 1:30:22 AM from Demigod Forums Demigod Forums

ok it turns out that this navicat program has a little quirk, but it kinda makes sense. What happened was I had two tables open, and one of them had a foreign key that referenced the other. Well I made the row with the source primary key in the one that gets referenced, and that was fine. Then I tried to enter a row in the second one that has a foreign key referencing the first one...this program doesn't account for that. I had to close the second table then reopen it for it to realize that I had entered a new row with the primary key that I was trying to reference.

Reply #6
Hack78
  • Member No.3,129,708
  • Join Date04/2008
  • Karma+50
June 29, 2009 5:11:19 AM from Sins of a Solar Empire Forums Sins of a Solar Empire Forums

Hmm - Sounds like your data hadn't been committed to the database - closing performs the commit so it is then available to use...

  • Home
  • Everything Else
  • Personal Computing
View all recent posts
Mark all posts as read  •   Delete cookies created by the forum  •   Return to Top
Stardock Forums v1.0.0.0    #108435  walnut2   Server Load Time: 00:00:00.0000094   Page Render Time:

Stardock Magazine | Register | Online Privacy Policy | Terms of Use

Copyright ?? 2012 Stardock Entertainment and Gas Powered Games. Demigod is a trademark of Gas Powered Games. All rights reserved. All other trademarks and copyrights are the properties of their respective owners. Windows, the Windows Vista Start button and Xbox 360 are trademarks of the Microsoft group of companies, and 'Games for Windows' and the Windows Vista Start button logo are used under license from Microsoft. ?? 2012 Advanced Micro Devices, Inc. All rights reserved. AMD, the AMD Arrow logo and combinations thereof are trademarks of Advanced Micro Devices, Inc.