How to insert or update a reference column for a foreign Key constraint

Source: Internet
Author: User

If a table is a foreign key to another table, the update or Insert of the Reference column for that table is subject to a number of restrictions. Disable Foreign Key Constraints with INSERT and UPDATE statements.

View FK information, mainly using Sys.foreign_keys and sys.foreign_key_columns tables, auxiliary tables: sys.objects (type= ' F '), Sys.tables,sys.columns

--Get FK listSelectFk.object_id  asfk_object_id, Fk.name asFk_name, Pt.name asParenttable_name, Pc.name asParenttable_column_name, Rt.name asReferencedtable_name, Rc.name asReferencedtable_column_name fromSys.foreign_keys FK with(NOLOCK)Inner JoinSys.foreign_key_columns FKC with(NOLOCK) onFk.object_id=fkc.constraint_object_idInner JoinSys.tables PT with(NOLOCK) onfkc.parent_object_id=Pt.object_idInner JoinSys.columns pc with(NOLOCK) onfkc.parent_object_id=Pc.object_id  andfkc.parent_column_id=pc.column_idInner JoinSys.tables asRt with(NOLOCK) onfkc.referenced_object_id=Rt.object_idInner JoinSys.columns RC with(NOLOCK) onfkc.referenced_object_id=Rc.object_id  andfkc.referenced_column_id=rc.column_idwhereRt.name=N'Referenced_table_name'--Disable FK constraintALTER TableParenttable_nameNocheck constraintFk_name--Enable FK constraintALTER TableParenttable_nameCheck constraintFk_name

Reference Msdn:alter TABLE (Transact-SQL)

ALTER TABLE  []CHECK |    NOCHECK CONSTRAINT  All | [] }

With CHECK | With NOCHECK

Specifies whether the data in the table was or is not validated against a newly added or re-enabled FOREIGN KEY or CHECK Co Nstraint. If not specified, with the CHECK is assumed for new constraints, and with NOCHECK are assumed for re-enabled constraints.

If you don't want to verify new CHECK or FOREIGN KEY constraints against existing data, use with NOCHECK. We don't recommend doing this, except in rare cases. The new constraint is evaluated in all later data updates. Any constraint violations that's suppressed by with NOCHECK when the constraint be added may cause future updates to FAI L If they update rows with data, does not comply with the constraint.

The query optimizer does not consider constraints that is defined with NOCHECK. Such constraints is ignored until they is re-enabled by using the ALTER table table with check check CONSTRAINT All.

{CHECK | NOCHECK} CONSTRAINT

Specifies that constraint_name is enabled or disabled. This option can is used with FOREIGN KEY and CHECK constraints. When NOCHECK was specified, the constraint is disabled and future inserts or updates to the column was not validated agains t the constraint conditions. DEFAULT, PRIMARY KEY, and UNIQUE constraints cannot be disabled.

All

Specifies that all constraints is either disabled with the NOCHECK option or enabled with the CHECK option.

Reference post:

How can I list all foreign keys referencing a given table in SQL Server?

How to insert or update a reference column for a foreign Key constraint

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.