Add Primary and Foreign Keys to your Oracle Autonomous Database

Search bar added to application for python, flask, ords, oracle autonomous database application. Chris Hoina, Oracle Senior Product Manager.

The Problem

SELECT FROM LIKE SQL statement showing a single restaurant name. Chris Hoina, ORDS, Oracle Autonomous Database
Viewing the HSISID in my table; unique identifier complications, Chris Hoina, ORDS, Oracle Autonomous Database

A Closer look

Navigating

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.
Navigator tab in Oracle Database Actions, Chris Hoina, Senior Product Manager, Database Tools, OCI, Autonomous Database
My three tables in Oracle Database Actions, Chris Hoina, Senior Product Manager, Database Tools, OCI, Autonomous Database
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

Editing my table in Oracle Database Actions, Chris Hoina, Senior Product Manager, Database Tools, OCI, Autonomous Database
Table Properties my table in Oracle Database Actions, Chris Hoina, Senior Product Manager, Database Tools, OCI, Autonomous Database
Primary Keys section in Database Actions SQL Worksheet Oracle Autonomous Database, Chris Hoina, Senior Product Manager, Database Tools, ORDS
  • 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
Output from establishing the Primary Key in Database Actions SQL Worksheet Oracle Autonomous Database, Chris Hoina, Senior Product Manager, Database Tools, ORDS
The DDL from a table in Database Actions SQL Worksheet Oracle Autonomous Database, Chris Hoina, Senior Product Manager, Database Tools, ORDS

Sample Code

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 ;

Establishing a Foreign Key

Foreign Key in Database Actions SQL Worksheet Oracle Autonomous Database, Chris Hoina, Senior Product Manager, Database Tools, ORDS
Adding a Foreign Key in Database Actions SQL Worksheet Oracle Autonomous Database, Chris Hoina, Senior Product Manager, Database Tools, ORDS
Establishing Foreign Keys and changing the name in Database Actions SQL Worksheet Oracle Autonomous Database, Chris Hoina, Senior Product Manager, Database Tools, ORDS
Part 1
Establishing Foreign Keys and changing the name in Database Actions SQL Worksheet Oracle Autonomous Database, Chris Hoina, Senior Product Manager, Database Tools, ORDS
Part 2
Output of the Foreign Key name change in Database Actions SQL Worksheet Oracle Autonomous Database, Chris Hoina, Senior Product Manager, Database Tools, ORDS

Sample Code

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

Andiamo!

Data Modeler in Database Actions in Oracle Autonomous Database, Chris Hoina, Senior Product Manager, Database Tools, ORDS
Navigator Tab in Data Modeler in Database Actions in Oracle Autonomous Database, Chris Hoina, Senior Product Manager, Database Tools, ORDS
Adding Objects to the Diagram Editor in Data Modeler in Database Actions in Oracle Autonomous Database, Chris Hoina, Senior Product Manager, Database Tools, ORDS
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

Okay, but what is this?

Now what

Find me

--

--

--

I’m a Senior Product Manager at Oracle. But I’m more than that. I’m not to be objectified, like some shiny toy or trinket…

Love podcasts or audiobooks? Learn on the go with our new app.

Recommended from Medium

Everything is object !

Hyperledger Fabric 2.2 Tutorial

The Best Ways to Teach Yourself to Code

Restricting the network to ping only to the Google Search Engine

SRE / DevOps / Kubernetes Weekly Collection#30(Week 35)

Final Entry

Programming Dance — (A)synchronicity

Reduce Unit Test Runtime Using GitLab CI Parallel

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store
Chris Hoina

Chris Hoina

I’m a Senior Product Manager at Oracle. But I’m more than that. I’m not to be objectified, like some shiny toy or trinket…

More from Medium

How to make the Data Warehouse automagical

Overview on Database Components

Load data from Google sheet to AWS RDS using Fivetran

Amazon Lake Formation