Can a FOREIGN KEY be filtered with WHERE clause

Paulo Leitao
Paulo Leitao used Ask the Experts™
on
Is it possible to create foreign key with where clause on the reference?
Something like this:

ALTER TABLE webapp.SimCards
  ADD CONSTRAINT FK_SimTypes foreign key (type) references webapp.settings_types (id) 
    where TypeGroup = "SIM Type" ;

Open in new window

The issue is that we have many groups on the Settings_Types table and when using it has reference on the foreign key, it can match against other groups like products, devices, etc..
In order to guaranty consistence, I want to make sure that column "type" on table SimCards only validate if value exist on column id from settings_types but only on TypesGroups = "SIM Type"

If this is not possible, is there a better way than create a check constrain using a function to check result ?
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Software Development
Commented:
use a trigger and if the validation not succeeds rollback the transaction like the following example.

-- This trigger prevents a row from being inserted in the Purchasing.PurchaseOrderHeader 
-- table when the credit rating of the specified vendor is set to 5 (below average).  

CREATE TRIGGER Purchasing.LowCredit ON Purchasing.PurchaseOrderHeader  
AFTER INSERT  
AS  
IF (@@ROWCOUNT_BIG  = 0)
RETURN;
IF EXISTS (SELECT *  
           FROM Purchasing.PurchaseOrderHeader AS p   
           JOIN inserted AS i   
           ON p.PurchaseOrderID = i.PurchaseOrderID   
           JOIN Purchasing.Vendor AS v   
           ON v.BusinessEntityID = p.VendorID  
           WHERE v.CreditRating = 5  
          )  
BEGIN  
RAISERROR ('A vendor''s credit rating is too low to accept new  
purchase orders.', 16, 1);  
ROLLBACK TRANSACTION;  
RETURN   
END;  
GO  

-- This statement attempts to insert a row into the PurchaseOrderHeader table  
-- for a vendor that has a below average credit rating.  
-- The AFTER INSERT trigger is fired and the INSERT transaction is rolled back.  

INSERT INTO Purchasing.PurchaseOrderHeader (RevisionNumber, Status, EmployeeID,  
VendorID, ShipMethodID, OrderDate, ShipDate, SubTotal, TaxAmt, Freight)  
VALUES (  
2  
,3  
,261  
,1652  
,4  
,GETDATE()  
,GETDATE()  
,44594.55  
,3567.564  
,1114.8638 );  
GO  

Open in new window


https://docs.microsoft.com/en-us/sql/t-sql/statements/create-trigger-transact-sql?view=sql-server-2017
You may use CHECK constraint instead of the trigger (let say the type and id columns are of integer type):
CREATE FUNCTION webapp.checkTypeSIMType(@type int)
AS
BEGIN
DECLARE @result bit = 0
IF EXISTS (SELECT * FROM webapp.settings_types WHERE id = @type AND TypeGroup = 'SIM Type')
  @result = 1
RETURN @result
END
GO

ALTER TABLE webapp.SimCards
  ADD CONSTRAINT CHK_SimTypes CHECK (webapp.checkTypeSIMType(type)=1) ;

Open in new window

You may also define the function with two parameters and send the TypeGroup as the second parameter.
Paulo LeitaoGlobal Hardware Manager

Author

Commented:
Thank you both for the quick reply, as I said on the question, I was searching for a solution that doesn't use the function on CHECK CONSTRAIN.

The reason for trying to avoid the function is that I need to insert information in batches "insert into table (column1, column2) Select ...", when using the function it costs more time against the trigger, because the trigger will deal all inserted rows as a single batch while the CONSTRAIN will run row by row which means select per each row.

I made some tests on this theory and if I make a loop of 1000 inserts, the CHECK CONSTRAIN comes up faster, but if I make an insert into from a select with 1000 rows, the Trigger is quite faster.

Therefore, I will accept the solution from Sebastian Strittmatter because I was looking to be away of the function as I wrote on the initial topic.
LOL, I did not read the Q carefully... :-)
Paulo LeitaoGlobal Hardware Manager

Author

Commented:
@pcelba, thank you for posting, in fact, because you insisted on that solution, I went back and test it vs the trigger.
I learn that if I was inserting/updating a line at the time your solution cost less, I always thought the function will have a higher payload than the trigger but because the constraint append before the trigger is faster, however I will send many rows in a statement to this table and that makes all the difference, nevertheless for other tables your solution will be faster :)

Thank you

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial