Categories
Autonomous Database

Add Primary and Foreign Keys to your Oracle Autonomous Database

I’m not a Database Administrator, but I feel as though I may be slowly evolving into one…

In a previous post, I left myself the task of correcting the html on a proposed Python/Flask/ORDS/Autonomous Database application. My then next step was to add a search bar (which I did):

Search bar added to application for python, flask, ords, oracle autonomous database application. Chris Hoina, Oracle Senior Product Manager.
Search bar added to the html for searching my Autonomous Database.

Although this was a small step forward it led to some serious questions. While having a search feature like this is good in theory, in practice it is totally unusable.

The Problem

If a user wants to search for a specific restaurant, they’d need to know a lot of information beforehand. At a minimum, the name of their target restaurant. But even that isn’t enough. Look at when I query for a single restaurant (randomly chosen – I’m not sponsored by Wendy’s btw):

SELECT FROM LIKE SQL statement showing a single restaurant name. Chris Hoina, ORDS, Oracle Autonomous Database
SQL statement and the output (a single restaurant in this case).

Some of these restaurants come back with additional store numbers (probably a reference to either a corporate- or franchise-owned property). So a search like this simply will not do.

That’s problem number one. The second problem is that in the original datasets, while HSISID and Permit ID (read about them here) are unique in the Restaurants table. That isn’t the case with the Violations and Inspections tables. There are a TON of entries for each restaurant in these tables, and the HSISID and Permit IDs are used repeatedly for subsequent inspections. Just take a look at this example (they’re all like that though!):

Viewing the HSISID in my table; unique identifier complications, Chris Hoina, ORDS, Oracle Autonomous Database
Using the HSISID over and over again make it challenging to gather necessary information across all tables.

A Closer look

Above, is the Violations table, but the Inspection table looks similar. From what I observer, each time a violation or inspection is recorded that same HSISID is used (Permit IDs too). But in these two tables an Entry ID is used as the unique identifier. I know the image is greyed out, but if you look closely you can see the “Object ID” in that screenshot.

But these Object IDs don’t mean anything outside of this table. And it underscores the point that my original approach to searching (i.e search bar) using a single identifier won’t work. Across the three tables these restaurants are uniquely identified in different ways.

At a minimum, these tables should be linked so that when an HSISID is searched for in the Restaurant table, it gathers all associated data from the Violations and Inspections table too. Meaning, if I search for a specific “Wendy’s” only that target restaurant comes back along with all historical inspection and inspection violation information as well.

Now that we understand the problem, I’ll attempt a solution. For my next feat, I’ll relate the three tables to one another in a logical way. Continue on dear reader…


Navigating

Navigate to your Autonomous Database, then Database Actions. Once there, look under the “Development” section, and choose “SQL”.

Database Actions Development SQL Worksheet Oracle Autonomous Database, Chris Hoina, Senior Product Manager, Database Tools, ORDS
NOTE: I'm sure there are other ways to accomplish this, but this is what worked for me. 

Once in that SQL Worksheet, look at the the left side of your browser, you’ll see the Navigator tab. Of the two drop-down lists, look at the second one. That is the “Object” selector; for my purposes I made sure that Tables was selected.

Navigator tab in Oracle Database Actions, Chris Hoina, Senior Product Manager, Database Tools, OCI, Autonomous Database
Navigator tab in Oracle Database Actions in my Autonomous Database Always Free OCI account.

I have the three tables; two of which (Inspections and Violations) need Foreign Keys while the third table (Restaurants) will need a Primary Key.

My three tables in Oracle Database Actions, Chris Hoina, Senior Product Manager, Database Tools, OCI, Autonomous Database
My three tables in Oracle Database Actions: Inspections, Restaurants, Violations
TL;DR - The Restaurant table is like my Parent table, whereas the other two tables are like children (or dependents). Establishing these Primary and Foreign keys is a way for me to easily establish interdependence/relation among these three tables. 

Establishing a Primary Key

I’ll use the Restaurant table as an example. Here is the step-by-step…

Editing my table in Oracle Database Actions, Chris Hoina, Senior Product Manager, Database Tools, OCI, Autonomous Database

Right-click on the table (Restaurants), then select “Edit”.

Table Properties my table in Oracle Database Actions, Chris Hoina, Senior Product Manager, Database Tools, OCI, Autonomous Database

If a Primary Key has already been established, then you’ll see a Column (which is actually displayed as a row in this Table Properties table) that already has a check in the “PK” column.

If there isn’t one, continue by clicking the Primary Key option (see the left-most red arrow).

Primary Keys section in Database Actions SQL Worksheet Oracle Autonomous Database, Chris Hoina, Senior Product Manager, Database Tools, ORDS

Once in the Primary key option, you’ll see the following boxes with checks:

  • Enabled
  • Initially Immediate,
  • Validate
Choosing HSISID as the Primary Key in Database Actions SQL Worksheet Oracle Autonomous Database, Chris Hoina, Senior Product Manager, Database Tools, ORDS

I’m using the HSISID (found in the “Available Columns” list) as my Primary Key. Once I select that, the “Add Selected Columns” arrow will illuminate (from white to gray). Click it once to move your selection over to the right in the “Selected Columns” list.

Once your column has been moved, you can click “Apply”.

Output from establishing the Primary Key in Database Actions SQL Worksheet Oracle Autonomous Database, Chris Hoina, Senior Product Manager, Database Tools, ORDS

You’ll see this “Output” screen appear. Here you can see that my table was successfully altered. If there are errors, it’ll let you know, and there will be A LOT of red (trust me, I saw tons of red prior to writing this post).

The DDL from a table in Database Actions SQL Worksheet Oracle Autonomous Database, Chris Hoina, Senior Product Manager, Database Tools, ORDS

Bonus points, you can always find the Data Definition Language (DDL) in the DDL option (read up on this and the other types of SQL statements here).

As I see it, it looks like everything since the inception of this table has been recorded. I would imagine you could take this and build some sort of automation/orchestration script with it (along with the DDL from the other tables too).

Sample Code

Table as a Primary Key

CREATE TABLE ADMIN.RESTAURANTS 
    ( 
     OBJECTID           NUMBER , 
     HSISID             NUMBER , 
     NAME               VARCHAR2 (4000) , 
     ADDRESS1           VARCHAR2 (4000) , 
     ADDRESS2           VARCHAR2 (4000) , 
     CITY               VARCHAR2 (4000) , 
     STATE              VARCHAR2 (4000) , 
     POSTALCODE         VARCHAR2 (4000) , 
     PHONENUMBER        VARCHAR2 (4000) , 
     RESTAURANTOPENDATE DATE , 
     FACILITYTYPE       VARCHAR2 (4000) , 
     PERMITID           NUMBER , 
     X                  NUMBER , 
     Y                  NUMBER , 
     GEOCODESTATUS      VARCHAR2 (4000) 
    ) 
    TABLESPACE DATA 
    LOGGING 
;


CREATE UNIQUE INDEX ADMIN.RESTAURANTS_PK ON ADMIN.RESTAURANTS 
    ( 
     HSISID ASC 
    ) 
    TABLESPACE DATA 
    LOGGING 
;

ALTER TABLE ADMIN.RESTAURANTS 
    ADD CONSTRAINT RESTAURANTS_PK PRIMARY KEY ( HSISID ) 
    USING INDEX ADMIN.RESTAURANTS_PK ;

That is literally all did to set this up. Very straightforward, next I’ll walk through how to set up Foreign keys. Then I’ll show you what it looks like in the Data Modeler so you can visually see the newly-established relationships of the tables.


Establishing a Foreign Key

We’re still in the SQL worksheet (found in Database Actions). Both Inspections and Violations tables need a Foreign key; I’ll demonstrate with Inspections.

Foreign Key in Database Actions SQL Worksheet Oracle Autonomous Database, Chris Hoina, Senior Product Manager, Database Tools, ORDS

Right-click on the table and select “Edit”, just like I did when establishing a Primary Key. Next, navigate to the “Foreign Keys” option in the Table Properties.

Adding a Foreign Key in Database Actions SQL Worksheet Oracle Autonomous Database, Chris Hoina, Senior Product Manager, Database Tools, ORDS

It’ll be empty, but you’ll want to click the
“+” key (see the arrow). You’ll see “NEW_FK_1” appear under the Foreign Key table. If you click on that you can change the name to whatever your heart desires.

I then changed the Foreign Key name to something that I could remember (and something that would make sense at a later date); “Inspections_FK”. I then selected “HSISID” as the Local Column.

And automatically, the schema recognizes the Primary Key I previously established in the Restaurants table. I didn’t do that, that was all done for me automatically! Very cool.

Output of the Foreign Key name change in Database Actions SQL Worksheet Oracle Autonomous Database, Chris Hoina, Senior Product Manager, Database Tools, ORDS

After I click “Apply” you’ll see the output from this change. I can then return to the Foreign Keys option and voilĂ , the Foreign Key name has been changed. I’ll proceed to do the same to my “Violations” table as well.

Sample Code

Table as a Foreign Key

CREATE TABLE ADMIN.INSPECTIONS 
    ( 
     OBJECTID    NUMBER , 
     HSISID      NUMBER , 
     SCORE       NUMBER , 
     DATE_       DATE , 
     DESCRIPTION VARCHAR2 (4000) , 
     TYPE        VARCHAR2 (4000) , 
     INSPECTOR   VARCHAR2 (4000) , 
     PERMITID    NUMBER 
    ) 
    TABLESPACE DATA 
    LOGGING 
;

ALTER TABLE ADMIN.INSPECTIONS 
    ADD CONSTRAINT INSPECTIONS_FK FOREIGN KEY 
    ( 
     HSISID
    ) 
    REFERENCES ADMIN.RESTAURANTS ( HSISID ) 
    ON DELETE CASCADE 
    NOT DEFERRABLE 
;

Data Modeler Primer

This isn’t a “How-to” on the Data Modeler (Documentation here though), but rather just me taking a minute to share how you can visually see the relationships between tables. It definitely helped me better understand why I needed to establish Primary and Foreign Keys.

Andiamo!

Data Modeler in Database Actions in Oracle Autonomous Database, Chris Hoina, Senior Product Manager, Database Tools, ORDS

From the Database Actions Launchpad, select “Data Modeler”.

Navigator Tab in Data Modeler in Database Actions in Oracle Autonomous Database, Chris Hoina, Senior Product Manager, Database Tools, ORDS

Once there, you’ll see two drop-down menus. You’ll see your current Schema (Admin for me, in this case). In the second drop-down menu, you’ll see a list of available “Objects”. I’ll want to make sure that I have “Tables” selected.

Adding Objects to the Diagram Editor in Data Modeler in Database Actions in Oracle Autonomous Database, Chris Hoina, Senior Product Manager, Database Tools, ORDS

You can drag these over one at a time, or select them all and drag all at once. They’ll appear in the Diagram Editor, with the relationships already included (since I already set up the Primary and Foreign Keys).

New Diagram of tables in the Data Modeler Diagram Editor in Database Actions in Oracle Autonomous Database, Chris Hoina, Senior Product Manager, Database Tools, ORDS

Expand these diagrams and you’ll reveal additional information. In this image, I’ve expanded the Inspections table to reveal Foreign Key details. I’ll do the same by expanding the bottom of the Violations table to reveal its Foreign Key. That little red arrow signifies that there is additional information hidden.

Okay, but what is this?

What we are looking at is a visual representation of the relationships between these tables. Everything is “linked” through the HSISID. My interpretation of this is that everything begins with the Restaurant table, and then the other two tables act as subordinates.

For a single Restaurant (which has a unique HSISID) there are many entries in both Inspections and Violations that use the HSISID repeatedly. In those cases, that HSISID is no longer unique. It is still important, necessary information.

And now that everything is linked, I’ll want to create a single query (SQL presumably) that when executed will return all relevant information from a single entry in the Restaurant table with all associated (HSISID) entries from the Inspections and Violations table, nested underneath it.

Clear as mud? Good. Hope this helps more than it hurts.

Now what

Next I’ll need to come up with something (in SQL perhaps) that I believe I’ll use in tandem with ORDS. From there I’ll work on building the html portion of this python + flask application so that the search is more intuitive (to the user) and elegant (for the Autonomous Database). Believe it or not, but it took about a week to wrap my head around all this key business. Needless to say, I’m slightly behind schedule. But expect more updates as I progress.

Hope you learned something.

Find me

HMU on:

Leave a Reply

Your email address will not be published.