Can a FOREIGN KEY be filtered with WHERE clause

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 ?
Paulo LeitaoGlobal Hardware ManagerAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Sebastian StrittmatterSoftware DevelopmentCommented:
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
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
pcelbaCommented:
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.
0
Paulo LeitaoGlobal Hardware ManagerAuthor 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.
0
pcelbaCommented:
LOL, I did not read the Q carefully... :-)
0
Paulo LeitaoGlobal Hardware ManagerAuthor 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
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.