Primary Key Workaround

[JJOHNS]

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 = ERSON_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?

 

[LKBrwnDBA]

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



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

 

[SantaMufasa]

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:

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:

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

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

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

 

[JJOHNS]

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.

 

[LKBrwnDBA]

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.



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

 

[JJOHNS]

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.

 

[JJOHNS]

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.

 

[Beilstwh]

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