good sql reference?

from 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?? :grin:  

24,313 views 5 replies
Reply #1 from GalCiv2 Forums Top

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 from Demigod Forums Top

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. 

Offworld Trading Company
Reply #3 from Sins Forums Top

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 #4 from Demigod Forums Top

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 #5 from Sins Forums Top

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