Ask a Question
Advertise on boostr.in
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 -
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 ?
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.
Â Â Â Â
Oct 3, 2015
to add a comment.
Related Hot Questions
Government Jobs Opening