• 0

[PHP][SQL] Foreign Key usage help


Question

Hey guys,

I spent a few hours looking on google and the neowin forums too, and nothing that could really help me...

I have a table called USERS, and another table called SELLER, BUYER,

I have in my USERS table id (auto_increment), username, password

in the SELLER and BUYER table I have id (auto_increment), user_id (being referenced to `id` from the USERS table), first_name, last_name

ok so my question is... now what?

when a user registers will everything be automatic?

I have the registration script working to register my users, so I can make entries into the USERS table without a problem, but now how do I use the other tables with the foreign_keys?

my users have the ability to enter information into a form and have it be saved into either SELLER or BUYER but as of right now I get an error

Insertion Failed: Cannot add or update a child row: a foreign key constraint fails (`list_`.`seller`, CONSTRAINT `seller_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`))

my query is:

$sql = "INSERT INTO `seller` (`first_name`, `last_name`, `prop_address`, `city`, `zip`, `cell_phone`, `home_phone`, `other1`, `other2`)

VALUES ('$fName', '$lName', '$address', '$city', '$zip', '$cell', '$home', '$other1', '$other2')";

mysql_query($sql, $link) or die ("Insertion Failed: " . mysql_error());

I really don't know how this works, I can work without using foreign_keys and just automatically create entry rows and have the id = user_id from a session variable but I'm trying to have something

automatic put into place and learn from it.

can someone give me some help on the proper usage and perhaps a real world example that I can study and learn from using php+sql queries and how the sql database will know which user is logged on and how to make records for that particular user.

thanks guys

Link to comment
Share on other sites

1 answer to this question

Recommended Posts

  • 0

Well from the sql insert example you are showing us. You mention that the seller table has a field called "user_id" which is defined as a foreign key pointing to the USERS table. However in your SQL statement, you dont attempt to add any values into the user_id field, hence why it is failing. The user_id field, now being a foreign key is mandatory. The purpose of the foreign key is to ensure referential integrity of the data. In other words, only values that are supposed to appear in the database are permitted.

So Say you have 3 Users in your USERS table, and they all have IDs of 1 2 3 respectively. You go to insert into the sales table which itself has a user_id field that is marked as a foreign key - pointing to the USERS table ID field.

So you do a statement like this

INSERT INTO sales (user_id, Product) VALUES ('5','Toothbrush');

With a foreign key, there is no ID of 5 in the users table so it stops you from inserting that data into the table, to help enforce the integrity of the information, because you cant have information about a customer that doesnt exist (id of 5 in this case) in the sales table. I'm sure someone else can explain it better. A Foreign key really does not do anything for you automatically other than ensuring you dont have data sitting there that has no purpose. I think your idea on what the foreign key does is quite different.

Link to comment
Share on other sites

This topic is now closed to further replies.
  • Recently Browsing   0 members

    • No registered users viewing this page.