I have been a mysql/postgres user and enforcing uniqueness for primary key by the DB seems natural to me until I discovered that snowflake does not enforce unique on primary key. This also makes sense since if the table is not that big, application which does the insertion can always check for uniqueness on the primary key itself and normally application does not use the primary key for an insertion.

If this understanding is correct, when would it make sense to let the DB to enforce uniqueness for the primary key? My guess is when the time for an application to check uniqueness before insertion the time to actually insert is slower than using the DB to enforce uniqueness and just insert directly.

CodePudding user response：

The database "checking for you" and "you do checking" should cost the same. You could do it worse though.

But the thing with performance, is never do anything you don't need to. So if you have de-duplicated some data at step A and then Insert into table B, how does the DB know the constraints do not need to be checked.. it doesn't so it has to check again. and again, and again.

In Oracle to get things performant, you spend a amount of time turning the constraints off. And then on again. Now that seems somewhat risky.

So in Snowflake there is No Free Lunch, but there is also No Hidden Tax, and I know people like that fully of easy Carbs feelings of the Free Lunch. But I don't want it.