Q:

Complex key constraint: allow only if uuid is new or another column matches

For my use case I am trying to get something like this to work with Azure Data Factory (ADF)
but I'm also interested in this question from a theoretical perspective. Is it possible to do something like this in a normal SQL engine like postgres?
I want to disallow inserts based on this key: (non_unique_id, timestamp) where timestamp is a unique value for that pair.
for example:
data to insert:
non_unique_id: 0cf6c19c14
timestamp: 1970-01-01 00:00:01

case 1 ALLOWED
before insert:
select non_unique_id, timestamp from tbl where non_unique_id = 0cf6c19c14;

0 results

case 2 ALLOWED
before insert:
select non_unique_id, timestamp from tbl where non_unique_id = 0cf6c19c14;

0cf6c19c14, 1970-01-01 00:00:01
0cf6c19c14, 1970-01-01 00:00:01
0cf6c19c14, 1970-01-01 00:00:01
...

case 3 NOT ALLOWED
before insert:
select non_unique_id, timestamp from tbl where non_unique_id = 0cf6c19c14;

0cf6c19c14, 2038-01-19 03:14:05
0cf6c19c14, 2038-01-19 03:14:05
0cf6c19c14, 2038-01-19 03:14:05
...

Of course there are other columns but this illustrates the simplest cases

A:

This is a normalisation issue as timestamp is functionally dependent on non_unique_id.
So there should be another table with primary key non_unique_id and the corresponding timestamp.
In SQL Server/Azure SQL Database you can enforce this with an indexed view though.
Create a view with schema binding and definition
SELECT non_unique_id,
       timestamp,
       COUNT_BIG(non_unique_id) AS Count
FROM   dbo.YourTable
GROUP  BY non_unique_id,
          timestamp 

and then put a unique clustered index on column non_unique_id in the view.

