June 15, 2018 at 8:39 pm
Hello all,
I am using SQL Server Express 2017, and I have a table with two foreign keys I am attempting to insert data into from another table.
However, the insert statement gives an error stating that there was a conflict with FK... constraint with (Foreign Key Table) on column (ID). I ended up deleting that FK, and got the same error on the other FK. I made sure there was data in the Foreign Key table to validate the insert into the Main table. Now, I don't want to insert a bunch of records without the ability to have this FK to the other two tables.
Thoughts?
thanks,
Mike
June 18, 2018 at 6:23 am
mjdemaris - Friday, June 15, 2018 8:39 PMHello all,
I am using SQL Server Express 2017, and I have a table with two foreign keys I am attempting to insert data into from another table.
However, the insert statement gives an error stating that there was a conflict with FK... constraint with (Foreign Key Table) on column (ID). I ended up deleting that FK, and got the same error on the other FK. I made sure there was data in the Foreign Key table to validate the insert into the Main table. Now, I don't want to insert a bunch of records without the ability to have this FK to the other two tables.
Thoughts?
thanks,
Mike
Can you illustrate your problem with some actual data? I know of no issue with FKs in 2017 Express.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
June 18, 2018 at 6:40 am
mjdemaris - Friday, June 15, 2018 8:39 PMHello all,
I am using SQL Server Express 2017, and I have a table with two foreign keys I am attempting to insert data into from another table.
However, the insert statement gives an error stating that there was a conflict with FK... constraint with (Foreign Key Table) on column (ID). I ended up deleting that FK, and got the same error on the other FK. I made sure there was data in the Foreign Key table to validate the insert into the Main table. Now, I don't want to insert a bunch of records without the ability to have this FK to the other two tables.
Thoughts?
thanks,
Mike
If there were something we could actually look at, such as the actual table create statements for all the tables involved, we might have a way to do something other than just make a wild-a$$ guess. If we at least had the table create statements and some sample data along with the desired results and some descriptive language that indicates what a record in each of the tables involved actually represents, we might then be able to improve to a scientific wild-a$$ guess (aka SWAG), but without any of that, we're going to be just as blind to the problem as you are at the moment, and perhaps even more so.
That said, maybe reviewing what it means to have a foreign key in place will be helpful. With such a "constraint" in place, you will be unable to insert a record that does not contain a value for a column so constrained, where that value actually exists in the related table in the appropriate column. As an example, let's pretend we have a table of orders and a table of customers. You could have a foreign key constraint on the orders table for the customer_id column, that relates that row in the orders table to a specific row in the customers table, and let's assume the column in the customer table is also customer_id. In that scenario, once the foreign key constraint is in place and active, you would NOT be able to insert a row in the orders table without using a valid customer_id value in the customer_id column of the orders table, where "valid" means that the customer_id value must exist in the customer_id column of the customers table. You can't just insert the data in the orders table before it exists in the customers table. Can't tell you how often I've seen developers think they could insert the data in the orders table first, and then go back and insert into the customers table. That's not going to happen with a foreign key constraint in place, and that's the entire idea behind them. They prevent you from entering an order not tied to a customer. Does that help?
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
June 18, 2018 at 9:33 am
>> I am using SQL Server Express 2017, and I have a table with two foreign keys I am attempting to insert data into from another table. <<
How do you expect us to do anything for you? We don't even know the names of these tables! What do the references look like? Do they have DRI actions on them? Exactly what do your insertions look like -- we didn't even see the code you tried to execute!
Also, the correct terms are "referenced" and "referencing" tables. The terms "master" or "main" refer to tape files back in the 1950s and 1960s.
You do not insert records in SQL; you insert rows. These rows all come in as a set, not one record at a time like a file system. Finally, there is no such thing as a generic "id" in RDBMS; it has to be the identifier of something in particular. That was the old record numbers in filesystems, which were carried over and the first versions of SQL Server and Sybase in the form of the identity table property (it's not even a column).
Would you like to try again?
Please post DDL and follow ANSI/ISO standards when asking for help.
June 18, 2018 at 12:27 pm
It's likely you need to join the table you're loading from to the tables referenced by the foreign keys so you can insert the correct values.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply