Organizational Research By

Surprising Reserch Topic

unique constraint vs checking before insert


unique constraint vs checking before insert  using -'mysql,sql,sql-server,oracle'

I have a SQL server table RealEstate with columns - Id, Property, Property_Value. This table has about 5-10 million rows and can increase even more in the future. I want to insert a row only if a combination of Id, Property, Property_Value does not exist in this table.

Example Table -

1,Rooms,5
1,Bath,2
1,Address,New York
2,Rooms,2
2,Bath,1
2,Address,Miami


Inserting 2,Address,Miami should NOT be allowed. But, 2,Price,2billion is okay. I am curious to know which is the "best" way to do this and why. The why part is most important to me. The two ways of checking are -


At application level - The app should check if a row exists before it inserts a row.
At database level - Set unique constraints on all 3 columns and let the database
do the checking instead of person/app.


Is there any scenario where one would be better than the other ?

Thanks.

PS: I know there is a similar question already, but it does not answer my problem -
Unique constraint vs pre checking
Also, I think that UNIQUE is applicable to all databases, so I don't think I should remove the mysql and oracle tags.
    
asked Oct 3, 2015 by balvant maurya
0 votes
33 views



Related Hot Questions



Government Jobs Opening


...