Very Computer

Hi!
I have a problem here and it is putting me NUTS.
Here is my situation:
I've two tables named "PRODUCT" and "PTYPE" and a FK constraint from
"PRODUCT" to "PTYPE" named "FK_PRODUCT_PTYPE".

PRUDUCT
id <- Primary key
name
ptype

PTYPE
id <- Primary key
description

PRODUCT.ptype FK constraint to PTYPE.id

TABLE PTYPE have 1 row
id description
--  ---------------------
A   ptype B

TABLE PRODUCT have 1 row
id name            ptype
--  -------------  --
P1  product P1      A

In my application I need to disable and reenable the constraint
"FK_PRODUCT_PTYPE" to make some updates to my tables.

If I execute a STORE PROCEDURE that have this statement:
    - exec('update PTYPE set id=''B'' where id=''A''')
the SQL Server raise an error:
    - UPDATE statement conflicted with COLUMN REFERENCE constraint
'FK_PRODUCT_PTYPE'.
    The conflict occurred in database 'XXX', table 'PRODUCT', column
'PTYPE'.
until now everything is ok.
If I execute a second STORE PROCEDURE that have this statements:
    - exec('alter table PRODUCT NOCHECK constraint FK_PRODUCT_PTYPE')
    - exec('update PTYPE set id=''B'' where id=''A''')
    - exec('alter table PRODUCT CHECK constraint FK_PRODUCT_PTYPE')
    - exec('update PRODUCT set ptype=''B'' where ptype=''A''')
again it works fine. But ...
if then I execute a third STORE PROCEDURE that have this statement:
    - exec('update PTYPE set id=''A'' where id=''B''')
the SQL Server DO NOT raise any error. It must raise an error because I have
a fk constraint between PRODUCT and PTYPE tables that have to be checked by
SQL SERVER, I suppose?!?!?.

What I'm doing wrong!?!?
Please help me.

Thanks in Advance,
Regards,
Mrio

Try this ... please.
---------------------------------

Create Table dbo.PRODUCT
 (
 id char(2) Not Null,
 name varchar(50) Null,
 ptype char(1) Null
 )

Alter Table dbo.PRODUCT Add Constraint
 PK_PRODUCT Primary Key Nonclustered
 (
 id
 )

Create Table dbo.PTYPE
 (
 id char(1) Not Null,
 description varchar(50) Null
 )

Alter Table dbo.PTYPE Add Constraint
 PK_PTYPE Primary Key Nonclustered
 (
 id
 )

Alter Table dbo.PRODUCT Add Constraint
 FK_PRODUCT_PTYPE Foreign Key
 (
 ptype
 ) References dbo.PTYPE
 (
 id
 )

Insert Into PTYPE(id, description) Values('A', 'ptype B')

Insert Into PRODUCT(id, name, ptype) Values('P1', 'product P1', 'A')

Create Procedure spP01
As
 exec('update PTYPE set id=''B'' where id=''A''')
Return

Create Procedure spP02
As
 exec('alter table PRODUCT NOCHECK constraint FK_PRODUCT_PTYPE')
 exec('update PTYPE set id=''B'' where id=''A''')
 exec('alter table PRODUCT CHECK constraint FK_PRODUCT_PTYPE')
 exec('update PRODUCT set ptype=''B'' where ptype=''A''')
Return

Create Procedure spP03
As
 exec('update PTYPE set id=''A'' where id=''B''')
Return

Exec spP01
Exec spP02
Exec spP03

Statement three of stored procedure two needs to have the follwoing format

    exec('alter table PRODUCT WITH CHECK CHECK constraint FK_PRODUCT_PTYPE')

When renabling a check or foreign key constraint you must specify WITH CHECK
or the existing data will not be validated against the constraint.

--Buddy

Quote:> Hi!
> I have a problem here and it is putting me NUTS.
> Here is my situation:
> I've two tables named "PRODUCT" and "PTYPE" and a FK constraint from
> "PRODUCT" to "PTYPE" named "FK_PRODUCT_PTYPE".

> PRUDUCT
> id <- Primary key
> name
> ptype

> PTYPE
> id <- Primary key
> description

> PRODUCT.ptype FK constraint to PTYPE.id

> TABLE PTYPE have 1 row
> id description
> --  ---------------------
> A   ptype B

> TABLE PRODUCT have 1 row
> id name            ptype
> --  -------------  --
> P1  product P1      A

> In my application I need to disable and reenable the constraint
> "FK_PRODUCT_PTYPE" to make some updates to my tables.

> If I execute a STORE PROCEDURE that have this statement:
>     - exec('update PTYPE set id=''B'' where id=''A''')
> the SQL Server raise an error:
>     - UPDATE statement conflicted with COLUMN REFERENCE constraint
> 'FK_PRODUCT_PTYPE'.
>     The conflict occurred in database 'XXX', table 'PRODUCT', column
> 'PTYPE'.
> until now everything is ok.
> If I execute a second STORE PROCEDURE that have this statements:
>     - exec('alter table PRODUCT NOCHECK constraint FK_PRODUCT_PTYPE')
>     - exec('update PTYPE set id=''B'' where id=''A''')
>     - exec('alter table PRODUCT CHECK constraint FK_PRODUCT_PTYPE')
>     - exec('update PRODUCT set ptype=''B'' where ptype=''A''')
> again it works fine. But ...
> if then I execute a third STORE PROCEDURE that have this statement:
>     - exec('update PTYPE set id=''A'' where id=''B''')
> the SQL Server DO NOT raise any error. It must raise an error because I
have
> a fk constraint between PRODUCT and PTYPE tables that have to be checked
by
> SQL SERVER, I suppose?!?!?.

> What I'm doing wrong!?!?
> Please help me.

> Thanks in Advance,
> Regards,
> Mrio

Hi! Thanks for your awnser ... but ...
You say:

Quote:>When renabling a check or foreign key constraint you must specify WITH
CHECK
>or the existing data will not be validated against the constraint.

That's the point. I don't want to validate the data at this time. I want a
validation in
statement one of stored procedure three.
I think that if I alter the constraint like this "... PRODUCT CHECK
constraint ..." the statement one of SP three will fail, right? Wrong, it is
executed without any errors.

Quote:>Statement three of stored procedure two needs to have the follwoing format

>    exec('alter table PRODUCT WITH CHECK CHECK constraint
FK_PRODUCT_PTYPE')

>When renabling a check or foreign key constraint you must specify WITH
CHECK
>or the existing data will not be validated against the constraint.

>--Buddy

Mario,

Please post to the newsgroup only.

I am actually taking a look at your problem but in doing so, I'm getting
some abnormal behaviour from my SQL Server which is different from what you
get. So I need to figure out what's wrong with mine first. If I figure
anything out, I'll let you know.

Michael MacGregor
Senior Database Manager
Timeline Technology Inc.
www.timelinetech.com