novalidate option with the set constraints stateme
Informix 11.70.FC8W1GE All, I am trying to find valid syntax to incorporate the keywork NOVALIDATE in the SET CONSTRAINTS. Does anyone have a valid SQL command that will do this? I have tried every combination I can think of and nothing is working. I constantly get syntax errors. The reason for using this is because I am creating a copy of a production database that has inconsistencies in it. I know I can fix the inconsistencies before anyone suggests it. This will come later. In the meantime I was wondering if there was a way of telling Informix to ignore inconsistencies during data loading and this NOVALIDATE option seemed to be my savior but I cannot get it to work. All help appreciated.
SET CONSTRAINTS <constr=5Fname> ENABLED NOVALIDATE; As it appears the "FOR <table>" alternative doesn't work; on the other=20 hand this might be on purpose (and the docs incorrect) as the NOVALIDATE=20 option applies to foreign keys only, so the statement had to the=20 selection. Andreas From: "Andrew Grantham" <agrantha@hotmail.com> To: ids@iiug.org Date: 24.03.2016 15:40 Subject: novalidate option with the set constraints sta.... [36847] Sent by: ids-bounces@iiug.org Informix 11.70.FC8W1GE=20 All,=20 I am trying to find valid syntax to incorporate the keywork NOVALIDATE in=20 the=20 SET CONSTRAINTS. Does anyone have a valid SQL command that will do this? I = have tried every combination I can think of and nothing is working. I=20 constantly get syntax errors.=20 The reason for using this is because I am creating a copy of a production=20 database that has inconsistencies in it. I know I can fix the=20 inconsistencies=20 before anyone suggests it. This will come later. In the meantime I was=20 wondering if there was a way of telling Informix to ignore inconsistencies = during data loading and this NOVALIDATE option seemed to be my savior but=20 I=20 cannot get it to work.=20 All help appreciated.=20 ***************************************************************************= ****=20 Forum Note: Use "Reply" to post a response in the discussion forum.=20
Andreas,
Thank for the response. The syntax is good now but it still gives me
referential constraint errors.
Here is the SQL.
alter table "informix".task_bigbrothercheck add constraint (foreign
key (task) references "informix".task constraint "informix"
.for1_task_bigbrothercheck);
set constraints for1_task_bigbrothercheck enabled novalidate ;
load from task_bigbrothercheck.data insert into task_bigbrothercheck ;
Is this the correct order?
________________________________________
From: ids-bounces@iiug.org <ids-bounces@iiug.org> on behalf of Andreas Legner
<andreas.legner@de.ibm.com>
Sent: 24 March 2016 16:10
To: ids@iiug.org
Subject: Re: novalidate option with the set constraints.... [36848]
SET CONSTRAINTS <constr=5Fname> ENABLED NOVALIDATE;
As it appears the "FOR <table>" alternative doesn't work; on the other=20
hand this might be on purpose (and the docs incorrect) as the NOVALIDATE=20
option applies to foreign keys only, so the statement had to the=20
selection.
Andreas
From: "Andrew Grantham" <agrantha@hotmail.com>
To: ids@iiug.org
Date: 24.03.2016 15:40
Subject: novalidate option with the set constraints sta.... [36847]
Sent by: ids-bounces@iiug.org
Informix 11.70.FC8W1GE=20
All,=20
I am trying to find valid syntax to incorporate the keywork NOVALIDATE in=20
the=20
SET CONSTRAINTS. Does anyone have a valid SQL command that will do this? I =
have tried every combination I can think of and nothing is working. I=20
constantly get syntax errors.=20
The reason for using this is because I am creating a copy of a production=20
database that has inconsistencies in it. I know I can fix the=20
inconsistencies=20
before anyone suggests it. This will come later. In the meantime I was=20
wondering if there was a way of telling Informix to ignore inconsistencies =
during data loading and this NOVALIDATE option seemed to be my savior but=20
I=20
cannot get it to work.=20
All help appreciated.=20
***************************************************************************=
****=20
Forum Note: Use "Reply" to post a response in the discussion forum.=20
*******************************************************************************
Forum Note: Use "Reply" to post a response in the discussion forum.
You might be better off setting violations on the tables and then setting
the constraints to FILTER.
Art
Art S. Kagel, President and Principal Consultant
ASK Database Management
www.askdbmgt.com
Blog: http://informix-myview.blogspot.com/
Disclaimer: Please keep in mind that my own opinions are my own opinions
and do not reflect on the IIUG, nor any other organization with which I am
associated either explicitly, implicitly, or by inference. Neither do
those opinions reflect those of other individuals affiliated with any
entity with which I am affiliated nor those of the entities themselves.
On Fri, Mar 25, 2016 at 5:19 AM, Andrew Grantham <agrantha@hotmail.com>
wrote:
> Andreas,
>
> Thank for the response. The syntax is good now but it still gives me
> referential constraint errors.
>
> Here is the SQL.
>
> alter table "informix".task_bigbrothercheck add constraint (foreign
>
> key (task) references "informix".task constraint "informix"
>
> ..for1_task_bigbrothercheck);
>
> set constraints for1_task_bigbrothercheck enabled novalidate ;
> load from task_bigbrothercheck.data insert into task_bigbrothercheck ;>
> Is this the correct order?
>
> ________________________________________
> From: ids-bounces@iiug.org <ids-bounces@iiug.org> on behalf of Andreas
> Legner
> <andreas.legner@de.ibm.com>
> Sent: 24 March 2016 16:10
> To: ids@iiug.org
> Subject: Re: novalidate option with the set constraints.... [36848]
>
> SET CONSTRAINTS <constr=5Fname> ENABLED NOVALIDATE;
>
> As it appears the "FOR <table>" alternative doesn't work; on the other=20
> hand this might be on purpose (and the docs incorrect) as the NOVALIDATE=20
> option applies to foreign keys only, so the statement had to the=20
> selection.
>
> Andreas
>
> From: "Andrew Grantham" <agrantha@hotmail.com>
> To: ids@iiug.org
> Date: 24.03.2016 15:40
> Subject: novalidate option with the set constraints sta.... [36847]
> Sent by: ids-bounces@iiug.org
>
> Informix 11.70.FC8W1GE=20
>
> All,=20
>
> I am trying to find valid syntax to incorporate the keywork NOVALIDATE
> in=20
> the=20
> SET CONSTRAINTS. Does anyone have a valid SQL command that will do this? I
> =
>
> have tried every combination I can think of and nothing is working. I=20
> constantly get syntax errors.=20
>
> The reason for using this is because I am creating a copy of a
> production=20
> database that has inconsistencies in it. I know I can fix the=20
> inconsistencies=20
> before anyone suggests it. This will come later. In the meantime I was=20
> wondering if there was a way of telling Informix to ignore inconsistencies
> =
>
> during data loading and this NOVALIDATE option seemed to be my savior
> but=20
> I=20
> cannot get it to work.=20
>
> All help appreciated.=20
>
>
> ***************************************************************************=
> ****=20
>
> Forum Note: Use "Reply" to post a response in the discussion forum.=20
>
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
--047d7bd76bea1cdbee052edcd72e
Original post:
Andreas,
Thank for the response. The syntax is good now but it still gives me
referential constraint errors.
Here is the SQL.
alter table "informix".task_bigbrothercheck add constraint (foreign
key (task) references "informix".task constraint "informix"
.for1_task_bigbrothercheck);
set constraints for1_task_bigbrothercheck enabled novalidate ;
load from task_bigbrothercheck.data insert into task_bigbrothercheck ;
Is this the correct order?
Response:
That does not appear to be the correct order. It would appear that the
"enabled novalidate" is a 1 time thing...so if you enable the constraint
novalidate then current stuff in the table doesn't need to pass the constraint
check. However, you can't then insert new rows that won't qualify for the
constraint...so in your case you would need to delay adding the constraint
until after you load, then add the constraint in novalidate mode...something
like this:
load from task_bigbrothercheck.data insert into task_bigbrothercheck ;alter table "informix".task_bigbrothercheck add constraint (foreign key (task)
references "informix".task constraint "informix".for1_task_bigbrothercheck
disabled);
set constraints for1_task_bigbrothercheck enabled novalidate ;
Or you can do it in just 1 alter table like this:
alter table "informix".task_bigbrothercheck add constraint (foreign key (task)
references "informix".task constraint "informix".for1_task_bigbrothercheck
enabled novalidate);
Jacques Renaut
IBM Informix Advanced Support
APD Team