October 12, 2016 at 10:27 am
In a sql server 2012 database, the first insert statement works that is listed below works:
insert into [dbo].Cust (custid,[number],lockid)
values (134,118692,1098)
The following insert statement does not work since there is foreign key constratint on the column called lockid
insert into [dbo].Cust (custid,[number],lockid)
select custid,[number],lockid
from [dbo].[Custest]
Thus is there a way to do the second insert statement without having to disable or drop the foregin key constratint?
If so, would you show me what the sql that would work?
If this does does work, would you show me the sql on how to disable and the reenable the foreign key constraint so the insert statement will work?
October 12, 2016 at 10:50 am
dianerstein 8713 (10/12/2016)
In a sql server 2012 database, the first insert statement works that is listed below works:insert into [dbo].Cust (custid,[number],lockid)
values (134,118692,1098)
The following insert statement does not work since there is foreign key constratint on the column called lockid
insert into [dbo].Cust (custid,[number],lockid)
select custid,[number],lockid
from [dbo].[Custest]
Thus is there a way to do the second insert statement without having to disable or drop the foregin key constratint?
If so, would you show me what the sql that would work?
If this does does work, would you show me the sql on how to disable and the reenable the foreign key constraint so the insert statement will work?
Presumably the FK is there for a reason. Why are you trying to bypass it?
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
October 12, 2016 at 11:01 am
What you're asking to do really isn't logical. Walk through this.
You're telling SQL, with the FK: "Do not let me add a row to this table unless a matching row exists in another table."
Then you turn around and say: "I want to be able to add a row to this table even if there's no matching row in the other table."
I think those are mutually exclusive: either SQL should enforce the rule or it shouldn't. Perhaps you need to reconsider modify how you enforce the rule(s) without using a FK restriction, such as using trigger(s).
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
October 12, 2016 at 11:13 am
Further, if you modify the data, INSERT/UPDATE/DELETE, in the table with the foreign key constraint in such a way that the foreign key constraint can't be applied, you either have to drop it completely, or, you have to recreate it using NOCHECK, which makes it invalid for use with the query optimizer (Data integrity and enforcement is only part of what enforced referential integrity through foreign keys provide. You also get performance enhancements).
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
October 12, 2016 at 11:15 am
Also, if you just disable and reenable the foreign key constraint, that constraint will no longer be trusted, which, again, defeats the purpose of having the constraint in the first place.
You probably want to log the records with the missing foreign reference and only insert the ones where the foreign reference exists.
If this is an ETL process, you may also be able to insert the foreign references before inserting the records that contain missing foreign keys.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
October 12, 2016 at 12:29 pm
I am trying to load the valid lockID values into the cust table with the following sql:
DECLARE @Startlockid INT=0
DECLARE @MyTableVar table( lockID int);
insert into [dbo].[Lock]
OUTPUT INSERTED.lockID
INTO @MyTableVar
SELECT c.serialNumber,c.type,2
FROM [dbo].[Lock] c
select min(LockID) from @MyTableVar
set @Startlockid= (select min(LockID) from @MyTableVar)
;with cte as
(
Select SchoolID, lockID,
Row_Number() Over(Partition by SchoolID order by LockID) RowNum
From [dbo].Cust
)
Update cte set
lockID = RowNum + @Startlockid;
I create the rows in the lock table first. I save the values in @MyTableVar. I then want to update the values in the cust table.
Thus is there a way to accomplish this goal without having to disable or drop the foreign key constraint? if so, would you show me sql that would work to solve the issue?
October 12, 2016 at 12:56 pm
dianerstein 8713 (10/12/2016)
I am trying to load the valid lockID values into the cust table with the following sql:DECLARE @Startlockid INT=0
DECLARE @MyTableVar table( lockID int);
insert into [dbo].[Lock]
OUTPUT INSERTED.lockID
INTO @MyTableVar
SELECT c.serialNumber,c.type,2
FROM [dbo].[Lock] c
select min(LockID) from @MyTableVar
set @Startlockid= (select min(LockID) from @MyTableVar)
;with cte as
(
Select SchoolID, lockID,
Row_Number() Over(Partition by SchoolID order by LockID) RowNum
From [dbo].Cust
)
Update cte set
lockID = RowNum + @Startlockid;
I create the rows in the lock table first. I save the values in @MyTableVar. I then want to update the values in the cust table.
Thus is there a way to accomplish this goal without having to disable or drop the foreign key constraint? if so, would you show me sql that would work to solve the issue?
There are multiple issues with this. The foremost being that your Lock table is not properly normalized. The serial number is a natural key, but your first insert statement indicates that you don't have a unique constraint on it.
Also, you're approach is backwards. You're trying to insert valid ids from the Lock table into the Cust table, but the initial problem is that you have ids in the Custest table that don't exist in your Lock table. You need to update the Lock table to fix the problem, not the Cust table.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
October 12, 2016 at 12:58 pm
dianerstein 8713 (10/12/2016)
I am trying to load the valid lockID values into the cust table with the following sql:DECLARE @Startlockid INT=0
DECLARE @MyTableVar table( lockID int);
insert into [dbo].[Lock]
OUTPUT INSERTED.lockID
INTO @MyTableVar
SELECT c.serialNumber,c.type,2
FROM [dbo].[Lock] c
select min(LockID) from @MyTableVar
set @Startlockid= (select min(LockID) from @MyTableVar)
;with cte as
(
Select SchoolID, lockID,
Row_Number() Over(Partition by SchoolID order by LockID) RowNum
From [dbo].Cust
)
Update cte set
lockID = RowNum + @Startlockid;
I create the rows in the lock table first. I save the values in @MyTableVar. I then want to update the values in the cust table.
Thus is there a way to accomplish this goal without having to disable or drop the foreign key constraint? if so, would you show me sql that would work to solve the issue?
Please provide sample DDL, sample data, with INSERT statements corresponding with the sample data and desired results, so that we can properly understand what you are trying to do and provide sample code as a solution. This is all described well if you follow the first link in my signature. Without this, we are largely shooting in the dark. No one here is going to recommend that you drop or disable a FK without first understanding why.
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
October 12, 2016 at 1:06 pm
In all fairness, it would be useful on occasion to have a feature, let's call it "Delayed Consistency", allowing one to perform multiple DML operations within an batch transaction, perhaps initially isolating all modifications as a snapshot, and then all changes are finally persisted and checked for consistency only upon COMMIT TRAN. It certainly wouldn't be the default behaviour, but it might be useful things like deployments or complex ETL processes.
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
October 12, 2016 at 1:12 pm
Is there a way to do the update I want to accomplish without dropping or disable the constraint? If so, how would you accomplish this goal?
October 12, 2016 at 1:16 pm
dianerstein 8713 (10/12/2016)
Is there a way to do the update I want to accomplish without dropping or disable the constraint? If so, how would you accomplish this goal?
Please tell us why you are refusing to answer the questions or provide the information we have asked for.
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
October 12, 2016 at 2:30 pm
dianerstein 8713 (10/12/2016)
Is there a way to do the update I want to accomplish without dropping or disable the constraint? If so, how would you accomplish this goal?
Yes. Collect the correct lockid for the customer and use it in your insert.
For better assistance in answering your questions, please read this[/url].
Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]
October 12, 2016 at 2:45 pm
You may be able to leverage an 'instead of' trigger on the parent table. This canintercept the update of the key before it is changed. You can than grab that ID and process the child table while changing the parent key value. You will need a place holder value in the parent table that you can point the foreign keys while you switch the key value.
1. Update key value, interupted by instead of trigger
2. Update foreign key rows to a generic substitute key (substitute key must exist or FK constraint is violated)
3. Update key value
4. Update foreign key rows for generic substitute to use new parent key value
HOWEVER, this is a terrible idea and I recommend you do not do this. If your existing primary key has business meaning but is changeable; you should seriously consider using something else. A simple integer identity key is sufficient and there is no temptation to change it because it is meaningless to the data.
Wes
(A solid design is always preferable to a creative workaround)
October 13, 2016 at 6:20 pm
Please provide sample DDL, sample data, with INSERT statements corresponding with the sample data and desired results, so that we can properly understand what you are trying to do and provide sample code as a solution. This is all described well if you follow the first link in my signature. Without this, we are largely shooting in the dark. No one here is going to recommend that you drop or disable a FK without first understanding why.
Phil's suggestion will go a long way to not make guesses as to what may or may not work. I don't even want to offer any other advise at this point since we are dealing with bypassing a item that ensures data integrity. The order in which you insert data matters. There is a function, scope_identity(), which may or may not be helpful to you. Without knowing more as to your situation that is all I can state.
----------------------------------------------------
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply