Database Constraint To Check Unique

All posts relating to Oracle PL/SQL development.

Moderator: Tim...

Database Constraint To Check Unique

Postby ponic » Wed Apr 04, 2012 6:13 am

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
ponic
Senior Member
 
Posts: 160
Joined: Wed Mar 03, 2010 7:18 am

Re: Database Constraint To Check Unique

Postby Tim... » Wed Apr 04, 2012 10:03 am

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...
Tim...
Oracle ACE Director
Oracle ACE of the Year 2006 - Oracle Magazine Editors Choice Awards
OakTable Member
OCP DBA 7.3, 8, 8i, 9i, 10g, 11g
OCP Advanced PL/SQL Developer
Oracle Database: SQL Certified Expert
My website: http://www.oracle-base.com
My blog: http://www.oracle-base.com/blog
Tim...
Site Admin
 
Posts: 17935
Joined: Mon Nov 01, 2004 5:56 pm
Location: England, UK

Re: Database Constraint To Check Unique

Postby ponic » Wed Apr 04, 2012 10:34 am

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

Regards
ponic
Senior Member
 
Posts: 160
Joined: Wed Mar 03, 2010 7:18 am

Re: Database Constraint To Check Unique

Postby Tim... » Wed Apr 04, 2012 10:54 am

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...
Tim...
Oracle ACE Director
Oracle ACE of the Year 2006 - Oracle Magazine Editors Choice Awards
OakTable Member
OCP DBA 7.3, 8, 8i, 9i, 10g, 11g
OCP Advanced PL/SQL Developer
Oracle Database: SQL Certified Expert
My website: http://www.oracle-base.com
My blog: http://www.oracle-base.com/blog
Tim...
Site Admin
 
Posts: 17935
Joined: Mon Nov 01, 2004 5:56 pm
Location: England, UK

Re: Database Constraint To Check Unique

Postby ponic » Wed Apr 04, 2012 11:23 am

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

Thanks again.
ponic
Senior Member
 
Posts: 160
Joined: Wed Mar 03, 2010 7:18 am

Re: Database Constraint To Check Unique

Postby Tim... » Wed Apr 04, 2012 12:24 pm

:)
Tim...
Oracle ACE Director
Oracle ACE of the Year 2006 - Oracle Magazine Editors Choice Awards
OakTable Member
OCP DBA 7.3, 8, 8i, 9i, 10g, 11g
OCP Advanced PL/SQL Developer
Oracle Database: SQL Certified Expert
My website: http://www.oracle-base.com
My blog: http://www.oracle-base.com/blog
Tim...
Site Admin
 
Posts: 17935
Joined: Mon Nov 01, 2004 5:56 pm
Location: England, UK


Return to Oracle SQL and PL/SQL Development

Who is online

Users browsing this forum: No registered users and 5 guests

cron