Check constraint or Trigger for make sure the key is referenced (FK)

Question

Hi,
I've implemented a trigger and a function in a database "X" on a table "son", that need check the consistency of the virtual FK present in a "parent" table in a database "A"... external to the former, naturally on the same sql server instance.

Which of the two options might be less invasive in a multi-line insert context?
I tried with a single insert and multiple few rows... but imaging a situation of 10k di records?
In a context of reading and writing, even if it is only one process that writes so they told me.

The trigger version with multi row insert don't intercept the FK miss, but the function yes.

THANKS, Alen

Some code

CREATE OR ALTER TRIGGER dbo.trChk_PK_parent 
 ON [dbo].[son] 
 AFTER INSERT, UPDATE
AS
BEGIN
 IF (ROWCOUNT_BIG() = 0)
 RETURN;

 IF NOT EXISTS ( SELECT 1 
 FROM [A].[dbo].[parent] 
 WHERE [ID] IN (SELECT i.[ID] FROM inserted i)
 )
 THROW 60000, 'PK not exists', 1;
END

and the function

CREATE FUNCTION dbo.fnCheckFK_Parent (
 @key INT
) 
RETURNS BIT
AS
BEGIN
    DECLARE @exists bit = 0
 ;
    IF EXISTS (
 SELECT TOP(1) 1 
 FROM [A].[dbo].[parent] 
 WHERE ID =  @key
    ) BEGIN 
         SET @exists = 1 
      END;
      RETURN @exists
END
GO

ALTER TABLE [X].[dbo].[son]
ADD CONSTRAINT CHK_FK_parent
CHECK(dbo.fnCheckFK_Parent(IDparent) = 1)

2 tables...

CREATE TABLE [dbo].[son](
 [ID_son] [int] NOT NULL,
 [IDparent] [int] NOT NULL,
 [Voce] [varchar](50) NULL,
 CONSTRAINT [PK_ID_son] PRIMARY KEY CLUSTERED 
(
 [ID_son] ASC
)
)

ALTER TABLE [dbo].[son]  WITH CHECK 
ADD  CONSTRAINT [CHK_FK_parent] CHECK  (([dbo].[fnCheckFK_parent]([IDparent])=(1)))
GO

ALTER TABLE [dbo].[son] CHECK CONSTRAINT [CHK_FK_paren]
GO

and the parent

CREATE TABLE [dbo].[parent](
 [IDparent] [int] NOT NULL,
 [Voce] [varchar](50) NULL,
 CONSTRAINT [PK_ID_parent] PRIMARY KEY CLUSTERED 
(
 [IDparent] ASC
)
)

the statements (before insert some row in parent)

INSERT INTO [X].[dbo].[son]
SELECT 1, 1, 'Not esists'

INSERT INTO [X].[dbo].[son]
SELECT 1, 2, 'Esists'

INSERT INTO [X].[dbo].[son]
SELECT 1, 2, 'Esists'
UNION ALL
SELECT 1, 1, 'Not Esists'
UNION ALL
SELECT 1, 3, 'Esists'

Answer 1

Your trigger code is incorrect. Try this:

 CREATE OR ALTER TRIGGER dbo.trChk_PK_parent 
  ON [dbo].[son] 
  AFTER INSERT, UPDATE
 AS
 BEGIN

IF EXISTS ( SELECT 1 
  FROM inserted i
    LEFT OUTER JOIN [A].[dbo].[parent]  p
    ON p.[ID] = i.[ID] 
    WHERE p.[ID] IS NULL -- Does not exist
    )
  )
  THROW 60000, 'PK not exists', 1;
END

Answer 2

> I've implemented a trigger and a function in a database "X" on a table "son", that need check the consistency of the virtual FK present in a "parent" table in a database "A"... external to the former, naturally on the same SQL server instance. <<

The correct terms in RDBMS are “referenced” and “referencing” tables, not “son” and “parent”; those terms belong to the old network databases. A table can reference itself in RDBMS, and one table can reference reference and vice versa.

Since SQL is a declarative language, we hate triggers. We prefer declarative constructs instead of procedural code. In fact, the only reason the triggers exist in SQL is that the original systems back when I worked on the standards in the 1980s were built on top of existing file and network database systems, so we had no other way to do it. Let’s take a look at what you’ve got.

As an aside, putting prefixes that give metadata in a data element names is considered bad form. We prefer that you tell us what a thing is by its nature, not how it’s used in a particular implementation. The names of table should also be plural or collective nouns because they model set.

Since identifiers are measured on a nominal scale, they cannot be numerics. They do not measure a volume quantity or magnitude of any kind. This is usually covered in your first class on data modeling

SQL As had declarative referential integrity (DRI) For quite a few years now. We put them into the language as part of getting rid of triggers. We found that update and delete actions were the most common (if I remember that meeting, about 80+ percent of the time).

CREATE TABLE Children
(child_id CHAR(5) NOT NULL PRIMARY KEY,
parent_id CHAR(5) NOT NULL REFERENCES Parents
ON DELETE CASCADE
ON UPDATE CASCADE);

The REFERENCES clause will do your job for you.

CREATE TABLE Parents
(parent_id CHAR(5) NOT NULL PRIMARY KEY,
voce VARCHAR(50)); --useless data element

I cannot figure out exactly what “voce” means in your data model. It appears in two different places and it’s rather oversized. Surely you did careful research before you allocated 50 characters to it. If you don’t protect against oversize columns, you’ll get them. Do not invite garbage to your schema

You might also want to see how the insertion statement has changed the language in the last 20 years. We now use table constructors, so the optimizer can put things in place. Why are you using the original Sybase row constructor, with the old SELECT..UNION syntax? However, one huge problem is that what you posted for your insertion statement has a duplicate row! This means it’s not a table. This is by definition!! Let’s remove the redundant duplicates that you put in:

INSERT INTO Children
VALUES
(‘01’, ‘01’, 'Not Exists'),
(‘01’, ‘02’, 'Exists'),
(‘01’, ‘03’, ‘Exists');

In short, you’ve done the wrong thing and done it badly.

Answer 3

Performancewise, my bet goes on the trigger. The UDF is one call with data access for every row when you insert 10000. And while there is inlining of UDFs on SQL 2019, it does not apply to UDFs in DDL, like CHECK constraints.

But if you really want to know - benchmark!

Answer 4

Glad your problem is solved.
If you have any question, please feel free to let me know.

Regards
Echo