Home » SQL & PL/SQL » SQL & PL/SQL » Prevent duplicate data entry
Prevent duplicate data entry [message #275551] Sun, 21 October 2007 16:09 Go to next message
florida
Messages: 82
Registered: April 2006
Member
I would like to make sure there are no duplicate record entries in my Oracle 9i table (called MainTable) which has an Id field that is the primary key, City with a varchar data type, Fid and Fid2 are number data types.
Id   City      Fid   Fid2
1    Oakland   34    2
2    Boston    23    34
3    Chicago   25    43


Sometimes someone can enter a duplicate City, Fid and Fid2 and it will end up like this:
Id   City      Fid   Fid2
1    Oakland   34    2
2    Boston    23    34
3    Chicago   25    43
4    Chicago   25    43



What constraints or restrictions can I place on the MainTable where it will never allow a duplicate entry into the table?
I would like to do this somehow in the database. If someone tries to enter a duplicate I should get a error message or something to indicate an attempt to enter duplicate data.

Please advise if this is possible?

[Updated on: Sun, 21 October 2007 16:21]

Report message to a moderator

Re: Prevent duplicate data entry [message #275552 is a reply to message #275551] Sun, 21 October 2007 17:08 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
>If someone tries to enter a duplicate
What is classified as a duplicate?
Do you realize that multiple cities can have the same name; but they are in different counties or states?
Re: Prevent duplicate data entry [message #275553 is a reply to message #275552] Sun, 21 October 2007 17:12 Go to previous messageGo to next message
florida
Messages: 82
Registered: April 2006
Member
In this case a duplicate is anything with same City, Fid, and Fid2.

Example:
Id   City      Fid   Fid2
3    Chicago   25    43
4    Chicago   25    43

Re: Prevent duplicate data entry [message #275554 is a reply to message #275551] Sun, 21 October 2007 17:17 Go to previous message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
So place a multi-field Unique Index across those 3 fields.
Previous Topic: Query Help
Next Topic: indexes
Goto Forum:
  


Current Time: Thu Mar 28 21:02:52 CDT 2024