You can use two different foreign keys in messages referring to chats and tickets, respectively. The intended usage is that one of these foreign keys has a value and the other has NULL. The cascade delete will work from either table.
The main drawback is we cannot specify non-null for the foreign keys, so it will be possible to insert into messages with both foreign keys NULL, which is essentially an uncaught error in usage. (or both non NULL, which is also an error in usage.)
(For this and numerous other scenarios, it would be great if SQL offered column grouping such that exactly one member of a column group had to be not NULL, but alas this is an omission in relational algebra.)
CREATE TABLE products
( product_id INT PRIMARY KEY,
product_name VARCHAR(50) NOT NULL,
category VARCHAR(25)
);
CREATE TABLE products2
( product_id2 INT PRIMARY KEY,
product_name VARCHAR(50) NOT NULL,
category VARCHAR(25)
);
CREATE TABLE inventory
( inventory_id INT PRIMARY KEY,
product_id INT,
product_id2 INT,
CONSTRAINT fk_inv_product_id
FOREIGN KEY (product_id)
REFERENCES products (product_id)
ON DELETE CASCADE,
CONSTRAINT fk_inv_product_id2
FOREIGN KEY (product_id2)
REFERENCES products2 (product_id2)
ON DELETE CASCADE
);
insert into products (product_id,product_name,category) values (1,'item1', 'general'),(2,'item2', 'general');
insert into products2 (product_id2,product_name,category) values (4,'item4', 'general'),(5,'item5', 'general');
insert into inventory (inventory_id,product_id,product_id2) values (10, 1, NULL), (20,2,NULL), (40,NULL,4), (50,NULL,5);
/* these two inserts are usage error, not caught */
insert into inventory (inventory_id,product_id,product_id2) values (1000,1,4), (1001,NULL,NULL);
select * from products;
select * from products2;
select * from inventory;
delete from products where product_id = 1;
select * from products;
select * from products2;
select * from inventory;
delete from products2 where product_id2 = 4;
select * from products;
select * from products2;
select * from inventory;
https://rextester.com/l/sql_server_online_compiler
updated_at
column probably the other also can have that.comment message
andticket message
, cause the nature is same and they are same model they should be in 1 table, even if we call itticket comment
it won't change anything cause we can also use comment for chat, likechat comment
, cause they have same nature.