Setting up SQL Relationships?

23 Nov 2007 4:29 AM

Mark Moss

Help

        I have not worked with SQL Server For several years and I have the
following question.

        I want to set up a relationship between a Parent Table and a Child
table, where the Parent can be deleted and the children are left. I also
want the Parent to exists before a Child can be added.

        I have set my PrimaryKey Table and Foreign Key Table,

        Unchecked Check existing data on creation
        Checked Enforce relationship for replication
        Checked Enforce relationship for INSERTs and UPDATEs
            Unchecked Cascade Update Related Fields
            Unchecked Delete Related Records

        Whenever I try to delete a record from the Parent Table I get an
error informing me that their are child records related to this record and
thus I cannot delete the Parent.

        What should I have set my relationship to be ?

MarkMoss

23 Nov 2007 5:21 AM

Ben Nevarez

Hi Mark,

If you want a table relationship where "parents can be deleted and the
children are left" implementing foreign keys may not be the solution you are
looking for. You will not be able to delete parent records in this scenario.

If you want a "parent to exists before a child can be added" you can
implement that in an insert trigger. The trigger should validate that the
parent exists.

Hope this helps,

Ben Nevarez
Senior Database Administrator
AIG SunAmerica

Show quoteHide quote

"Mark Moss" wrote:

> Help
>
>
>         I have not worked with SQL Server For several years and I have the
> following question.
>
>         I want to set up a relationship between a Parent Table and a Child
> table, where the Parent can be deleted and the children are left. I also
> want the Parent to exists before a Child can be added.
>
>         I have set my PrimaryKey Table and Foreign Key Table,
>
>         Unchecked Check existing data on creation
>         Checked Enforce relationship for replication
>         Checked Enforce relationship for INSERTs and UPDATEs
>             Unchecked Cascade Update Related Fields
>             Unchecked Delete Related Records
>
>
>         Whenever I try to delete a record from the Parent Table I get an
> error informing me that their are child records related to this record and
> thus I cannot delete the Parent.
>
>
>         What should I have set my relationship to be ?
>
>
> MarkMoss
>
>
>