Updating a Facts Table Linked by Keys

[Deepraj Kunnath]

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:
Msg 547, Level 16, State 0, Line 1
The INSERT statement conflicted with the FOREIGN KEY constraint "Order_Date". The conflict occurred in database "Digiprotech", table "dbo.DimCalendar", column 'Order_Date'.
The statement has been terminated.

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.

 

Thank you,

Deep

[Sridar K]

No, if F key constraint, thn u can't the master record. First clear the child rec and try deleting the master rec. more over dim doesn't req a f key cons. 

--
Thanks & Regards,

Sridar Krishnamoorthy

 +91  95354 88000

[Deep Kunnath]

Thanks Sridar, I tried removing the constraint and it worked. It also worked when I added the date value in the Date dimension 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.  If I added the Date in the Date dimension table first, then it would work.  Otherwise it would be an orphaned child record with no parent record and thus received an error