Home SQL–How can I add a constraint based on multiple joined tables in an INSERT statement?

Questions

SQL–How can I add a constraint based on multiple joined tables in an INSERT statement?

January 21, 2023

I am attempting to limit the creation of a new record in a cross table to Active records in the respective primary tables. In this example, the Owners table and the Pets table each contain an ‘Active’ column with a boolean data type. The cross table contains columns for OwnerId and PetId. I do not want the INSERT statement to fire unless both the Owner and the Pet are Active.

The unconstrained statement follows:

INSERT INTO OwnerPets (Id, OwnerId, PetId) 
VALUES (NEWID()
, 1234
, 2345)

I have seen numerous examples online showing how to insert data from a joined table using a SELECT FROM WHERE constraint, but that is not what I am trying to do here.

I am able to accomplish this constraint in my UPDATE statement, and I include it here in case it provides any additional insight. The updated values are pulled from the UI.

UPDATE OwnerPets
SET
 OwnerID=1234
, PetId=2345
, DateAcquired='2021-01-23'
, CurrentOnVaccinations=1
, LastModifyDate=CURRENT_TIMESTAMP
, Comment='My Comments'
FROM OwnerPets 
INNER JOIN Owners ON OwnerPets.OwnerId = Owners.Id
INNER JOIN Pets ON OwnerPets.PetId = Pets.Id 
WHERE OwnerPets.Id=3456 
AND Owners.Active = 1
AND Pets.Active = 1

I am attempting to do something similar with INSERT but nothing I have tried seems to work. Any help would be appreciated!

>Solution :

You can use select with insert. If query from the two tables in this select — no rows will be returned if where is not valid. Like this:

INSERT INTO OwnerPets (Id, OwnerId, PetId) 
  SELECT NEWID(), O.Id, P.Id
  FROM Owners O, Pets P
  WHERE O.Id=1234 AND O.Active = 1
  AND P.Id = 2345 AND P.Active = 1