Update table where there is FOREIGN KEY reference

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2008 » T-SQL (SS2K8) » Update table where there is FOREIGN KEY...

Update table where there is FOREIGN KEY reference

Rate Topic

Display Mode

Topic Options

Author

Message

Smash125

Posted Sunday, August 05, 2012 3:41 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, May 09, 2013 7:04 AM
Points: 157, Visits: 833

Want to update t4 table
but update fails as there is relationship between tables. Right there is one to many relationship between T3 and T4

CREATE TABLE T3
(
FirstName NVARCHAR(255),
MiddleName NVARCHAR(255),
LastName NVARCHAR(255),
ContactID INT,
CONSTRAINT pk_T3_pid PRIMARY KEY(ContactID)
)
GO
CREATE TABLE T4
(
ContactID INT,
SalesOrderID INT,
TotalDue MONEY,
CONSTRAINT pk_T4_sid PRIMARY KEY(SalesOrderID),
CONSTRAINT fk_T4_sid FOREIGN KEY(ContactID) REFERENCES T3(ContactID)
)
GO

I want to update T4 table for ContactID= 1 where i want to update SalesOrderID and TotalDue

How to go ahead with this kind of update

Post #1340256

SomewhereSomehow

Posted Sunday, August 05, 2012 7:32 AM

SSC Journeyman

Group: General Forum Members
Last Login: Tuesday, May 14, 2013 10:52 PM
Points: 75, Visits: 410

You may disable constraint like that
alter table dbo.T4 nocheck constraint fk_T4_sid
and do what you want, but, be aware of performance problems. I blogged about it here (unfortunatelly it is only in russian, but t-sql and plans talk for them selfes).
To have all the benefits of FK, your FK should be trusted, that means, that there should be a solid integrity of data and no missing values of fk column for pk.

I am really sorry for my poor gramma. And I hope that value of my answers will outweigh the harm for your eyes.
Blog: http://somewheresomehow.ru
Twitter: @SomewereSomehow

Post #1340268

« Prev Topic | Next Topic »

Permissions