Temporary Disable\enable Constraints (Foreign Key, Check Constriant, Unique Key)

Database
Enthusiast

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

13 REPLIES
Enthusiast

Re: Temporary Disable\enable Constraints (Foreign Key, Check Constriant, Unique Key)

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/Alte...

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.

Junior Supporter

Re: Temporary Disable\enable Constraints (Foreign Key, Check Constriant, Unique Key)

Hi.

There is no ALTER TABLE MODIFY CONSTRAINT DISABLE functionality in Teradata.

Oracle is Oracle, Teradata is Teradata.

Cheers.

Carlos.

Enthusiast

Re: Temporary Disable\enable Constraints (Foreign Key, Check Constriant, Unique Key)

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

Enthusiast

Re: Temporary Disable\enable Constraints (Foreign Key, Check Constriant, Unique Key)

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?

Enthusiast

Re: Temporary Disable\enable Constraints (Foreign Key, Check Constriant, Unique Key)

I again ran the same command after restarting my Studio and it works.

I don't know why.

thanks.

Parth

Enthusiast

Re: Temporary Disable\enable Constraints (Foreign Key, Check Constriant, Unique Key)

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

Enthusiast

Re: Temporary Disable\enable Constraints (Foreign Key, Check Constriant, Unique Key)

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.

Enthusiast

Re: Temporary Disable\enable Constraints (Foreign Key, Check Constriant, Unique Key)

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

Junior Supporter

Re: Temporary Disable\enable Constraints (Foreign Key, Check Constriant, Unique Key)

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.