Disable the Foreginekeys

Question

Disable the Foreginekeys

shiv-356842

SSCarpal Tunnel

Points: 4312
More actions
August 1, 2008 at 4:06 am

#190335

HI Team,
How can i disable the foreginekey in a table
Ist i can disable primary or foreginekey it is little bit confussing to me
can u any body give syntax on this
i am using
alter table employees
disable constraint emp_emp_id_Pk cascade
But it is not working could any help me plz

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

ChiragNS One Orange Chip Points: 26137 More actions · Answer 1

Hi

When you disable the primary key all the related foregin keys will be disabled. Be carefull with this as data integrity may be lost. With the contraints and foregin keys disabled wrong data may be inserted into the tables.

"Keep Trying"

shiv-356842 SSCarpal Tunnel Points: 4312 More actions · Answer 2

Yes you are correct we can diable the primaykey's related foregine keys also disable

so that is the reason how can i disable only foregine keys in a table

give the syntax....plz

ChiragNS One Orange Chip Points: 26137 More actions · Answer 3

Hi

Only foreignkeys and check constraints can be "disabled". The syntax is - ALTER TABLE Table1

NOCHECK Constraint ForeignkeyConstraintName.

PrimaryKeys cannot be disabled, they can be dropped. Dropping them removes the Foreign key relationships. If you are going to take this step be very very carefull.

"Keep Trying"

Bob Pinella SSCommitted Points: 1737 More actions · Answer 4

Hi,

It's also worth mentioning that if you need to re-enable your foreign keys, you have the option of verifying the integrity of these keys. For example:

Alter Table [YourTableName]

WITH CHECK

CHECK CONSTRAINT [YourForeignKeyName]

"WITH CHECK" will verify that your foreign key data is still valid(Meaning that it verifies all rows in one table have corresponding records in another table referenced by the foreign key). If the data is not valid, an error message is returned. You do not need to include the "WITH CHECK" argument, but keep in mind that although future inserts will be checked against your foreign key constraint, your current foreign key data may not be valid. If you do receive an error by using the "WITH CHECK" option, you will need to correct the invalid data and then run the statement again. You can check to see if any of your foreign keys have invalid data by querying from the following system view:

select * from sys.foreign_keys

This DMV contains a column called "is_not_trusted" which indicates whether a foreign key is not valid.

Bob Pinella