SQL Server 2019

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
create table products (
    productid int identity primary key,
    name varchar(255)
)
create table orders (
    orderid int identity primary key,
    x int
)
create table reminders (
    ReminderId int identity primary key,
    parentId int, -- presumably
    type varchar(255),
    check (type in ('product', 'order')),
    parent_productid as (case when type = 'product' then parentId end) persisted,
    parent_orderid as (case when type = 'order' then parentId end) persisted,
    foreign key (parent_productid) references products(productId),
    foreign key (parent_orderid) references orders(orderid)
);
insert into products (name) values ('test')
1 rows affected

insert into orders (x) values (-1)
1 rows affected

insert into orders (x) values (-2)
1 rows affected

-- should work
insert into reminders (parentId, type) values (1, 'product')
1 rows affected

-- should work
insert into reminders (parentId, type) values (2, 'order')
1 rows affected

-- should fail
insert into reminders (parentId, type) values (3, 'product')
Msg 547 Level 16 State 0 Line 2
The INSERT statement conflicted with the FOREIGN KEY constraint "FK__reminders__paren__29572725". The conflict occurred in database "fiddle_7add1d814d904328b7eded90122f0b6b", table "dbo.products", column 'productid'.
Msg 3621 Level 0 State 0 Line 2
The statement has been terminated.