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
AND      tc.constraint_schema = kcu.constraint_schema
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 may 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.

I always tell my students how to protect their Oracle credentials but hesitated to do the same for MySQL because it involves Windows batch shell scripting (albeit simple one line scripting). Oracle is nice and clean about hiding credentials. If you only provide the USER name at the command prompt, then your PASSWORD is never displayed. Only the user name is displayed in the DOS command prompt session’s window frame. You do that in Oracle with a student user name, as follows:

C:\Data\Oracle> sqlplus student
SQL*Plus: Release 10.2.0.1.0 - Production on Mon Sep 21 09:20:25 2009
Copyright (c) 1982, 2005, Oracle.  All rights reserved.
Enter password:

MySQL isn’t quite so convenient as a command-line tool. You must enter the -u or --user followed by the user name and -p or --password followed by the password when connecting to the database. For example, this shows a typical command prompt window frame:

As noted by Stephen in his comment, you can also mimic Oracle by using the following syntax:

C:\Data\MySQL> mysql -ustudent -p
Enter password:

Unless you use that syntax, anybody walking up can see your USER and PASSWORD in clear text. Not exactly the best security practice, eh? You can also suppress the USER name from displaying by writing a small batch file with your text editor or at the command-line, like this:

C:\Data\MySQL>COPY CON mysqlcmd.bat
@mysql -ustudent -p
^Z

While you’ll need to enter your password because you really don’t want to store that in a file. The @ symbol suppresses echo of the command, which means you won’t display your USER name or PASSWORD when you start the mysql client tool. It will only display the batch file name and the welcome message:

Hope this helps you when you’re working at the MySQL command-line. However, I’m often stunned by how few use it. They prefer phpMyAdmin or Quest’s Toad for MySQL.

A couple students stumbled on the MySQL on-line documentation today. Specifically, they didn’t find instructions on how a non-root user could change their password. I’ve also added this as an explanation on the MySQL documentation for 12.5.1.6. SET PASSWORD Syntax page.

You reset your own password when you’re not the root user with this syntax:

mysql> SET PASSWORD = PASSWORD('new_password');

This is simple syntax but carries a risk if you walk away from your terminal because somebody can run it without needing to know the original user password. Oracle requires that you know the original password if you use this syntax:

SQL> PASSWORD
Changing password FOR PLSQL
OLD password:
NEW password:
Retype NEW password:
Password changed

If you think that you’re safe walking away from an Oracle SQL*Plus console, check this because it doesn’t require knowing the current password either.

SQL> SHOW USER
USER IS "PLSQL"
SQL> ALTER USER plsql IDENTIFIED BY plsql;
USER altered.

Don’t walk away, always lock your terminal or use the QUIT command to exit MySQL. This generally prevents somebody hijacking your password. You can check this post if you’re interested in changing the root password when you don’t have it to begin with. Hope this helps a few folks.

Another faculty member asked me how to convert a DVD to an ISO on his Mac. Here are the instructions for everybody.

He needed to convert it because he wanted to deploy it on a Netbook from a USB drive. We all know you shouldn’t do this unless you’ve paid for the license and are only installing it on a single machine, like the Adobe license specifies.

The only tricky part to this is the last command-line step, which I borrowed from Mac OS X hints.

1. Open Disk Utility on your Mac OS X. It’ll look something like this:

2. Click the New Image icon in the tool bar, and save it as a compressed file. This writes it as a .dmg file, which is an Apple format .iso file.

It’ll take a while to write. The length of time is relevant to the size of the image on the DVD.

3. This is the first of two tricky steps. They’re tricky because you need to use the command-line. If you followed the instructions, you’ve saved the .dmg file on the Desktop. Now, you need to convert the .dmg formatted file to an .iso formatted file. You open Terminal, which is found in your Applications folder. Once launched follow the steps below. Change the directory to the Desktop or the folder where you put the file. You must do this before running this command because I’ve used relative file syntax (more or less only the file name). Alternatively, you could provide fully qualified file names.

MacPro:~ mclaughlinm$ cd Desktop
MacPro:Desktop mclaughlinm$ ls *.dmg
Acrobat9.dmg
MacPro:Desktop mclaughlinm$ hdiutil convert Acrobat9.dmg -format UDTO -o Acrobat9.iso
Reading Acrobat9                         (Apple_ISO : 0)…
...............................................................................................................
Elapsed Time: 27.655s
Speed: 22.2Mbytes/sec
Savings: 0.0%
created: /Users/mclaughlinm/Desktop/Acrobat9.iso.cdr

4. Before you copy it to your Windows USB, you need to remove the trailing .cdr from the file name. The syntax at the command-line is:

MacPro:Desktop mclaughlin$ mv Acrobat9.iso.cdr Acrobat9.iso

5. Open Finder, and copy the .iso to your USB drive, and delete the temporary copy.

Hope this helps some folks.

The longest outstanding question I’ve received is on how you can aggregate the number of rows in a nested table (one inside another). If you need a refresher on joining non-collection columns to nested columns, you should check this earlier blog post.

The basic premise is boils down to two points. One are you trying to get a count of the elements in the nested collection, or a count of the elements in the nested collection based on one or more columns in the nested table. There are two ways to perform a basic count of the nested line numbers, and one way to perform the other. You find out how to perform both in the post.

1. You can run the following script to create a sample data environment. It is re-runnable, and designed to run on either Oracle Database 10g or 11g.

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
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51

-- Conditionally delete objects in reverse dependency order.
BEGIN
  FOR i IN (SELECT   object_name
            ,        object_type
            FROM     user_objects
            WHERE    object_type IN ('TABLE','SEQUENCE','TYPE')
            AND      object_name IN ('TELEPHONE_TYPE','TELEPHONE_TABLE'
                                    ,'CONTACT_BOOK','CONTACT_BOOK_S1')
            ORDER BY object_type, object_name DESC) LOOP
    EXECUTE IMMEDIATE 'DROP '||i.object_type||' '||i.object_name;
  END LOOP;
END;
/
 
-- Create a composite data type (structure) and collection.
CREATE TYPE telephone_type AS OBJECT
( country_code   VARCHAR2(3)
, prefix_code    VARCHAR2(5)
, phone_number   VARCHAR2(9));
/
 
CREATE TYPE telephone_table AS TABLE OF telephone_type;
/
 
-- Create a table with a nested composite table and sequence.
CREATE TABLE contact_book
( contact_book_id   NUMBER
, first_name        VARCHAR2(30)
, last_name         VARCHAR2(30)
, telephone_numbers TELEPHONE_TABLE )
NESTED TABLE telephone_numbers STORE AS telephone_struct;
 
CREATE SEQUENCE contact_book_s1;
 
-- Insert two rows of data.
INSERT INTO contact_book
VALUES
( contact_book_s1.nextval
,'Harry','Potter'
,telephone_table(telephone_type('44','20','7299 2127')
                ,telephone_type('44','17','8926 8115')
                ,telephone_type('44','18','3344 5566')
                ,telephone_type('44','72','9878 5436')));
 
INSERT INTO contact_book
VALUES
( contact_book_s1.nextval
,'Weasley','Ronald'
,telephone_table(telephone_type('40','021','407 47 46')
                ,telephone_type('44','19','4088 1062')
                ,telephone_type('44','21','4075 5066')));

2. You can query the line numbers of the nested table by the scalar columns two ways. The first uses the CARDINALITY function introduced in Oracle 10g. The second leverages a CROSS JOIN and traditional aggregation tools.

2(a). Using the CARDINALITY function is generally the simplest and best approach to this problem:

1
2
3
4

SELECT first_name
,      last_name
,      CARDINALITY(telephone_numbers) list
FROM   contact_book;

This yield the following data set:

FIRST_NAME   LAST_NAME      LIST
------------ ------------ ------
Harry        Potter            4
Weasley      Ronald            3

2(b). Using the traditional CROSS JOIN and GROUP BY clause:

1
2
3
4
5
6
7

SELECT   cj.first_name
,        cj.last_name
,        COUNT(*)
FROM    (SELECT *
         FROM contact_book cb CROSS JOIN TABLE(cb.telephone_numbers)) cj
GROUP BY cj.first_name
,        cj.last_name;

This yield the following data set:

FIRST_NAME   LAST_NAME      LIST
------------ ------------ ------
Harry        Potter            4
Ronald       Weasley           3

3. If you wanted to know how many numbers where in the nested table by country code, you need to solve the problem through a CROSS JOIN and GROUP BY clause, like this:

1
2
3
4
5
6
7
8
9

SELECT   cj.first_name
,        cj.last_name
,        cj.country_code
,        COUNT(*)
FROM    (SELECT *
         FROM contact_book cb CROSS JOIN TABLE(cb.telephone_numbers)) cj
GROUP BY cj.first_name
,        cj.last_name
,        cj.country_code;

This yield the following data set:

FIRST_NAME   LAST_NAME    COUNTRY_CODE   LIST
------------ ------------ ------------ ------
Harry        Potter       44                4
Ronald       Weasley      44                2
Ronald       Weasley      40                1

If I captured the basics of the question, great. If there are more questions, please let me know.