3

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

  • 1
    So, the constraint should be something like COUNT(non_unique_id) = 0 OR COUNT(non_unique_id || timestamp) > 1 ? AFAIK, very few DBMS implements declarative constraints (beside candidate-, foreign-key) at table level. It should not be to hard implementing it with a procedural construct like a trigger though. – Lennart May 2 at 8:34
5

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.

| improve this answer | |
  • That's elegant. If you insert directly into the table, does the view enforce the constraint anyhow? – Lennart May 2 at 9:17
  • 1
    Yes - inserting into the table will automatically maintain the view in the same query plan and validate the constraints on it – Martin Smith May 2 at 9:19
  • That's cool. Thanks – Lennart May 2 at 9:20

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.