What will be the T-sql Query of the scenario?

I have a scenario where there is an apartment and each apartment has flats known as "Property" in my table. Each flat can have multiple appliances like AC, TV, fridge etc. We are maintaining a master table of appliances.

Say user has purchased some items for that house like TV, AC and CFL Bulbs. So he would , in the very first time, carry a insert operation in which the details like ApplianceId, DateOfPurchase, Serial# of Product, PropertyId (Id of the flat) are inserted. Till this we are fine开发者_StackOverflow社区 no issues.

Now our QA tossed a situation where user has entered some wrong serial number to TV. Now he wants to update the record. But in between comes the following conditions:

1. The appliance's serial number must be unique but the two different appliances might have similar serial numbers, that If in my flat there are 4 appliances out of which I have two ceiling fans, a fridge and an AC then the serial number of 1 ceiling fan and AC might be same but the two ceiling fan serial numbers can neither be same.

2. The check should be carried out for all properties. That is in entire table this should be applicable. It should not be propertywise. That is If i have an AC with Serial#1234 then , it is not possible that SO guru Jon Skeet can also have an AC with Serial#1234. He can have Refridgrator with the same Serial#. And note we are using serial number in plain numeric form like 123456789901. No characters are allowed.

Please help me out, and inform if any thing is not clear.

---

unique constraint on the property table on (Product, SerialNo)? Is Assume product holds the productType

That should do the trick.

GJ

---

You just need a unique constraint or index on appliance type, serial number by the sounds of things.

create unique index uix on appliances(type, serial_number)

---

I'm going to assume that ApplianceId refers to the type of appliance, i.e. each kind of appliance has a unique identifier. In this case, the combination of ApplianceId and Serial# form the primary key for your appliance table. Setting that will prevent inserts or updates that cause a conflict.

---

Different manufacturers of the same product could be an issue. You'd need a make in the constraint as well, in addition to the type and serial number.