MacLochlainns Weblog

One of my students asked how you validate the foreign keys in a MySQL database. First off, this only works if the database engine supports referential integrity (the fancy word for foreign keys as database level constraints). InnoDB and Falcon support referential integrity. The answer can be found by leveraging the data catalog in the INFORMATION_SCHEMA.

Here's the query:

SELECT   CONCAT(tc.table_schema,'.',tc.table_name,'.',tc.constraint_name) AS "Constraint"
,        CONCAT(kcu.table_schema,'.',kcu.table_name,'.',kcu.column_name) AS "Foreign Key"
,        CONCAT(kcu.referenced_table_schema,'.',kcu.referenced_table_name,'.',kcu.referenced_column_name) AS "Primary Key"
FROM     information_schema.table_constraints tc JOIN information_schema.key_column_usage kcu
ON       tc.constraint_name = kcu.constraint_name
WHERE    tc.constraint_type = 'FOREIGN KEY'
ORDER BY tc.table_name
,        kcu.column_name;

It's a bit different then the USER_CONSTRAINTS and USER_CONS_COLUMNS views in Oracle, which are covered in this prior post.

I recently ran across another interesting detail on MySQL foreign keys creation and removal that has to do with the case sensitivity of constraints. If you create the constraint in lower case and then try to drop the foreign key constraint in upper case, you'll encounter the following error:

ERROR 1025 (HY000): Error on rename of '.\database_name\table_name' to '.\database_name\#sql2-79c-1' (errno: 152)

The reason appears to be that MySQL can't find the table with the constraint name, and therefore throws an error that appears related to failure writing the new structure. It can be misleading.

It’s the end of our Spring term, and yes occasionally somebody can’t sign on to their Oracle instance because their event log is full. They get the following message on Winodws:

C:\>sqlplus sys AS sysdba
 
SQL*Plus: Release 11.1.0.7.0 - Production ON Wed Jul 15 10:19:37 2009
 
Copyright (c) 1982, 2008, Oracle.  ALL rights reserved.
 
Enter password:
ERROR:
ORA-28056: Writing audit records TO Windows Event Log failed

The fix is simple, just delete your items from your Windows’ event log.

The fact that you could raise an ugly error when you query an external table always bothered me. I looked at Java stored procedures as the best solution initially. That was overkill. This afternoon, while writing about them for the new PL/SQL Workboook, it became clear. The fix is really easy.

If you know little to nothing about external tables, you can go read this earlier post. Likewise, if you don’t know about objects and object collection, you can refer to this post. Having provided you with the context, here’s an example that eliminates errors when querying an external table without an external file.

1. Create an external file, like this character table.

CREATE TABLE character
( character_id NUMBER
, first_name VARCHAR2(20)
, last_name VARCHAR2(20))
  ORGANIZATION EXTERNAL
  ( TYPE oracle_loader
    DEFAULT DIRECTORY download
    ACCESS PARAMETERS
    ( RECORDS DELIMITED BY NEWLINE CHARACTERSET US7ASCII
      BADFILE     'DOWNLOAD':'character.bad'
      DISCARDFILE 'DOWNLOAD':'character.dis'
      LOGFILE     'DOWNLOAD':'character.log'
      FIELDS TERMINATED BY ','
      OPTIONALLY ENCLOSED BY "'"
      MISSING FIELD VALUES ARE NULL )
    LOCATION ('character.csv'))
REJECT LIMIT UNLIMITED;

2. Create a user-defined object type that mirrors your external table defintion, like this:

CREATE OR REPLACE TYPE character_obj IS OBJECT
( character_id NUMBER
, first_name VARCHAR2(20)
, last_name VARCHAR2(20));
/

3. Create a user-defined collection of your object type, like

CREATE OR REPLACE TYPE character_obj_table IS TABLE OF character_obj;
/

4. Create a function that returns the user-defined collection of your object type, like

CREATE OR REPLACE FUNCTION character_source
RETURN character_obj_table IS
  c          NUMBER;
  collection CHARACTER_OBJ_TABLE := character_obj_table();
BEGIN
  FOR i IN (SELECT * FROM character) LOOP
    collection.EXTEND;
    collection(c) := character_obj( i.character_id
                                  , i.first_name
                                  , i.last_name);
    c := c + 1;
  END LOOP;
  RETURN collection;
EXCEPTION
  WHEN OTHERS THEN
    RETURN collection;
END;
/

5. Query the function not the table, which returns no rows found when the file doesn’t physically exist, or the file contains no data. Lastly, the function returns the data when it is there.

SELECT * FROM TABLE(character_source);

Hope this helps those using external tables to avoid the typical error stack:

SELECT * FROM character
*
ERROR at line 1:
ORA-29913: error IN executing ODCIEXTTABLEOPEN callout
ORA-29400: DATA cartridge error
KUP-04040: file character.csv IN CHARACTER NOT found

I also wrote this older post about confirming it in the database catalog. If you any follow-up suggestions, please let me know.

Never change something that works! Not really, but sometimes you feel that way. Especially, when you toast 5 to 10 minutes working through an undocumented behavior in PL/SQL. You’d think after writing it for 19 years, I’d have seen it all but not so.

I was working through a tried and true example from Chapter 14 of the Oracle Database 11g PL/SQL Programming book to prepare for teaching my class tomorrow, when I found this nice quirk. It took a few minutes to figure out what was happening, but here it is so you don’t have to look for it too. You can only use variable names that are attributes of the object type as formal parameters in object type constructors. If you try to vary it, you’ll trigger the following exception:

LINE/COL ERROR
-------- -----------------------------------------------------------------
4/11     PL/SQL: Item ignored
4/26     PLS-00307: too many declarations of 'HELLO_THERE' match this call
6/5      PL/SQL: Statement ignored
6/13     PLS-00320: the declaration of the type of this expression IS incomplete OR malformed

All I did to trigger this exception was change the who variable to make it scope specific, like iv_who for instance variable, pv_who for parameter variable, and lv_who for local variable.

1
2
3
4
5
6
7
8
9
10
11
12

CREATE OR REPLACE TYPE hello_there IS OBJECT
( iv_who VARCHAR2(20)
, CONSTRUCTOR FUNCTION hello_there
  RETURN SELF AS RESULT
, CONSTRUCTOR FUNCTION hello_there
  ( pv_who VARCHAR2 )
  RETURN SELF AS RESULT
, MEMBER FUNCTION get_who RETURN VARCHAR2
, MEMBER PROCEDURE set_who (pv_who VARCHAR2)
, MEMBER PROCEDURE to_string )
INSTANTIABLE NOT FINAL;
/

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33

CREATE OR REPLACE TYPE BODY hello_there IS
 
  CONSTRUCTOR FUNCTION hello_there RETURN SELF AS RESULT IS
    hello HELLO_THERE := hello_there('Generic Object.');
  BEGIN
    self := hello;
    RETURN;
  END hello_there;
 
  CONSTRUCTOR FUNCTION hello_there (pv_who VARCHAR2) RETURN SELF AS RESULT IS
 
  BEGIN
    self.iv_who := pv_who;
    RETURN;
  END hello_there;
 
  MEMBER FUNCTION get_who RETURN VARCHAR2 IS
  BEGIN
    RETURN self.iv_who;
  END get_who;
 
  MEMBER PROCEDURE set_who (pv_who VARCHAR2) IS
  BEGIN
    self.iv_who := pv_who;
  END set_who;
 
  MEMBER PROCEDURE to_string IS
  BEGIN
    dbms_output.put_line('Hello '||self.iv_who);
  END to_string;
 
END;
/

1
2
3
4
5
6
7
8
9
10
11
12

CREATE OR REPLACE TYPE hello_there IS OBJECT
( iv_who VARCHAR2(20)
, CONSTRUCTOR FUNCTION hello_there
  RETURN SELF AS RESULT
, CONSTRUCTOR FUNCTION hello_there
  ( iv_who VARCHAR2 )
  RETURN SELF AS RESULT
, MEMBER FUNCTION get_who RETURN VARCHAR2
, MEMBER PROCEDURE set_who (pv_who VARCHAR2)
, MEMBER PROCEDURE to_string )
INSTANTIABLE NOT FINAL;
/

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33

CREATE OR REPLACE TYPE BODY hello_there IS
 
  CONSTRUCTOR FUNCTION hello_there RETURN SELF AS RESULT IS
    hello HELLO_THERE := hello_there('Generic Object.');
  BEGIN
    self := hello;
    RETURN;
  END hello_there;
 
  CONSTRUCTOR FUNCTION hello_there (iv_who VARCHAR2) RETURN SELF AS RESULT IS
 
  BEGIN
    self.iv_who := iv_who;
    RETURN;
  END hello_there;
 
  MEMBER FUNCTION get_who RETURN VARCHAR2 IS
  BEGIN
    RETURN self.iv_who;
  END get_who;
 
  MEMBER PROCEDURE set_who (pv_who VARCHAR2) IS
  BEGIN
    self.iv_who := pv_who;
  END set_who;
 
  MEMBER PROCEDURE to_string IS
  BEGIN
    dbms_output.put_line('Hello '||self.iv_who);
  END to_string;
 
END;
/

This is certainly interesting. I’ve no clue why such a limitation exists. The name of a parameter list member in the constructor should be independent from the attribute of a user object.