relationship question

22 Dec 2005 12:38 AM

Kenny M.

Hi It may be a stupid question but I can not see why I define a relationship
and disable the option "Disabling a Foreign Key Constraint with INSERT and
UPDATE Statements"

in this case I just dont define the relationship.

Somebody can say a comment.

22 Dec 2005 8:11 AM

Jens

You can define FK on tables on simply disable them for loading
maintainance or other purposes, and then enable them again. Otherwise
you would have to drop them and recreate them.

CREATE TABLE SomeParentTable
(
        ParentPKCol INT
        CONSTRAINT PK_SomeParentTable PRIMARY KEY (ParentPKCol)
)

Create table SomeChildTable
(
        PKChildCol INT,
        ParentPKCol INT NOT NULL
      CONSTRAINT fk1_SomeParentTable
      FOREIGN KEY
      REFERENCES SomeParentTable (ParentPKCol)
)

--Doesn´t work, CHECK is enabled
insert into SomeChildTable Values (1,1)

--Disabling the CHECK
ALTER TABLE SomeChildTable NOCHECK CONSTRAINT fk1_SomeParentTable
--This works now
insert into SomeChildTable Values (1,1)

--Delete it once again
DELETE FROM SomeChildTable

--THis is the normal behaviour
ALTER TABLE SomeChildTable NOCHECK CONSTRAINT fk1_SomeParentTable

--Inserting first the parent then the child records
insert into SomeParentTable Values (1)
insert into SomeChildTable Values (1,1)

Drop table SomeChildTable

Drop table SomeParentTable

HTH, Jens Suessmeyer.

22 Dec 2005 9:34 PM

Kenny M.

Ohh absolutly, now I see the reason

--
Kenny M.

Show quote

"Jens" wrote:

>
> You can define FK on tables on simply disable them for loading
> maintainance or other purposes, and then enable them again. Otherwise
> you would have to drop them and recreate them.
>
>
>
> CREATE TABLE SomeParentTable
> (
>         ParentPKCol INT
>         CONSTRAINT PK_SomeParentTable PRIMARY KEY (ParentPKCol)
> )
>
> Create table SomeChildTable
> (
>         PKChildCol INT,
>         ParentPKCol INT NOT NULL
>       CONSTRAINT fk1_SomeParentTable
>       FOREIGN KEY
>       REFERENCES SomeParentTable (ParentPKCol)
> )
>
>
> --DoesnÂ´t work, CHECK is enabled
> insert into SomeChildTable Values (1,1)
>
> --Disabling the CHECK
> ALTER TABLE SomeChildTable NOCHECK CONSTRAINT fk1_SomeParentTable
> --This works now
> insert into SomeChildTable Values (1,1)
>
> --Delete it once again
> DELETE FROM SomeChildTable
>
> --THis is the normal behaviour
> ALTER TABLE SomeChildTable NOCHECK CONSTRAINT fk1_SomeParentTable
>
> --Inserting first the parent then the child records
> insert into SomeParentTable Values (1)
> insert into SomeChildTable Values (1,1)
>
>
> Drop table SomeChildTable
>
> Drop table SomeParentTable
>
>
>
> HTH, Jens Suessmeyer.
>
>