All Forums Database
parthmalhan 26 posts Joined 09/14
23 Sep 2014
Temporary Disable\enable Constraints (Foreign Key, Check Constriant, Unique Key)

Hi,
 
I want to Update a table. But before that i want disable foreign key\check Constraint\Unique Key on some tables.
After update, i want to enable those disables constraint again.
 
Can you please tell me how can i do that.
 
Parth

Raja_KT 1246 posts Joined 07/09
23 Sep 2014

You can use alter statement with drop and alter.Make a backup of scripts. Hope you have a name for fk,check and unique key constraints. It is better to have names:
http://www.info.teradata.com/HTMLPubs/DB_TTU_14_00/index.html#page/SQL_Reference/B035_1144_111A/Alter_Function-Syntax.020.052.html
Also it seems the document for add and drop is missing a syntax, if I am not wrong
ALTER TABLE table_1 ADD constraint ck1 (column_2 > 100);
If the values do not match the constraints, then it will fail.
 

Raja K Thaw
My wiki: http://en.wikipedia.org/wiki/User:Kt_raj1
Street Children suffer not by their fault. We can help them if we want.

CarlosAL 512 posts Joined 04/08
23 Sep 2014

Hi.
There is no ALTER TABLE MODIFY CONSTRAINT DISABLE functionality in Teradata.
Oracle is Oracle, Teradata is Teradata.
Cheers.
Carlos.

parthmalhan 26 posts Joined 09/14
23 Sep 2014

Thanks for information.
Actually i'm doing this in a loop.
So i need to drop them dynamically.
 
The problem is the following statement, which is not working.But even its not giving any error.
It says. Call Completed.
 
CALL CALLME('ALTER TABLE testdb.tab drop Constraint Forkey1;COMMIT WORK;');
 
DO you have any idea, why its behaving like this

Raja_KT 1246 posts Joined 07/09
23 Sep 2014

You mean to say the DDL remains the same before alter and after  alter, when you do : show table testdb.tab ?
Do you need commit work for a ddl?
can you test it independently?

Raja K Thaw
My wiki: http://en.wikipedia.org/wiki/User:Kt_raj1
Street Children suffer not by their fault. We can help them if we want.

parthmalhan 26 posts Joined 09/14
24 Sep 2014

I again ran the same command after restarting my Studio and it works.
I don't know why.
thanks.
 
Parth

parthmalhan 26 posts Joined 09/14
24 Sep 2014

Hi,
 
Now some other problem with same issue.
There are some composite keys in my DB.
When i drop and Recreate them, i get error.
 
Following is the simple sample script which will generate error.
Please Help.

/* Create A Table with Composite Primary Key */
CREATE MULTISET TABLE compositePrimaryKey ,NO FALLBACK ,
     NO BEFORE JOURNAL,
     NO AFTER JOURNAL,
     CHECKSUM = DEFAULT,
     DEFAULT MERGEBLOCKRATIO
     (
      a INTEGER NOT NULL,
      b INTEGER NOT NULL, 
CONSTRAINT ab PRIMARY KEY ( a ,b ));

/* Create Table to reference Above Table */
CREATE MULTISET TABLE compositeForeignKey ,NO FALLBACK ,
     NO BEFORE JOURNAL,
     NO AFTER JOURNAL,
     CHECKSUM = DEFAULT,
     DEFAULT MERGEBLOCKRATIO
     (
      FOREIGNa INTEGER,
      FOREIGNb INTEGER,
      testcolumn VARCHAR(50) CHARACTER SET LATIN CASESPECIFIC)
PRIMARY INDEX ( FOREIGNa );

/* (Important Part) : Create a Foreign Key Constraint on compositeForeignKey Table */
ALTER TABLE compositeForeignKey 
	ADD Constraint FK_CompositePrimaryKey 
	FOREIGN KEY(FOREIGNa,FOREIGNb) 
	REFERENCES compositePrimaryKey(a,b);
/* Above Table created a new table with name COMPOSITEFOREIGNKEY_0 internally.
 * You can CHECK that in tables list. Just refresh Tables node.
 * */

/* Now we will drop Foreign Key Constraint, that we just created */
ALTER TABLE compositeForeignKey DROP constraint FK_CompositePrimaryKey;

/* Recreate Constraint */
ALTER TABLE compositeForeignKey 
	ADD Constraint FK_CompositePrimaryKey 
	FOREIGN KEY(FOREIGNa,FOREIGNb) 
	REFERENCES compositePrimaryKey(a,b);
/* This will give you following error 
 * 
 * Executed as Single statement.  Failed [5303 : HY000] Error table 'TESTDB.compositeForeignKey_0' already exists.
 * STATEMENT 1: Alter Table failed.
 * 
 * */ 

 
Can someone tell me why this new table is created.
and why it didn't get dropped, when i dropped constraint.

Parth

Raja_KT 1246 posts Joined 07/09
24 Sep 2014

Hope you verified each and every step you peform. You can use show table command to verify. Are you shure your penultimate alter works fine?
You can drop and resubmit your create request from compositeForeignKey and see.

Raja K Thaw
My wiki: http://en.wikipedia.org/wiki/User:Kt_raj1
Street Children suffer not by their fault. We can help them if we want.

parthmalhan 26 posts Joined 09/14
24 Sep 2014

i verified all the statements.
penultimate ALTER Statement is working fine. it dropped only Constraint from compositeForeignKey, But it did not drop table "compositeForeignKey_0".
When i create Foreign Key at the time of Table Creation, it doesn't create "compositeForeignKey_0" table.
 
Why so.
 
Parth

CarlosAL 512 posts Joined 04/08
24 Sep 2014

Hi.
This is the expected behaviour. When you implement a constraint at the table creation there is no errortables '*_0'.
The error tables are created when 'ALTER TABLE... ADD CONSTRAINT...' is executed.
HTH.
Cheers.
Carlos.
 

parthmalhan 26 posts Joined 09/14
24 Sep 2014

Thanks for this valuable info.
 
Should i Check for this table every time when i'm altering table, And delete it if exists?
Or is there any setting that will delete this automatically if it exists, when i alter table ?
 
Parth

dnoeth 4628 posts Joined 11/04
24 Sep 2014

Hi Parth,
why do you want to disable/enable FKs? Due to bad performance?
In a DWH you hardly find FKs and if they're usually implemented using REFRENCE WITH (NO) CHECK OPTION.

Dieter

parthmalhan 26 posts Joined 09/14
25 Sep 2014

Thanks
I Have Created a Seapare Procedure which will Drop All Tables having myTableNameToMask || '
0' before Adding Constraint.
 
Thanks all.
 
Parth

parthmalhan 26 posts Joined 09/14
25 Sep 2014

that was myTableNameToMask || '_0' in above comment.
Spell Mistake

Parth

You must sign in to leave a comment.