Insert into table with foreign key constraint

Transact-SQL
1

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?

2

I reckon:

Either you want the foreign key constraint - in which case you need to ensure that [lockid] exists, or any old value is acceptable in the [lockid] column, in which case drop the FKey constraint (i.e. permanently)

No point having a Foreign Key constraint and only "some" of the data conforms.

3

So you are saying I would need to drop or disable the constraint and then reinable the constraint, correct? If so, would you show me how to accomplish this goal with sql?

4

No, I am saying you need to make sure that the [lockid] value DOES exist, before you attempt to insert your new row into [Cust] table.

OR

You decide that you do not need the Foreign Key Constraint at all, and drop it - permanently, not just "for a short time" whilst you insert an invalid value for [lockid]

5

I know that the lockid exists. Thus is there a way to insert rows in a table without dropping or disable the constraint? if so, how would I setup that sql?

6

OK, something I'm not understanding here.

If the [lockid] exists then the INSERT will not raise an error (because the constraint check fails)

So I must be misunderstanding what the problem is

You are suggesting you want to disable the constraint. I need to understand WHY you think you should be doing that,

(i.e. what is the problem? rather than "what is your assumption of the ANSWER to the problem"!!)

7

hmm. when doing the above insert could it be since you are not specifying the columns for Cust, it is throwing a red herring error.

it could be that your insert is doing the following

insert into [dbo].Cust(lockid, number, custid)
select custid,[number],lockid
from [dbo].[Custest]

8

I want to disable or drop the constraint since I cannot update the table. I get the foreign key constraint. When I look at the lockid in the lock table, I see that the lockid exists. I place the 'begin trans and commit trans around the update of the lock table. From everything that I can see, the lockid exists. Thus I can not figure out why me adding lockid to the cust table does not work.

9

I really think that is the wrong way to go. The Foreign Key is there to prevent you, accidentally, adding a record with an invalid [lockid]. You need that safeguard, hence disabling the FKey is not a good idea.

What does this give you?

-- insert into [dbo].Cust
select custid,[number],lockid
from [dbo].[Custest] AS CT
WHERE NOT EXISTS
(
    SELECT *
    FROM dbo.YourLockTable AS L
    WHERE L.lockid = CT.lockid
)

That will show you and row in [Custest] which does not have a matching row in [YourLockTable]

1 Like