Pre Exam Test Practice (SSC | Banking | IAS | GATE | State Level | CLAT | Railways | Insurance | Teaching Exams | NDA | Defence | IES |State Exam )

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,Address,New York

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.

asked Oct 3, 2015 by balvant maurya
0 votes

Related Hot Questions

Government Jobs Opening