Solved

Changing database structure by Adding Primary Key field SQL 11g

Posted on 2014-10-28
7
309 Views
Last Modified: 2014-10-31
Greetings Experts -

Today a general discussion question.

I have inherited a database an Oracle 11g database with NO primary key field.  
Instead an external identifier with a not null constraint was used to relate all the tables, run the forms and update the records.  

As is the fate of all external identifiers, it is now being superseded and will no longer be available to my inherited database.  Even better--not every record in the inherited database will be assigned a new external identifier.

Ideally, I would like to migrate the old external identifier into a new field, and modify its former field to a primary key field with an autonumber sequence.  By reusing the field I hope to avoid having to rewrite all of the tables/queries and forms.

I am unclear as to the method and timing of updating the related tables.  Should I create a new temporary field in the 'base' table so that I maintain integrity while I update the related child tables?  Other pitfalls?  Is this an accepted approach?  Are there others?

CongoGrey
0
Comment
Question by:Prisoner362670
  • 4
  • 3
7 Comments
 
LVL 29

Expert Comment

by:MikeOM_DBA
ID: 40409500
Can you post a sample of the table definition?
0
 

Author Comment

by:Prisoner362670
ID: 40413112
Sure Mike - Sorry for the delayed response as I have been ill.

Table1: Demography
ColName             IDNO                                    FName  
DataType            VARCHAR2 (7 Byte)            VARCHAR2(20 Byte)
Nullable              NO                                           Yes
DataDefault        (null)                                          (null)
PrimaryKey          (null)                                         (null)                                            
ConstraintType    Check (idno is not null)
Status                    Enabled                                  
Deferrable            Not Deferrable
IndexName            (null)      

Table2: RelatedTable1

ColName                    IDNO
DataType                    VARCHAR2 (7 Byte)          
Nullable                       No
DataDefault                (null)
PrimaryKey                 (null)

Let me know if you need additional information
0
 
LVL 29

Expert Comment

by:MikeOM_DBA
ID: 40413193
Sorry, but I am still not sure what you mean by "an external identifier"?
What is the source and how is the local table populated with this value?

Why not preserve the current values of said identifier converting it to primary key and just code a function to generate the future values for that key?
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 

Author Comment

by:Prisoner362670
ID: 40413350
The 'external identifier' is an id number generated by another database.  Certain demographic information from that database is imported into the Oracle 11g database using the SQL loader utility.  Imports may be new records or updates to existing records.

The 'external identifier' will change both size and datatype in the external database within a few months.  
There are several tacks I could take which is why this is a discussion question.

Your suggestion is novel to me.  Let me restate it to make sure that I understand:  modify the idno field to primary key, retain the current values in idno and set a sequence function to assign values to all new records.
Create a new field to hold the new external identifier and use this value to locate records for update.

Another twist on the same suggestion: If I updated the field/datatype in the Oracle 11g demographic table with a primary key constraint and updated the old identifier to the new identifier (because I need to perform updates using the external database), how would I change the old identifier written in the related tables in Oracle 11g to the new identifier?
0
 
LVL 29

Accepted Solution

by:
MikeOM_DBA earned 500 total points
ID: 40414239
Maybe we are on the same page:

1) Add column for the new identifier
2) Copy old identifier to new identifier column in order to change to the new identifier when available
3) Create unique index on IDN0 (old identifier)
4) Add primary key constraint  on (IDN0) using index  from (3)
5) Create a function that will generate PK values when new row is inserted
6) Code trigger on insert to populate PK using the function

. . . And no need to modify related tables.
0
 

Author Comment

by:Prisoner362670
ID: 40415796
Hi Mike - sounds like a plan!  I'll try it in my test environment between this afternoon and Monday afternoon and let you know how it turns out.
However, in the meantime I'm going to accept your solution.  It was the 'no need to modify related tables' that did it for me.
0
 

Author Closing Comment

by:Prisoner362670
ID: 40415798
I have yet to test this solution but overall it seems sound and prudent.  I will write back after test.
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Note: this article covers simple compression. Oracle introduced in version 11g release 2 a new feature called Advanced Compression which is not covered here. General principle of Oracle compression Oracle compression is a way of reducing the d…
How to Unravel a Tricky Query Introduction If you browse through the Oracle zones or any of the other database-related zones you'll come across some complicated solutions and sometimes you'll just have to wonder how anyone came up with them.  …
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.

705 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now