Jump to content
Sign in to follow this  
Weiss

primary and foregin key

Recommended Posts

HI!

im having a little understanding problem about keys. i have 2 tables. one with ID (primary key) and all kind of other stuff. now i have another table, e.g. adress, where i want to make a uniqe coloum connecting 1 to 1 with the ID of the main table. so what i did is connected the ID (primary key) of the first table to the ID (secondary key) of the second table. so how does that work now? because i cant insert anything to the second table since it gives me an error: #1452 - Cannot add or update a child row: a foreign key constraint fails (`logitech`.`cust_time`, CONSTRAINT `cust_time_ibfk_1` FOREIGN KEY (`order_num`) REFERENCES `customer` (`order_num`)).

how can i use the second table exactly? in this situation, the data to the second table should be inserted only a few days after the data to the first table.

thank you !

Share this post


Link to post
Share on other sites

If it's a 1-to-1 relationship, unless there's a good reason to separate those it's common to have everything in 1 table.

If you set up a foreign key that may not point to a record in another table, then allow that field to be nullable and insert null when you add the record.

Share this post


Link to post
Share on other sites

i dont think i understand. those are 1 to 1 tables. i can enter data to the main table but how will the data be insreted to the second table if i get an error and i try to insert?

Share this post


Link to post
Share on other sites

Allow null values in that field, and enter a null value for it when you insert the record.  e.g.:

INSERT INTO cust_time (order_num, ...) VALUES (null, ...)

Share this post


Link to post
Share on other sites

the idea is this, this first table get filled when customer buy product (primary key is order number which is uniqe, his id, address, name, etc.)

the second table is inserted after a few days when the customer enters the site and checkbox his day and time request for when he want the package to arrive.

so actually the second table doesnt get inserted at the same time the first one.

ive made the first table order number a primary key and wanted to make the second table order number as secondary key so they both point to each other. if i insert null value on the second table key how will it point to the firs table?

111.PNG

Share this post


Link to post
Share on other sites

The idea of a foreign key is that the database would not allow you to enter a non-existent order_number in your cust_time table. It would only accept order_numbers that already exist in the customer table.

  • Like 1

Share this post


Link to post
Share on other sites

The foreign key should be defined on the second table, not the first one.  The second table has a foreign key to the parent table.

  • Like 1

Share this post


Link to post
Share on other sites

Like I said though, with a 1-to-1 relationship you usually put all of that in one table.  It's not normal to have a primary key that is also a foreign key.

  • Like 1

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
Sign in to follow this  

×