title: UNIQUE constraint vs checking before INSERT
body: <p>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. </p>
  
  <p>Example Table - </p>
  
  <pre><code>1,Rooms,5
  1,Bath,2
  1,Address,New York
  2,Rooms,2
  2,Bath,1
  2,Address,Miami
  </code></pre>
  
  <p>Inserting <code>2,Address,Miami</code> should NOT be allowed. But, <code>2,Price,2billion</code> is okay. I am curious to know which is the "best" way to do this and <strong>why</strong>. The why part is most important to me. The two ways of checking are - </p>
  
  <ol>
  <li>At application level - The app should check if a row exists before it inserts a row.</li>
  <li>At database level - Set unique constraints on all 3 columns and let the database 
  do the checking instead of person/app.</li>
  </ol>
  
  <p>Is there any scenario where one would be better than the other ?</p>
  
  <p>Thanks.</p>
  
  <p><strong>PS:</strong> I know there is a similar question already, but it does not answer my problem - 
  <a href="https://stackoverflow.com/questions/4714024/unique-constraint-vs-pre-checking">Unique constraint vs pre checking</a> 
  Also, I think that UNIQUE is applicable to all databases, so I don't think I should remove the mysql and oracle tags.</p>