INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

Primary Key Workaround

Primary Key Workaround

(OP)
I have an Oracle Form which allows users to add more than one record into a table for a given person. Each person in the database is supposed to be allowed one and only one record. The simple solution was to use the person's ID as a primary key when the table was created. Unfortunately, the table was created with no primary key and has been in production this way for a few years. I cannot go back and add the primary key now because I may corrupt existing data. My problem is I need to change the form, probably using triggers, to act like it has the primary key. I hope that makes sense.

I have tried this trigger, using PRE_INSERT and ON-INSERT at the datablock level:

BEGIN
DECLARE
TEMP NUMBER;

BEGIN
SELECT COUNT(PERSON_ID) INTO TEMP
FROM MYTABLE
WHERE PERSON_ID = :PERSON_ID;

IF TEMP > 0 THEN
MESSAGE('*ERROR* Student already has a record in MYTABLE.');
:FORM_FIELD := '';
RAISE FORM_TRIGGER_FAILURE;
END IF;
END;
END;

PERSON_ID is the Person's ID and should have been the primary key for the MYTABLE table. In the datablock, however, it is invisible, pre-filled from the KEY_BLOCK. FORM_FIELD is the field where the cursor lands when entering the datablock and where the actions occur.

So, my question is, does anyone know how to use triggers (or properties) to make a form act like there is a primary key in the underlying table when there isn't one?

RE: Primary Key Workaround

You could still create a UNIQUE index on the person_id and it would act like a primary key.
3eyes

----------------------------------------------------------------------------
The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

RE: Primary Key Workaround

Quote (JJohns)

I cannot go back and add the primary key now because I may corrupt existing data.

I presume that you mean that you cannot go back and add the primary key constraint, right? If that is the case, I don't see how any corruption can occur by adding a PRIMARY KEY constraint. All that does is confirm that PERSON_ID is UNIQUE and NOT NULL. In fact, if you tried to implement the PK and it threw an error due to PERSON_ID being non-UNIQUE or NULL, then your data is already corrupt (or at least not following your business rules for that column).

If you want to find out if there will be a problem, then issue the following queries:

CODE

select count(*) from mytable where person_id is null;

select person_id,count(*) from mytable
 group by person_id
 having count(*) > 1; 

If you get results from either query, then your data are already problematical and you should figure out what you want to do about the offending rows. If you do not receive positive results from either query, then you can issue the following command and successfully be on your way without any chance of corrupting your data:

CODE

alter table mytable add constraint mytable_person_id_pk primary key (person_id); 

And, BTW, LKBrwnDBA's solution works, as well.

santaMufasa
(aka Dave of Sandy, Utah, USA)
“People may forget what you say, but they will never forget how you made them feel."

RE: Primary Key Workaround

(OP)
Thank you both for replying. Here is my problem: This application has been in production for several years (since 2007) and has been applied to 36 separate databases. I know that there are a lot of records already in databases that should not have been created. These databases belong to customers and I cannot alter the existing data at all. My task is to keep that from happening in the future without affecting what has already been done in the past. I cannot go back now and create a primary key because of the data that has already been entered. Unfortunately, I am not able to create a unique index, either. I get the error: ORA-01452: cannot CREATE UNIQUE INDEX; duplicate keys found.

I believe my only option here is to use triggers in the form. I just haven't found the right code to make this work yet.

RE: Primary Key Workaround


This is the time where you report to the "customers" that their data has duplicates and transfer to them the "hot" potato (what do they want to do with these dups?).

With your trigger you will not solve the issue, perhaps only prevent new dups to be added.
noevil

----------------------------------------------------------------------------
The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

RE: Primary Key Workaround

(OP)
"With your trigger you will not solve the issue, perhaps only prevent new dups to be added."

That is all I have been tasked to do.

RE: Primary Key Workaround

(OP)
I'd like to thank all of you for your assistance. If anyone is interested, here is how I resolved my problem:

I used a KEY-CREREC trigger to present an error message if the user clicks the Insert Record icon and the student already has at least one record.
I used a KEY-NXTREC to keep the user from arrowing down past the last record to insert a new one.

I have made it impossible for the user to insert more than one record for the student now, but left it possible for them to scroll through the records where the mistake was already made in the past.

I now realize that my question should have been posted in another forum, the Oracle Enterprise Development Suite forum. I'll be sure to use the correct forum in the future.

RE: Primary Key Workaround

you could have added a primary key without validation which will allow the old duplicates to be in the table but no new ones. You do this by making a non unique index on the user_id and then make it a primary key without validation

create index my_table_t1 on my_table(person_id);
ALTER TABLE my_table ADD PRIMARY KEY (person_id) NOVALIDATE;

Bill
Lead Application Developer
New York State, USA

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close