Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Update table where there is FOREIGN KEY reference Expand / Collapse
Author
Message
Posted Sunday, August 5, 2012 3:41 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: 2 days ago @ 4:13 AM
Points: 210, Visits: 1,211

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
Posted Sunday, August 5, 2012 7:32 AM


SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Friday, November 14, 2014 9:53 PM
Points: 75, Visits: 444
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 »

Add to briefcase

Permissions Expand / Collapse