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

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.

StackExchange Database Administrators Q#266319, answer score: 5