Results 1 to 3 of 3
  1. #1
    Join Date
    Mar 2006

    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...

  2. #2
    Join Date
    Feb 2004
    In front of the computer
    Provided Answers: 54
    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.



  3. #3
    Join Date
    Mar 2006

    Foriegn key is correct!

    Thanks a lot! You're 100% correct!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts