June 15, 2012 at 7:49 pm
Hello everyone,
I am having trouble adding records into a Facts table, because it is linked to other dimension tables through keys. I have a Facts/Sales/Invoice table that has the following columns:
InvoiceNumber (Primary Key)
Order_Date (Foreign Key, connected to the Primary Key of the DimCalendar table)
CustomerID (Foreign Key, connected to the Primary Key of the DimCustomers table)
ProductID (Foreign Key, connected to the Primary Key of the DimProducts table)
Quantity
Price
TotalAmount
When I try to insert records into this table, I receive the following error:
[font="Courier New"]Msg 547, Level 16, State 0, Line 1
The INSERT statement conflicted with the FOREIGN KEY constraint "Order_Date". The conflict occurred in database "TransDB", table "dbo.DimCalendar", column 'Order_Date'.
The statement has been terminated.[/font]
It appears that it will not let me insert into the table unless the values exist in each of their corresponding dimension tables. Is there anyway around this aside from dropping the key constraints on all the other tables? If I remove the key constraints, it will allow me to insert.
Thanks in advance!
Dj
June 15, 2012 at 11:46 pm
If you don't have a valid FK for the date or customer or product, then why do you want it in the fact table?
June 16, 2012 at 12:03 am
foreign key constraints does not allow to insert value in the child table without inserting the value in the parent table. if you want to insert data in the child table then remove the foreign key constraints. if you want to delete the data directly from the child table than use cascading.
I hope it will give some help
June 16, 2012 at 5:15 pm
Thanks all, it worked when I added the date value in the Date dimension table (the primary key table) first before trying to insert it into the Facts table. The Date dimension table was the primary key table for all dates and the Facts table was linked to it using a foreign key. Looks like the issue was that I was creating an orphaned child record with no parent record.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply