Database Constraint To Check Unique

[ponic]

Hi Tim

I have two tables called Product and Category

Code: Select all

Product
product_id  pk
Product_No
Product_Desc


Code: Select all

Category
category_id pk
product_id fk to product_id of product table
category_code
rate


I am writing data to both these tables from front end.
I would like to have a condition that if Product_No from Product table and category_code from Category table should be unique,i.e.
if user trying to enter same Product_No and category_code, then there should be some kind of validation to prevent this.

What is the best mechanism to achieve this in database.
My version 10g on Solaris

Regards

[Tim...]

Hi.

You have a couple of choices:

1) Do the check only in the application before you even attempt to send anything to the database.

2) Do the check in the application and the database.

The database side of option (2) can be subdivided into several choices.

- Use an API and check the data before attempting to insert into the tables.

- Use a trigger on the category table to check the issue and fail if the rule is broken.

Personally, I would go for option 2. There is no point in sending crap data to the database if you can filter it in the application. Having said that, the database should be protected fro logical corruption by having the check there also. It is up to you if you prefer the API or trigger method.

Cheers

Tim...

[ponic]

Thanks Tim for the explanation, I will try to filter it in application itself rather than sending to database.

Regards

[Tim...]

Hi.

You need to consider the database also. If this condition is not allowed, someone doing something stupid in SQL*Plus can still cause a logical corruption. It's sensible to prevent that at the database and application level.

Cheers

Tim...

[ponic]

Yes you are right, I will apply conditions in application and database.

Thanks again.

[Tim...]