Unanswered: Insert statements, primary keys and relationships
I have a number of tables (30+) all collecting info about societies. I have a primary key (soc_ID) in all my tables and a number of one-to-one relationships. All the tables are joined.
Q1 : If I add into the first row in one table, do I HAVE to insert the PK in EVERY other table its related to? Q2:is it wise to have foreign keys nullable?
My MS SQL SERVER statement::
insert into society(soc_id) values ('1');
ERROR: The INSERT statement conflicted with the FOREIGN KEY constraint "FK_Society_EDU_materials_targeted". The conflict occurred in database "mem_soc", table "dbo.EDU_materials_targeted", column 'soc_id'.
The statement has been terminated.
I understand the error. Is there a short way to insert the PK in all related tables automatically otherwise my insert statement will be HUGE.
This method seems REALLY long winded...
I'm 99% sure that you've got the primary and foreign keys reversed between your EDU_materials_targeted and society tables. At least as I understand it, the soc_id should be the primary key in the society table, and it should be the foreign key in the dbo.EDU_materials_targeted table.
A foreign key should allow NULL values if the relationship has a cardinality of "1 to zero or more". In other words, if the child table might not have a matching row in the parent table, then the FK should allow NULL values.
Normally I'd move this discussion to the Microsoft SQL Server forum, but since it still is a relatively pure SQL issue I'm Ok with leaving it here in the SQL forum.