Weiss Posted September 7, 2017 Share Posted September 7, 2017 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 ! Link to comment Share on other sites More sharing options...
justsomeguy Posted September 7, 2017 Share Posted September 7, 2017 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. Link to comment Share on other sites More sharing options...
Weiss Posted September 7, 2017 Author Share Posted September 7, 2017 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? Link to comment Share on other sites More sharing options...
justsomeguy Posted September 7, 2017 Share Posted September 7, 2017 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, ...) Link to comment Share on other sites More sharing options...
Weiss Posted September 7, 2017 Author Share Posted September 7, 2017 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? Link to comment Share on other sites More sharing options...
davej Posted September 7, 2017 Share Posted September 7, 2017 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. 1 Link to comment Share on other sites More sharing options...
justsomeguy Posted September 7, 2017 Share Posted September 7, 2017 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. 1 Link to comment Share on other sites More sharing options...
justsomeguy Posted September 7, 2017 Share Posted September 7, 2017 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. 1 Link to comment Share on other sites More sharing options...
Recommended Posts
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 accountSign in
Already have an account? Sign in here.
Sign In Now