Thanks for the question, Hassan.

Asked: September 19, 2004 - 2:55 pm UTC

Last updated: September 20, 2004 - 7:36 pm UTC

Version: 8.1.7

Viewed 10K+ times! This question is

You Asked

Hi Tom,

What would be the best way to avoid duplicate records being inserted in a table which has a foreign key constraint? I have a situation in which I'm inserting rows from one table to another and the second table has a foreign key constraint. The table is being populated via a simple procedure in SQL*Plus environment. My question is that if I run the procedure once then say 100 records for a particular item are inserted into the target table but if one accidently executes the procedure for the same item, then 100 records will be again inserted, since we have a foreign key constraint. How can I avoid this or should I simply delete the duplicate records everytime?

Thanks in advance

Hassan

and Tom said...

to avoid duplicates, you create a UNIQUE constraint or a primary key.

Not sure what the foreign key has to do with anything there? the fkey would ensure a row exists in some other table. The fkey has nothing to do with duplication?

Add a unique or primary key constraint -- duplicates will not be permitted.

Rating

(3 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Avoiding Duplicate Records in a Table

Hassan Ayub, September 20, 2004 - 2:19 pm UTC

Thanks Tom,

In my particular situation, I need to have the foreign key constraint, 'cause the records are linked with another table having the primary key column obviously.If we have a unique key constraint then will that be one the foreign key column or what? Could you be a little more specific here.

Thanks

September 20, 2004 - 2:25 pm UTC

fkey constraint is not relevant in a discussion of "i need to have unique records".

you can have your fkey

what you need IN ADDITION to that is a unique constraint on the columns in this table that define "what is unique"

forget the fkey, you can have it, but it is just not relevant in a discussion of "preventing duplicate records".

Just add a unique constraint on the columns that are to be unique.

A reader, September 20, 2004 - 2:48 pm UTC

Create an unique constraint on the foreign key?

September 20, 2004 - 4:16 pm UTC

No, create a unique constraint on the columns that define "uniqueness"

I'm confused -- this seems like such an easy thing?

Q: What would be the best way to avoid duplicate records being inserted in a
table

A: add a unique constraint

Jason Longenecker, September 20, 2004 - 7:17 pm UTC

My advice to Hassan, when trying to create a table that has a FK column that must be unique do the following. Hassan I think I know what your trying to do because I've tried it before myself. Your efforts are geared towards creating two tables that act like one to a certain degree. Infact I suspect if you created one table out of the two you had I bet you could create two views which act in the manor of which you need them to. I could be wrong but try the following.

CREATE TABLE TBL_EXAMPLEA(
SITEID NUMBER(6) CONSTRAINT EXAMPLEA_SITEID_FK REFERENCES TBL_SITE_GENERAL_INFO (SITEID),
)
/

ALTER TABLE TBL_EXAMPLEA
ADD CONSTRAINT EXAMPLEA_PK PRIMARY KEY (SITEID)
/

September 20, 2004 - 7:36 pm UTC

so, of what relevance is the word/phrase "foreign key" here.

you have a table.
you want to prevent duplicates by some key(s).

that is quite simply saying "i want a unique or primary key" constraint.

I'm having a really hard time understanding why this is generating so much discussion?

Breadcrumb

Question and Answer

You Asked

and Tom said...

Rating

Comments

Avoiding Duplicate Records in a Table

More to Explore

PL/SQL demos

PL/SQL docs