MacLochlainns Weblog

Somebody asked how to validate foreign key constraints in an Oracle database. The following query finds constraints, and displays the table and column that holds constraint with table and column name pointed to by the constraint. Since a foreign key to primary key relationship defines the list of values for a foreign key column, the values must be found in the primary key column.

Both user_constraints and user_cons_columns are catalog views that limit you to your own schema. The user_constraints view lets you find information about constraints, while the user_cons_columns view lets you see column level detail about the constraints.

The query lets you resolve where to look for those keys without manually inspecting table creation scripts.

COL constraint_source FORMAT A38 HEADING "Constraint Name:| Table.Column"
COL references_column FORMAT A38 HEADING "References:| Table.Column"
 
SELECT   uc.constraint_name||CHR(10)
||      '('||ucc1.table_name||'.'||ucc1.column_name||')' constraint_source
,       'REFERENCES'||CHR(10)
||      '('||ucc2.table_name||'.'||ucc2.column_name||')' references_column
FROM     user_constraints uc
,        user_cons_columns ucc1
,        user_cons_columns ucc2
WHERE    uc.constraint_name = ucc1.constraint_name
AND      uc.r_constraint_name = ucc2.constraint_name
AND      ucc1.position = ucc2.position -- Correction for multiple column primary keys.
AND      uc.constraint_type = 'R'
ORDER BY ucc1.table_name
,        uc.constraint_name;

You generate the following output when you run this query. The results shows you: (a) constraint names with their corresponding table and column names; and (b) table and column names that holds the primary key which is referenced by foreign keys.

Constraint Name:                       References
Table.Column                           Table.Column
-------------------------------------- --------------------------------
FK_ADDRESS_1                           REFERENCES
(ADDRESS.CONTACT_ID)                   (CONTACT.CONTACT_ID)
 
FK_ADDRESS_2                           REFERENCES
(ADDRESS.ADDRESS_TYPE)                 (COMMON_LOOKUP.COMMON_LOOKUP_ID)
 
FK_ADDRESS_3                           REFERENCES
(ADDRESS.CREATED_BY)                   (SYSTEM_USER.SYSTEM_USER_ID)
 
FK_ADDRESS_4                           REFERENCES
(ADDRESS.LAST_UPDATED_BY)              (SYSTEM_USER.SYSTEM_USER_ID)
 
FK_COMMON_LOOKUP_1                     REFERENCES
(COMMON_LOOKUP.CREATED_BY)             (SYSTEM_USER.SYSTEM_USER_ID)
 
FK_COMMON_LOOKUP_2                     REFERENCES
(COMMON_LOOKUP.LAST_UPDATED_BY)        (SYSTEM_USER.SYSTEM_USER_ID)
 
FK_CONTACT_1                           REFERENCES
(CONTACT.MEMBER_ID)                    (MEMBER.MEMBER_ID)
 
FK_CONTACT_2                           REFERENCES
(CONTACT.CONTACT_TYPE)                 (COMMON_LOOKUP.COMMON_LOOKUP_ID)
 
FK_CONTACT_3                           REFERENCES
(CONTACT.CREATED_BY)                   (SYSTEM_USER.SYSTEM_USER_ID)
 
FK_CONTACT_4                           REFERENCES
(CONTACT.LAST_UPDATED_BY)              (SYSTEM_USER.SYSTEM_USER_ID)

You can then query the table and column referenced by the foreign key to determine the valid list of primary keys in the table. Extending the basic query design, you can narrow it to a specific constraint. This becomes very useful when you try to insert a row into an address table with an foreign key value that isn’t found in the list of valid primary keys.

A sample INSERT statement would be:

INSERT INTO address
VALUES
( 1101
, 1008
, 2001                                  -- This foreign key isn't a valid primary key.
,'Nowhereville'
,'Beatledom'
,'11111-1111'
, 3
, SYSDATE
, 3
, SYSDATE );

In my test instance, you would get a constraint violation error like the one below. You can download the setup scripts from McGraw-Hill’s web site for Oracle Database 11g PL/SQL Programming book that I wrote.

INSERT INTO address
*
ERROR at line 1:
ORA-02291: integrity CONSTRAINT (STUDENT.FK_ADDRESS_2) violated - parent KEY NOT found

Then, you can modify the earlier query to find the offending primary key column. Its offense is that there is no equivalent value to what you tried to input into another table. Here’s how you find the primary key column table:

COL constraint_source FORMAT A38 HEADING "Constraint Name:| Table.Column"
COL references_column FORMAT A38 HEADING "References:| Table.Column"
 
SELECT   uc.constraint_name||CHR(10)
||      '('||ucc1.table_name||'.'||ucc1.column_name||')' constraint_source
,       'REFERENCES'||CHR(10)
||      '('||ucc2.table_name||'.'||ucc2.column_name||')' references_column
FROM     user_constraints uc
,        user_cons_columns ucc1
,        user_cons_columns ucc2
WHERE    uc.constraint_name = ucc1.constraint_name
AND      uc.r_constraint_name = ucc2.constraint_name
AND      uc.constraint_type = 'R'
AND      uc.constraint_name = UPPER('&input_constraint_name');

It returns the following in my test instance:

CONSTRAINT Name:                       REFERENCES:
 TABLE.Column                           TABLE.Column
-------------------------------------- --------------------------------------
FK_ADDRESS_2                           REFERENCES
(ADDRESS.ADDRESS_TYPE)                 (COMMON_LOOKUP.COMMON_LOOKUP_ID)

You can now verify whether the value, 2001, that you tried to insert into a foreign key column exists. The query would be like follows:

SELECT   common_lookup_id
FROM     common_lookup
WHERE    common_lookup_id = 2001;

The query will say that no rows were found. You can remove the WHERE clause to find the list of valid primary key values.

I played around with a friend’s Kindle and really had to wonder why would people buy one of them. Since my books sell an electronic edition on Kindle, I hoped for a better solution.

Great news today, a better physical technology has arrived. You can now download a Kindle application for your iPhone! As a big iPhone fan, this is awesome. Naturally, I was curious how my last book looked.

I downloaded the sample chapter and it looked great on the iPhone. The images are well rendered and clear on the screen. I’m probably going to have to buy an e-copy (ouch) because they don’t provide authors with electronic copies.

Thanks a bunch to the team who ported it to the iPhone. Great job!

Somebody suggested that I post a simple quick view of how a basic query works. The following illustrates how the FROM is read first. Aliases assigned in the FROM clause, like the i, replace the full name of the table or view. Column references don’t require prepending with the table or view alias but doing so adds clarity in the query. You do need prepend table aliases or names when two or more columns returned by the query have the same names. This happens when you’re joining two or more tables because the SQL parser finds that they’re ambiguously defined otherwise.

While the column aliases use the optional AS keyword, table aliases must directly follow the table name. Table name really means a table, view, or inline view name. They can also mean a subquery factoring clause, which is the fancy name for a WITH statement – blogged on it here.

Selection comes in two phases, identifying the sources or tables in the FROM clause, and then filtering the sources based on comparisons in the WHERE clause. Join statements are also filters that match rows from different tables based on value or range comparisons. You find join statements in the FROM clause as part of a ON or USING subclause when queries use key words like JOIN et cetera. You find join statements in the WHERE clause when the queries list tables as comma separated elements in a FROM clause.

Projection is the narrowing of rows into columns qualified by the select list. A select list is the comma separated columns returned by a query in the SELECT clause.

While database management systems have their own particulars about sequencing and optimization, more or less they find the data sources, read the rows or indexes to rows, and then narrow the columns returned to those qualified in a select list. That’s about it unless a query involves aggregation or sorting operations.

Aggregation typically happens after selection but before projection. While columns in the SELECT clause often set the aggregation grouping, you may use columns other than those in the select list. You qualify the grouping columns in the optional GROUP BY clause.

Sorting by a column is done through the ORDER BY clause. The sorting of data follows the selection process, unless there is an aggregation process. Sorting follows aggregation when it is present in a query. Aggregated data sets are limited to ordering by columns in the GROUP BY clause.

Somebody posted a question about a dynamic NDS example found in the Oracle Database 11g PL/SQL Programming book on page 388. They asked if there was an easier way.

The answer is yes. Here’s a different example implementing the same concept on Native Dynamic SQL (NDS) with an input parameter. I borrowed it from the example I used for an Oracle framework to mimic the MySQL ENUM data type.

Basically, the following shows how you write a function using a dynamic NDS statement with an input parameter.

CREATE OR REPLACE FUNCTION proper_item_type 
( item_type_in VARCHAR2 ) RETURN VARCHAR2 IS
  -- Define a weakly typed system reference cursor.
  item_cursor   SYS_REFCURSOR;
 
  -- Define a target variable for the query result.
  item_type_out VARCHAR2(30);
 
  -- Create NDS statement, with a bind or placeholder variable.
  stmt          VARCHAR2(2000) := 'SELECT type_name '
                               || 'FROM   item_type '
                               || 'WHERE  UPPER(type_name) = UPPER(:type_name_in)';
BEGIN
  -- Open the cursor and dynamically assign the function actual parameter.
  OPEN item_cursor FOR stmt USING item_type_in;
 
  -- Fetch the first row return and return the value.
  FETCH item_cursor INTO item_type_out;
 
  -- CLose the cursor.
  CLOSE item_cursor;
 
  -- Return the value.
  RETURN item_type_out;
END;
/

This is certainly overkill if you only want to substitute a single parameter into a cursor. A simpler approach would be to write a dynamic cursor, and then open the cursor by passing the actual parameter. Here’s that example.

CREATE OR REPLACE FUNCTION proper_item_type 
( item_type_in VARCHAR2 ) RETURN VARCHAR2 IS
  -- Define a dynamic cursor.
  CURSOR c (item_type_name VARCHAR2) IS
    SELECT type_name
    FROM   item_type
    WHERE  UPPER(type_name) = UPPER(item_type_name);
BEGIN
  -- Open the cursor and dynamically assign the function actual parameter.
  FOR i IN c(item_type_in) LOOP
    RETURN i.type_name;
  END LOOP;
END;
/

An even more primitive approach relies on implicit assignment, like the following:

CREATE OR REPLACE FUNCTION proper_item_type 
( item_type_in VARCHAR2 ) RETURN VARCHAR2 IS
BEGIN
  -- Open the cursor and rely on implicit assignment within the cursor.
  FOR i IN (SELECT type_name
            FROM   item_type
            WHERE  UPPER(type_name) = UPPER(item_type_in)) LOOP
    RETURN i.type_name;
  END LOOP;
END;
/

I hope this answers the question. You can click on the Setup Code line to unfold the code. Let me know if you like this approach to posting setup code.

-- Conditionally drop table and sequence before attempting to create them.
BEGIN
  FOR i IN (SELECT TABLE_NAME
            FROM   user_tables
            WHERE  TABLE_NAME IN (UPPER('item_type')
                                 ,UPPER('item'))) LOOP
    EXECUTE IMMEDIATE 'DROP TABLE '||i.table_name||' CASCADE CONSTRAINTS';
  END LOOP;
  FOR i IN (SELECT sequence_name
            FROM   user_sequences
            WHERE  sequence_name IN (UPPER('item_type_s1')
                                    ,UPPER('item_s1'))) LOOP
    EXECUTE IMMEDIATE 'DROP SEQUENCE '||i.sequence_name;
  END LOOP;
END;
/
 
-- Create item_type table, index on the natural key, and sequence; then seed data.
CREATE TABLE item_type
( item_type_id NUMBER        CONSTRAINT pk_item_type   PRIMARY KEY
, TABLE_NAME   VARCHAR2(30)  CONSTRAINT nn_item_type_1 NOT NULL
, column_name  VARCHAR2(30)  CONSTRAINT nn_item_type_2 NOT NULL
, type_name    VARCHAR2(30)  CONSTRAINT nn_item_type_3 NOT NULL
, code         VARCHAR2(5)
, meaning      VARCHAR2(255) CONSTRAINT nn_item_type_4 NOT NULL);
 
CREATE UNIQUE INDEX item_type_u1
  ON item_type(TABLE_NAME,column_name,type_name);
 
CREATE TABLE item
( item_id        NUMBER        CONSTRAINT pk_item    PRIMARY KEY
, item_type      NUMBER        CONSTRAINT nn_item_1  NOT NULL
, item_title     VARCHAR2(60)  CONSTRAINT nn_item_2  NOT NULL
, item_rating_id NUMBER        CONSTRAINT nn_item_3  NOT NULL);
 
CREATE SEQUENCE item_type_s1 START WITH 1001;
CREATE SEQUENCE item_s1 START WITH 1001;
 
INSERT INTO item_type VALUES (item_type_s1.NEXTVAL,'ITEM','ITEM_TYPE','DVD',NULL,'DVD');
INSERT INTO item_type VALUES (item_type_s1.NEXTVAL,'ITEM','ITEM_TYPE','VHS',NULL,'VHS');
INSERT INTO item_type VALUES (item_type_s1.NEXTVAL,'ITEM','ITEM_TYPE','Blu-ray',NULL,'BLRY');

I went back and edited that post about MySQL nuances not in Oracle from last week about the MySQL ENUM data type. You can find a framework suggestion that lets you not have to change existing string conditioning application code during a migration from MySQL to Oracle.

You’ll see this in the middle of the page, just click it to unfold the details. Yes, JQuery has arrived on my blog.

As with everything else on this blog, let me know if you see an opportunity for improvement.

I use Alan Beaulieu’s book in my entry level SQL class because it is simple and short. The problem is that we focus on Oracle products as an Oracle Academic Partner. Three items that come up frequently are the MySQL ENUM and SET data types, and how to perform multiple row inserts.

MySQL’s ENUM data type

The ENUM data type lets you enter a list of possible string values. It acts like a check constraint in an Oracle database. As such, it restricts what you enter in the column to a value found in the list, or a NULL value provided you’ve not added a not null column constraint.

You could define a table that contains video store item types, like the following:

CREATE TABLE item_type
( id INT
, text ENUM ('VHS','DVD','Blu-ray') );

You should note that the case sensitivity for display is set by how you define them in the ENUM data type when you create the table. They may be entered in mixed, lowercase, or uppercase in an INSERT statement because they’re actually stored as a number. The numbers correlate to their order in an internal list of values, and that list start with the number one.

An alternative syntax with a VARCHAR data type is:

CREATE TABLE item_type
( id INT
, TYPE VARCHAR(20) CHECK ( TYPE IN ('DVD','VHS','Blu-ray') ));

In MySQL, this syntax is exactly equivalent in behavior to an ENUM data type. The same isn’t true in an Oracle database. While the equivalent check constraint statement is simple, it isn’t alike behavior. The comparable statement for Oracle names the constraint. You can’t name constraints in MySQL.

CREATE TABLE item_type
( id   NUMBER
, TYPE VARCHAR2(20) CONSTRAINT it_type CHECK ( TYPE IN ('DVD','VHS','Blu-ray') ));

The difference between an ENUM type and a check constraint in MySQL is that the data may not display in a uniform way. MySQL check constraints don’t impose case sensitive validation on input strings, and they also store the data however it is input. Whereas, Oracle does impose case sensitive check constraints and rejects non-conforming strings.

Both databases support single and multiple row INSERT statements. The syntax for single row INSERT statements is very much alike. Multiple row INSERT statement syntax differs between the implementations, as shown later in the blog post.

-- Conditionally drop table and sequence before attempting to create them.
BEGIN
  FOR i IN (SELECT TABLE_NAME
            FROM   user_tables
            WHERE  TABLE_NAME IN (UPPER('item_type')
                                 ,UPPER('item'))) LOOP
    EXECUTE IMMEDIATE 'DROP TABLE '||i.table_name||' CASCADE CONSTRAINTS';
  END LOOP;
  FOR i IN (SELECT sequence_name
            FROM   user_sequences
            WHERE  sequence_name IN (UPPER('item_type_s1')
                                    ,UPPER('item_s1'))) LOOP
    EXECUTE IMMEDIATE 'DROP SEQUENCE '||i.sequence_name;
  END LOOP;
END;
/
 
-- Create item_type table, index on the natural key, and sequence; then seed data.
CREATE TABLE item_type
( item_type_id NUMBER        CONSTRAINT pk_item_type   PRIMARY KEY
, TABLE_NAME   VARCHAR2(30)  CONSTRAINT nn_item_type_1 NOT NULL
, column_name  VARCHAR2(30)  CONSTRAINT nn_item_type_2 NOT NULL
, type_name    VARCHAR2(30)  CONSTRAINT nn_item_type_3 NOT NULL
, code         VARCHAR2(5)
, meaning      VARCHAR2(255) CONSTRAINT nn_item_type_4 NOT NULL);
 
CREATE UNIQUE INDEX item_type_u1
  ON item_type(TABLE_NAME,column_name,type_name);
 
CREATE TABLE item
( item_id        NUMBER        CONSTRAINT pk_item    PRIMARY KEY
, item_type      NUMBER        CONSTRAINT nn_item_1  NOT NULL
, item_title     VARCHAR2(60)  CONSTRAINT nn_item_2  NOT NULL
, item_rating_id NUMBER        CONSTRAINT nn_item_3  NOT NULL);
 
CREATE SEQUENCE item_type_s1 START WITH 1001;
CREATE SEQUENCE item_s1 START WITH 1001;
 
INSERT INTO item_type VALUES (item_type_s1.nextval,'ITEM','ITEM_TYPE','DVD',NULL,'DVD');
INSERT INTO item_type VALUES (item_type_s1.nextval,'ITEM','ITEM_TYPE','VHS',NULL,'VHS');
INSERT INTO item_type VALUES (item_type_s1.nextval,'ITEM','ITEM_TYPE','Blu-ray',NULL,'BLRY');

CREATE OR REPLACE FUNCTION proper_item_type
( item_type VARCHAR2 ) RETURN VARCHAR2 IS
  CURSOR c (type_name_in VARCHAR2) IS
    SELECT type_name
    FROM   item_type
	  WHERE  UPPER(type_name) = UPPER(type_name_in);
BEGIN
  FOR i IN c (item_type) LOOP
    RETURN i.type_name;
  END LOOP;
END;
/

CREATE OR REPLACE TRIGGER proper_item_type_t1
  BEFORE INSERT OR UPDATE ON item
  FOR EACH ROW
BEGIN
  :NEW.item_type := proper_item_type(:new_item_type);
END;
/

INSERT INTO item VALUES (item_s1.nextval, 'BLU-RAY', 'Star Wars I', 1001);

SELECT * FROM item;

MySQL’s SET data type

The SET data type lets you enter a list of possible string values but differs from the ENUM data type only in how the values are indexed. Values in the SET are stored as bit values. You may store up to 64 members in a set.

You create a table the same way as you did with ENUM data type, except that you use the SET key word.

CREATE TABLE item_type
( id INT
, TYPE SET ('DVD','VHS','Blu-ray'));

All insert patterns shown for the ENUM data type work with the SET data type.

Multiple row INSERT statements

The most common multiple row insert statement in MySQL is a comma delimited set of parenthetical values. Each parenthetical set of values maps to a row in the INSERT statement.

INSERT INTO item_type
VALUES
( 1, 'DVD' ), ( 2, 'Blu-ray'), ( 3, 'VHS' );

You can’t use the foregoing syntax in Oracle. Oralce only supports a multiple row insert with a subquery.

You can write a subquery in MySQL that fabricates an aggregate table by using numeric and string literals, like the following statement.

INSERT INTO item_type
SELECT 1, 'DVD'
UNION ALL
SELECT 2, 'Blu-ray'
UNION ALL
SELECT 3, 'VHS' ;

Another way to perform multiple row inserts in MySQL is to select from an existing table or filtered result set, like this:

INSERT INTO item_type
SELECT some_int, some_varchar FROM some_table;

You raise an error when you use parentheses in a multiple INSERT statement in MySQL. It’s important to note because the Oracle allows you to write the statement either way.

If you’re coming from MySQL to Oracle, you should note that you may use parentheses to enclose a subquery in Oracle. The Oracle SQL parser works with or without them in an INSERT statement.

The MySQL subquery example is the closest to the Oracle syntax for a multiple row insert. The difference is that Oracle give you the option to enclose a subquery in parentheses when using them inside INSERT statements. Oracle requires that you use the FROM dual clause. I actually wish Oracle would adopt the shorter syntax and maintain backward compatibility to the dual pseudo table.

INSERT INTO item_type
( SELECT 1, 'DVD' FROM dual
  UNION ALL
  SELECT 2, 'Blu-ray' FROM dual
  UNION ALL
  SELECT 3, 'VHS'  FROM dual);

Another way to perform a multiple row insert is to select from an existing table, like this:

INSERT INTO item_type
(SELECT some_int, some_varchar FROM some_table);

Other resources:

You should check Oracle’s document that qualifies differences between MySQL and Oracle. It is the Oracle® Database SQL Developer Supplementary Information for MySQL Migrations.

Somebody commented last week that they’d like the source code for an XSL example file found in this older post. I’ve added the requested code there. The code lets you query directly from your XSL against an Oracle database.

I’ve also created a top page for collecting references to XML blog pages and posts. It’s only got a few but it is an area that will be expanded.

I’m trying to eliminate the textbook from my database class, and wrote a data normalization blog page for my students; however, it is only done through 3NF so far. The post tries to remove the discrete math and provide clear examples. The students are checking it out for opportunities to make it clearer, if you’ve thoughts let me know.

Also, I’ve substantially update my initial blog page on SQL joins, and will put one out on set operators probably this Monday. If you’ve time to review it, I would appreciate suggestions for improvement.

The length of these blog pages has compelled me to move to a more friendly editor. I’ve opted for Blogo as my Mac blog editor. It comes from Brainjuice. My son’s experience with their customer service and product convinced me. You can find his blog post discussing customer service here.

Blogo prompts you, but beat the rush. Go to your Site Admin, select Writing under Settings in WordPress, and enable the remote XML-RPC publishing protocol. More or less like this prompt.

It’s almost funny reflecting on when I first learned how to write SQL in 1985. It was using SQL/DS 1.1 (Structured Query Language/Data System) on a VMS/CMS operating system at the IBM center in the Los Angeles, California. That was in the Crocker Bank Building when I worked for First Interstate Bank. The building is still there and so are databases but both banks are long gone.

After 24 years of SQL a quick primer on joins seems like a good idea. I have a simple example that should help illustrate various joins and SQL semantics. You’ll find it as a blog page here …

LAMP the ubiquitous acronym for Linux, Apache, MySQL, and PHP, Python, or Perl has a couple cousins. They are OPAL (Oracle’s stack on Linux, and MAMP (Mac OS X, Apache, MySQL, and PHP et cetera). Perhaps another acronym on the horizon is: OPAM (Oracle, PHP, Apache, and Mac OS X). OPAM is a guess on my part. Nobody knows what Oracle’s marketing department may choose. Regardless of the acronym for it, Oracle has published instructions for configuring an Oracle/PHP stack on Mac OS X.

I generally configure the OPAL stack with Zend Core for Oracle and the Oracle database on a virtual machine running Windows XP, Windows Vista, Ubuntu, or Red Hat Linux. If you follow my posts I prefer VMWare Fusion over Parallels. The MAMP stack I use is open source and provided by Living E. It follows the pattern of Mac OS X installations, which differs from the recent posting from Oracle. It’s easy to install, as you tell from the documentation. MAMP installs PHP 5.2.6 as the current release.

It’s a great choice when you incorporate the open source Sequel Pro tool. Isn’t it ashame that Sequel Pro doesn’t work natively with Oracle. If I find some time this summer, it might make a great project to extend it to Oracle. The interface to Sequel Pro looks like this:

When you create a connection, you should know the typical values. The database value can be left blank when connecting as the superuser root:

Host:     localhost
User:     root
Password: root
Database: 
Socket:   /Applications/MAMP/tmp/mysql/mysql.sock
Port:     3306

Here’s the connection dialog where you’ll enter the values:

Have fun playing with the MAMP stack.