//***************************************************************************
// (c) Copyright IBM Corp. 2007 All rights reserved.
// 
// The following sample of source code ("Sample") is owned by International 
// Business Machines Corporation or one of its subsidiaries ("IBM") and is 
// copyrighted and licensed, not sold. You may use, copy, modify, and 
// distribute the Sample in any form without payment to IBM, for the purpose of 
// assisting you in the development of your applications.
// 
// The Sample code is provided to you on an "AS IS" basis, without warranty of 
// any kind. IBM HEREBY EXPRESSLY DISCLAIMS ALL WARRANTIES, EITHER EXPRESS OR 
// IMPLIED, INCLUDING, BUT NOT LIMITED TO, THE IMPLIED WARRANTIES OF 
// MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE. Some jurisdictions do 
// not allow for the exclusion or limitation of implied warranties, so the above 
// limitations or exclusions may not apply to you. IBM shall not be liable for 
// any damages you suffer as a result of using, copying, modifying or 
// distributing the Sample, even if IBM has been advised of the possibility of 
// such damages.
//***************************************************************************
//
// SOURCE FILE NAME: TbConstr.java
//
// SAMPLE: How to create, use and drop constraints
//
// SQL Statements USED:
//         CREATE TABLE
//         DROP TABLE
//         DELETE
//         COMMIT
//         ROLLBACK
//         INSERT
//         ALTER
//
// JAVA 2 CLASSES USED:
//         Statement
//         ResultSet
//
// Classes used from Util.java are:
//         Db
//         JdbcException
//
//                           
// Output will vary depending on the JDBC driver connectivity used.
//***************************************************************************
//
// For more information on the sample programs, see the README file.
//
// For information on developing Java applications see the Developing Java Applications book.
//
// For information on using SQL statements, see the SQL Reference.
//
// For the latest information on programming, compiling, and running DB2
// applications, visit the DB2 Information Center at
//     http://publib.boulder.ibm.com/infocenter/db2luw/v9r7/index.jsp
//**************************************************************************/

import java.lang.*;
import java.sql.*;

class TbConstr
{
  public static void main(String argv[])
  {
    try
    {
      Db db = new Db(argv);

      System.out.println();
      System.out.println(
        "THIS SAMPLE SHOWS HOW TO CREATE, USE AND DROP CONSTRAINTS.\n");

      // connect to the 'sample' database
      db.connect();

      demo_NOT_NULL(db.con);
      demo_UNIQUE(db.con);
      demo_PRIMARY_KEY(db.con);
      demo_CHECK(db.con);
      demo_CHECK_INFO(db.con);
      demo_WITH_DEFAULT(db.con);

      System.out.println();
      System.out.println(
      "----------------------------------------------------------\n" +
        "#####################################################\n" +
        "#    Create tables for FOREIGN KEY sample functions #\n" +
        "#####################################################");

      FK_TwoTablesCreate(db.con);

      demo_FK_OnInsertShow(db.con);
      demo_FK_ON_UPDATE_NO_ACTION(db.con);
      demo_FK_ON_UPDATE_RESTRICT(db.con);
      demo_FK_ON_DELETE_CASCADE(db.con);
      demo_FK_ON_DELETE_SET_NULL(db.con);
      demo_FK_ON_DELETE_NO_ACTION(db.con);

      System.out.println();
      System.out.println(
      "----------------------------------------------------------\n" +
        "########################################################\n" +
        "# Drop tables created for FOREIGN KEY sample functions #\n" +
        "########################################################");
      FK_TwoTablesDrop(db.con);

      // disconnect from the 'sample' database
      db.disconnect();
    }
    catch (Exception e)
    {
      JdbcException jdbcExc = new JdbcException(e);
      jdbcExc.handle();
    }
  } // main

  // helping function: This function creates two foreign keys
  static void FK_TwoTablesCreate(Connection con)
  {
    try
    {
      System.out.println();
      System.out.println(
        "  CREATE TABLE deptmt(deptno CHAR(3) NOT NULL,\n" +
        "                    deptname VARCHAR(20),\n" +
        "                    CONSTRAINT pk_dept\n" +
        "                    PRIMARY KEY(deptno))");

      Statement stmt = con.createStatement();
      stmt.executeUpdate(
        "CREATE TABLE deptmt(deptno CHAR(3) NOT NULL, " +
        "                  deptname VARCHAR(20), " +
        "                  CONSTRAINT pk_dept " +
        "                  PRIMARY KEY(deptno))");
      stmt.close();

      System.out.println();
      System.out.println(
        "  INSERT INTO deptmt VALUES('A00', 'ADMINISTRATION'),\n" +
        "                         ('B00', 'DEVELOPMENT'),\n" +
        "                         ('C00', 'SUPPORT')");

      Statement stmt1 = con.createStatement();
      stmt1.executeUpdate(
        "INSERT INTO deptmt VALUES('A00', 'ADMINISTRATION'), " +
        "                       ('B00', 'DEVELOPMENT'), " +
        "                       ('C00', 'SUPPORT') ");
      stmt1.close();

      System.out.println();
      System.out.println(
        "  CREATE TABLE empl(empno CHAR(4),\n" +
        "                   empname VARCHAR(10),\n" +
        "                   dept_no CHAR(3))");

      Statement stmt2 = con.createStatement();
      stmt2.executeUpdate("CREATE TABLE empl(empno CHAR(4), " +
                          "                 empname VARCHAR(10), " +
                          "                 dept_no CHAR(3))");
      stmt2.close();

      System.out.println();
      System.out.println(
        "  INSERT INTO empl VALUES('0010', 'Smith', 'A00'),\n" +
        "                        ('0020', 'Ngan', 'B00'),\n" +
        "                        ('0030', 'Lu', 'B00'),\n" +
        "                        ('0040', 'Wheeler', 'B00'),\n" +
        "                        ('0050', 'Burke', 'C00'),\n" +
        "                        ('0060', 'Edwards', 'C00'),\n" +
        "                        ('0070', 'Lea', 'C00')");

      Statement stmt3 = con.createStatement();
      stmt3.executeUpdate(
        "INSERT INTO empl VALUES('0010', 'Smith', 'A00'), " +
        "                      ('0020', 'Ngan', 'B00'), " +
        "                      ('0030', 'Lu', 'B00'), " +
        "                      ('0040', 'Wheeler', 'B00'), " +
        "                      ('0050', 'Burke', 'C00'), " +
        "                      ('0060', 'Edwards', 'C00'), " +
        "                      ('0070', 'Lea', 'C00')  ");
      stmt3.close();

      System.out.println("\n  COMMIT");
      con.commit();
    }
    catch (Exception e)
    {
      JdbcException jdbcExc = new JdbcException(e, con);
      jdbcExc.handle();
    }
  } // FK_TwoTablesCreate

  // helping function
  static void FK_TwoTablesDisplay(Connection con)
  {
    try
    {
      System.out.println();
      System.out.println("  SELECT * FROM deptmt");
      System.out.println("    DEPTNO  DEPTNAME\n" +
                         "    ------- --------------");

      Statement stmt = con.createStatement();
      ResultSet rs = stmt.executeQuery("SELECT * FROM deptmt");

      while (rs.next())
      {
        System.out.println("    " +
                           Data.format(rs.getString("deptno"),7) + " " +
                           Data.format(rs.getString("deptname"),20));
      }
      rs.close();
      stmt.close();

      System.out.println();
      System.out.println("  SELECT * FROM empl");
      System.out.println("    EMPNO EMPNAME    DEPT_NO\n" +
                         "    ----- ---------- -------");

      Statement stmt1 = con.createStatement();
      ResultSet rs1 = stmt1.executeQuery("SELECT * FROM empl");

      while (rs1.next())
      {
        System.out.print("    " +
                          Data.format(rs1.getString("empno"),5) + " " +
                          Data.format(rs1.getString("empname"),10));
        String deptNo = rs1.getString("dept_no");
        if (deptNo !=null)
        {
          System.out.print(" " + Data.format(deptNo,3));
        }
        else
        {
          System.out.print(" -");
        }
        System.out.println();
      }
      rs1.close();
      stmt1.close();
    }
    catch (Exception e)
    {
      JdbcException jdbcExc = new JdbcException(e, con);
      jdbcExc.handle();
    }
  } // FK_TwoTablesDisplay

  // helping function
  static void FK_TwoTablesDrop(Connection con)
  {
    try
    {
      System.out.println();
      System.out.println("  DROP TABLE deptmt");
      Statement stmt = con.createStatement();
      stmt.executeUpdate("DROP TABLE deptmt");
      stmt.close();

      System.out.println();
      System.out.println("  DROP TABLE empl");
      Statement stmt1 = con.createStatement();
      stmt1.executeUpdate("DROP TABLE empl");
      stmt1.close();

      System.out.println("\n  COMMIT");
      con.commit();
    }
    catch (Exception e)
    {
      JdbcException jdbcExc = new JdbcException(e, con);
      jdbcExc.handle();
    }
  } // FK_TwoTablesDrop


  // helping function
  static void FK_Create(String ruleClause, Connection con)
  {
    try
    {
      System.out.println();
      System.out.println("  ALTER TABLE empl\n" +
                         "    ADD CONSTRAINT fk_dept\n" +
                         "    FOREIGN KEY(dept_no)\n" +
                         "    REFERENCES deptmt(deptno)\n" +
                         "    " + ruleClause);

      Statement stmt = con.createStatement();
      stmt.executeUpdate("ALTER TABLE empl " +
                         "  ADD CONSTRAINT fk_dept " +
                         "  FOREIGN KEY(dept_no) " +
                         "  REFERENCES deptmt(deptno) " +
                         ruleClause);
      stmt.close();

      System.out.println();
      System.out.println("  COMMIT");
      con.commit();
    }
    catch (Exception e)
    {
      JdbcException jdbcExc = new JdbcException(e, con);
      jdbcExc.handle();
    }
  } // FK_Create


  // helping function
  static void FK_Drop(Connection con)
  {
    try
    {
      System.out.println();
      System.out.println("  ALTER TABLE empl DROP CONSTRAINT fk_dept");

      Statement stmt = con.createStatement();
      stmt.executeUpdate("ALTER TABLE empl DROP CONSTRAINT fk_dept");
      stmt.close();

      System.out.println();
      System.out.println("  COMMIT");
      con.commit();
    }
    catch (Exception e)
    {
      JdbcException jdbcExc = new JdbcException(e, con);
      jdbcExc.handle();
    }
  } // FK_Drop

  // This function demonstrates how to use a 'NOT NULL' constraint.
  static void demo_NOT_NULL(Connection con)
  {
    System.out.println();
    System.out.println(
      "----------------------------------------------------------\n" +
      "USE THE SQL STATEMENTS:\n" +
      "  CREATE TABLE\n" +
      "  COMMIT\n" +
      "  INSERT\n" +
      "  DROP TABLE\n" +
      "TO SHOW A 'NOT NULL' CONSTRAINT.");

    // Create a table called empl_sal with a 'NOT NULL' constraint
    try
    {
      System.out.println();
      System.out.println(
        "  CREATE TABLE empl_sal(lastname VARCHAR(10) NOT NULL,\n" +
        "                       firstname VARCHAR(10),\n" +
        "                       salary DECIMAL(7, 2))");

      Statement stmt = con.createStatement();
      stmt.executeUpdate(
        "CREATE TABLE empl_sal(lastname VARCHAR(10) NOT NULL, " +
        "                     firstname VARCHAR(10), " +
        "                     salary DECIMAL(7, 2))");
      stmt.close();

      System.out.println();
      System.out.println("  COMMIT");
      con.commit();
    }
    catch (Exception e)
    {
      JdbcException jdbcExc = new JdbcException(e, con);
      jdbcExc.handle();
    }

    // Insert a row in the table empl_sal with NULL as the lastname.
    // This insert will fail with an expected error.
    try
    {
      String strStmt;
      System.out.println();
      System.out.println(
        "  INSERT INTO empl_sal VALUES(NULL, 'PHILIP', 17000.00)");

      Statement stmt1 = con.createStatement();
      stmt1.executeUpdate(
        "INSERT INTO empl_sal VALUES(NULL, 'PHILIP', 17000.00) ");
      stmt1.close();
    }
    catch (Exception e)
    {
      JdbcException jdbcExc = new JdbcException(e, con);
      jdbcExc.handleExpectedErr();
    }

    // drop the table empl_sal
    try
    {
      System.out.println();
      System.out.println("  DROP TABLE empl_sal");
      Statement stmt2 = con.createStatement();
      stmt2.executeUpdate("DROP TABLE empl_sal");
      stmt2.close();
    }
    catch (Exception e)
    {
      JdbcException jdbcExc = new JdbcException(e, con);
      jdbcExc.handle();
    }
  } // demo_NOT_NULL

  // This function demonstrates how to use a 'UNIQUE' constraint.
  static void demo_UNIQUE(Connection con)
  {
    System.out.println();
    System.out.println(
      "----------------------------------------------------------\n" +
      "USE THE SQL STATEMENTS:\n" +
      "  CREATE TABLE\n" +
      "  COMMIT\n" +
      "  INSERT\n" +
      "  ALTER TABLE\n" +
      "  DROP TABLE\n" +
      "TO SHOW A 'UNIQUE' CONSTRAINT.");

    // Create a table called empl_sal with a 'UNIQUE' constraint
    try
    {
      System.out.println();
      System.out.println(
        "  CREATE TABLE empl_sal(lastname VARCHAR(10) NOT NULL,\n" +
        "                       firstname VARCHAR(10) NOT NULL,\n" +
        "                       salary DECIMAL(7, 2),\n" +
        "                       CONSTRAINT unique_cn\n" +
        "                       UNIQUE(lastname, firstname))");

      Statement stmt = con.createStatement();
      stmt.executeUpdate(
        "CREATE TABLE empl_sal(lastname VARCHAR(10) NOT NULL, " +
        "                     firstname VARCHAR(10) NOT NULL, " +
        "                     salary DECIMAL(7, 2), " +
        "                     CONSTRAINT unique_cn " +
        "                     UNIQUE(lastname, firstname))");
      stmt.close();

      System.out.println();
      System.out.println("  COMMIT");
      con.commit();
    }
    catch (Exception e)
    {
      JdbcException jdbcExc = new JdbcException(e, con);
      jdbcExc.handle();
    }

    // Insert two rows into the table empl_sal that have the same lastname
    // and firstname values. The insert will fail with an expected error
    // because the rows violate the PRIMARY KEY constraint.
    try
    {
      System.out.println();
      System.out.println(
        "  INSERT INTO empl_sal VALUES('SMITH', 'PHILIP', 17000.00),\n" +
        "                            ('SMITH', 'PHILIP', 21000.00)");

      Statement stmt1 = con.createStatement();
      stmt1.executeUpdate(
        "INSERT INTO empl_sal VALUES('SMITH', 'PHILIP', 17000.00), " +
        "                          ('SMITH', 'PHILIP', 21000.00)  ");
      stmt1.close();
    }
    catch (Exception e)
    {
      JdbcException jdbcExc = new JdbcException(e, con);
      jdbcExc.handleExpectedErr();
    }

    // drop the 'UNIQUE' constraint on the table empl_sal
    try
    {
      System.out.println();
      System.out.println(
        "  ALTER TABLE empl_sal DROP CONSTRAINT unique_cn");

      Statement stmt2 = con.createStatement();
      stmt2.executeUpdate(
        "ALTER TABLE empl_sal DROP CONSTRAINT unique_cn ");
      stmt2.close();
    }
    catch (Exception e)
    {
      JdbcException jdbcExc = new JdbcException(e, con);
      jdbcExc.handle();
    }

    // drop the table empl_sal
    try
    {
      System.out.println();
      System.out.println("  DROP TABLE empl_sal");
      Statement stmt3 = con.createStatement();
      stmt3.executeUpdate("DROP TABLE empl_sal");
      stmt3.close();
    }
    catch (Exception e)
    {
      JdbcException jdbcExc = new JdbcException(e, con);
      jdbcExc.handle();
    }
  } // demo_UNIQUE

  // This function demonstrates how to use a 'PRIMARY KEY' constraint.
  static void demo_PRIMARY_KEY(Connection con)
  {
    System.out.println();
    System.out.println(
      "----------------------------------------------------------\n" +
      "USE THE SQL STATEMENTS:\n" +
      "  CREATE TABLE\n" +
      "  COMMIT\n" +
      "  INSERT\n" +
      "  ALTER TABLE\n" +
      "  DROP TABLE\n" +
      "TO SHOW A 'PRIMARY KEY' CONSTRAINT.");

    // Create a table called empl_sal with a 'PRIMARY KEY' constraint
    try
    {
      System.out.println();
      System.out.println(
        "  CREATE TABLE empl_sal(lastname VARCHAR(10) NOT NULL,\n" +
        "                       firstname VARCHAR(10) NOT NULL,\n" +
        "                       salary DECIMAL(7, 2),\n" +
        "                       CONSTRAINT pk_cn\n" +
        "                       PRIMARY KEY(lastname, firstname))");

      Statement stmt = con.createStatement();
      stmt.executeUpdate(
        "CREATE TABLE empl_sal(lastname VARCHAR(10) NOT NULL, " +
        "                     firstname VARCHAR(10) NOT NULL, " +
        "                     salary DECIMAL(7, 2), " +
        "                     CONSTRAINT pk_cn " +
        "                     PRIMARY KEY(lastname, firstname))");
      stmt.close();

      System.out.println();
      System.out.println("  COMMIT");
      con.commit();
    }
    catch (Exception e)
    {
      JdbcException jdbcExc = new JdbcException(e, con);
      jdbcExc.handle();
    }

    // Insert two rows into the table empl_sal that have the same lastname
    // and firstname values. The insert will fail with an expected error
    // because the rows violate the PRIMARY KEY constraint.
    try
    {
      System.out.println();
      System.out.println(
        "  INSERT INTO empl_sal VALUES('SMITH', 'PHILIP', 17000.00),\n" +
        "                            ('SMITH', 'PHILIP', 21000.00)");

      Statement stmt1 = con.createStatement();
      stmt1.executeUpdate(
        "INSERT INTO empl_sal VALUES('SMITH', 'PHILIP', 17000.00)," +
        "                          ('SMITH', 'PHILIP', 21000.00) ");
      stmt1.close();
    }
    catch (Exception e)
    {
      JdbcException jdbcExc = new JdbcException(e, con);
      jdbcExc.handleExpectedErr();
    }

    // drop the 'PRIMARY KEY' constraint on the table empl_sal
    try
    {
      System.out.println();
      System.out.println("  ALTER TABLE empl_sal DROP CONSTRAINT pk_cn");

      Statement stmt2 = con.createStatement();
      stmt2.executeUpdate("ALTER TABLE empl_sal DROP CONSTRAINT pk_cn");
      stmt2.close();
    }
    catch (Exception e)
    {
      JdbcException jdbcExc = new JdbcException(e, con);
      jdbcExc.handle();
    }

    // drop the table empl_sal
    try
    {
      System.out.println();
      System.out.println("  DROP TABLE empl_sal");

      Statement stmt3 = con.createStatement();
      stmt3.executeUpdate("DROP TABLE empl_sal");
      stmt3.close();
    }
    catch (Exception e)
    {
      JdbcException jdbcExc = new JdbcException(e, con);
      jdbcExc.handle();
    }
  } // demo_PRIMARY_KEY

  // This function demonstrates how to use a 'CHECK' constraint.
  static void demo_CHECK(Connection con)
  {
    System.out.println();
    System.out.println(
      "----------------------------------------------------------\n" +
      "USE THE SQL STATEMENTS:\n" +
      "  CREATE TABLE\n" +
      "  COMMIT\n" +
      "  INSERT\n" +
      "  ALTER TABLE\n" +
      "  DROP TABLE\n" +
      "TO SHOW A 'CHECK' CONSTRAINT.");

    // Create a table called empl_sal with a 'CHECK' constraint
    try
    {
      System.out.println();
      System.out.println(
        "  CREATE TABLE empl_sal(lastname VARCHAR(10),\n" +
        "                       firstname VARCHAR(10),\n" +
        "                       salary DECIMAL(7, 2),\n" +
        "                       CONSTRAINT check_cn\n" +
        "                       CHECK(salary < 25000.00))");

      Statement stmt = con.createStatement();
      stmt.executeUpdate(
        "CREATE TABLE empl_sal(lastname VARCHAR(10), " +
        "                     firstname VARCHAR(10), " +
        "                     salary DECIMAL(7, 2), " +
        "                     CONSTRAINT check_cn " +
        "                     CHECK(salary < 25000.00))");
      stmt.close();

      System.out.println();
      System.out.println("  COMMIT");
      con.commit();
    }
    catch (Exception e)
    {
      JdbcException jdbcExc = new JdbcException(e, con);
      jdbcExc.handle();
    }

    // Insert a row in the table empl_sal that violates the rule defined
    // in the 'CHECK' constraint. This insert will fail with an expected
    // error.
    try
    {
      System.out.println();
      System.out.println(
        "  INSERT INTO empl_sal VALUES('SMITH', 'PHILIP', 27000.00)");

      Statement stmt1 = con.createStatement();
      stmt1.execute(
        "INSERT INTO empl_sal VALUES('SMITH', 'PHILIP', 27000.00)");
      stmt1.close();
    }
    catch (Exception e)
    {
      JdbcException jdbcExc = new JdbcException(e, con);
      jdbcExc.handleExpectedErr();
    }

    // drop the 'CHECK' constraint on the table empl_sal
    try
    {
      System.out.println();
      System.out.println("  ALTER TABLE empl_sal DROP CONSTRAINT check_cn");

      Statement stmt2 = con.createStatement();
      stmt2.executeUpdate(
        "ALTER TABLE empl_sal DROP CONSTRAINT check_cn");
      stmt2.close();
    }
    catch (Exception e)
    {
      JdbcException jdbcExc = new JdbcException(e, con);
      jdbcExc.handle();
    }

    // drop the table empl_sal
    try
    {
      System.out.println();
      System.out.println("  DROP TABLE empl_sal");
      Statement stmt3 = con.createStatement();
      stmt3.executeUpdate("DROP TABLE empl_sal");
      stmt3.close();
    }
    catch (Exception e)
    {
      JdbcException jdbcExc = new JdbcException(e, con);
      jdbcExc.handle();
    }
  } // demo_CHECK

  // This function demonstrates how to use an 'INFORMATIONAL' constraint.
  static void demo_CHECK_INFO(Connection con)
  {
    Statement stmt = null;
    try
    {
      stmt = con.createStatement();
      
      System.out.println(
        "----------------------------------------------------------\n" +
        "USE THE SQL STATEMENTS:\n" +
        "  CREATE TABLE\n" +
        "  COMMIT\n" +
        "  INSERT\n" +
        "  ALTER TABLE\n" +
        "  DROP TABLE\n" +
        "TO SHOW AN 'INFORMATIONAL' CONSTRAINT.");

      // create a table called empl with a 'CHECK' constraint
      System.out.println(
        "\n  CREATE TABLE empl(empno INTEGER NOT NULL PRIMARY KEY,\n" +
        "                   name VARCHAR(10),\n" +
        "                   firstname VARCHAR(20),\n" +
        "                   salary INTEGER CONSTRAINT minsalary\n" +
        "                          CHECK (salary >= 25000)\n" +
        "                          NOT ENFORCED\n" +
        "                          ENABLE QUERY OPTIMIZATION)\n");
      stmt.executeUpdate(
        "CREATE TABLE empl(empno INTEGER NOT NULL PRIMARY KEY," +
        "                   name VARCHAR(10)," +
        "                   firstname VARCHAR(20)," +
        "                   salary INTEGER CONSTRAINT minsalary" +
        "                          CHECK (salary >= 25000)" +
        "                          NOT ENFORCED" +
        "                          ENABLE QUERY OPTIMIZATION)");

      System.out.println("  COMMIT");
      con.commit();
    }
    catch (Exception e)
    {
      JdbcException jdbcExc = new JdbcException(e, con);
      jdbcExc.handle();
    }
    
    try
    {
      // insert data that doesn't satisfy the constraint 'minsalary'. 
      // database manager does not enforce the constraint for IUD operations 
      System.out.println(
        "\n\nTO SHOW NOT ENFORCED OPTION\n" +
        "\n  INSERT INTO empl VALUES(1, 'SMITH', 'PHILIP', 1000)\n");
            
      stmt.executeUpdate(
        "INSERT INTO empl VALUES(1, 'SMITH', 'PHILIP', 1000)");
      con.commit();
    }
    catch (Exception e)
    {
      JdbcException jdbcExc = new JdbcException(e, con);
      jdbcExc.handle();
    }
    
    try
    {
      // alter the constraint to make it ENFORCED by database manager
      System.out.println(
        "Alter the constraint to make it ENFORCED by database manager\n" +
        "\n  ALTER TABLE empl ALTER CHECK minsalary ENFORCED");
      
      stmt.executeUpdate(
        "ALTER TABLE empl ALTER CHECK minsalary ENFORCED");
      con.commit();
    }
    catch (Exception e)
    {
      JdbcException jdbcExc = new JdbcException(e, con);
      jdbcExc.handleExpectedErr();
    }
    
    try
    {
      // delete entries from EMP Table
      System.out.println("\n  DELETE FROM empl");
      
      stmt.executeUpdate("DELETE FROM empl");
      con.commit();
    }
    catch (Exception e)
    {
      JdbcException jdbcExc = new JdbcException(e, con);
      jdbcExc.handle();
    }
    
    try
    {
      // alter the constraint to make it ENFORCED by database manager
      System.out.println(
        "\n\nTO SHOW ENFORCED OPTION\n" +
        "\n  ALTER TABLE empl ALTER CHECK minsalary ENFORCED\n ");
      
      stmt.executeUpdate("ALTER TABLE empl ALTER CHECK minsalary ENFORCED");
      con.commit();
    }
    catch (Exception e)
    {
      JdbcException jdbcExc = new JdbcException(e, con);                    
      jdbcExc.handle();
    }
    
    try
    {
      // insert table with data not conforming to the constraint 'minsalary'
      // database manager enforces the constraint for IUD operations
      System.out.println(
        "  INSERT INTO empl VALUES(1, 'SMITH', 'PHILIP', 1000)");
      
      stmt.executeUpdate(
        "INSERT INTO empl VALUES(1, 'SMITH', 'PHILIP', 1000)");
      con.commit();
    }
    catch (Exception e)
    {
      JdbcException jdbcExc = new JdbcException(e, con);
      jdbcExc.handleExpectedErr();
    }
    
    try
    {
      // drop table
      System.out.println("\n  DROP TABLE empl");
      
      stmt.executeUpdate("DROP TABLE empl");
      con.commit();
      stmt.close();
    }
    catch (Exception e)
    {
      JdbcException jdbcExc = new JdbcException(e, con);
      jdbcExc.handle();
    }
  } // demo_CHECK_INFO

  // This function demonstrates how to use a 'WITH DEFAULT' constraint.
  static void demo_WITH_DEFAULT(Connection con)
  {
    System.out.println();
    System.out.println(
      "----------------------------------------------------------\n" +
      "USE THE SQL STATEMENTS:\n" +
      "  CREATE TABLE\n" +
      "  COMMIT\n" +
      "  INSERT\n" +
      "  DROP TABLE\n" +
      "TO SHOW A 'WITH DEFAULT' CONSTRAINT.");

    // Create a table called empl_sal with a 'WITH DEFAULT' constraint
    try
    {
      System.out.println();
      System.out.println(
        "  CREATE TABLE empl_sal(lastname VARCHAR(10),\n" +
        "                       firstname VARCHAR(10),\n" +
        "                       salary DECIMAL(7, 2) " +
        "WITH DEFAULT 17000.00)");

      Statement stmt = con.createStatement();
      stmt.executeUpdate(
        "CREATE TABLE empl_sal(lastname VARCHAR(10), " +
        "                     firstname VARCHAR(10), " +
        "                     salary DECIMAL(7, 2) WITH DEFAULT 17000.00)");
      stmt.close();

      System.out.println();
      System.out.println("  COMMIT");
      con.commit();
    }
    catch (Exception e)
    {
      JdbcException jdbcExc = new JdbcException(e, con);
      jdbcExc.handle();
    }

    // Insert three rows into the table empl_sal, without any value for the
    // the third column. Since the third column is defined with a default
    // value of 17000.00, the third column for each of these three rows
    // will be set to 17000.00.
    try
    {
      String strStmt;
      System.out.println();
      System.out.println("  INSERT INTO empl_sal(lastname, firstname)\n" +
                         "    VALUES('SMITH', 'PHILIP'),\n" +
                         "          ('PARKER', 'JOHN'),\n" +
                         "          ('PEREZ', 'MARIA')");

      Statement stmt1 = con.createStatement();
      stmt1.executeUpdate("INSERT INTO empl_sal(lastname, firstname) " +
                          "  VALUES('SMITH' , 'PHILIP'), " +
                          "        ('PARKER', 'JOHN'), " +
                          "        ('PEREZ' , 'MARIA') ");
      stmt1.close();
    }
    catch (Exception e)
    {
      JdbcException jdbcExc = new JdbcException(e, con);
      jdbcExc.handle();
    }

    // retrieve and display the data in the table empl_sal
    try
    {
      String strStmt;
      System.out.println();
      System.out.println("  SELECT * FROM empl_sal");
      System.out.println("    FIRSTNAME  LASTNAME   SALARY\n" +
                         "    ---------- ---------- --------");

      Statement stmt2 = con.createStatement();
      ResultSet rs = stmt2.executeQuery("SELECT * FROM empl_sal");

      while (rs.next())
      {
        System.out.println("    " +
          Data.format(rs.getString("firstname"),10) + " " +
          Data.format(rs.getString("lastname"),10) + " " +
          Data.format(rs.getDouble("salary"),7,2));
      }
      rs.close();
      stmt2.close();
    }
    catch (Exception e)
    {
      JdbcException jdbcExc = new JdbcException(e, con);
      jdbcExc.handle();
    }

    // drop the table empl_sal
    try
    {
      System.out.println();
      System.out.println("  DROP TABLE empl_sal");
      Statement stmt3 = con.createStatement();
      stmt3.executeUpdate("DROP TABLE empl_sal");
      stmt3.close();
    }
    catch (Exception e)
    {
      JdbcException jdbcExc = new JdbcException(e, con);
      jdbcExc.handle();
    }
  } // demo_WITH_DEFAULT

  // This function demonstrates how to insert into a foreign key
  static void demo_FK_OnInsertShow(Connection con)
  {
    System.out.println();
    System.out.println(
      "----------------------------------------------------------\n" +
      "USE THE SQL STATEMENTS:\n" +
      "  ALTER TABLE\n" +
      "  COMMIT\n" +
      "  INSERT\n" +
      "  ROLLBACK\n" +
      "TO SHOW HOW A FOREIGN KEY WORKS ON INSERT.");

    // display the initial content of the 'deptmt' and 'empl' table
    FK_TwoTablesDisplay(con);

    // create a foreign key on the 'empl' table that reference the 'deptmt'
    // table
    FK_Create("", con);

    // insert an entry into the parent table, 'deptmt'
    try
    {
      System.out.println();
      System.out.println("  INSERT INTO deptmt VALUES('D00', 'SALES')");

      Statement stmt = con.createStatement();
      stmt.executeUpdate("INSERT INTO deptmt VALUES('D00', 'SALES')");
      stmt.close();
    }
    catch (Exception e)
    {
      JdbcException jdbcExc = new JdbcException(e, con);
      jdbcExc.handle();
    }

    // insert an entry into the child table, 'empl'
    try
    {
      System.out.println();
      System.out.println(
        "  INSERT INTO empl VALUES('0080', 'Pearce', 'E03')");

      Statement stmt1 = con.createStatement();
      stmt1.executeUpdate(
        "INSERT INTO empl VALUES('0080', 'Pearce', 'E03')");
      stmt1.close();
    }
    catch (Exception e)
    {
      JdbcException jdbcExc = new JdbcException(e, con);
      jdbcExc.handleExpectedErr();
    }

    // display the final content of the 'deptmt' and 'empl' table
    FK_TwoTablesDisplay(con);

    // roll back the transaction
    try
    {
      System.out.println();
      System.out.println("  ROLLBACK");
      con.rollback();
    }
    catch (Exception e)
    {
      JdbcException jdbcExc = new JdbcException(e);
      jdbcExc.handle();
    }

    // drop the foreign key
    FK_Drop(con);
  } // demo_FK_OnInsertShow

  // This function demonstrates how to use an 'ON UPDATE NO ACTION'
  // foreign key
  static void demo_FK_ON_UPDATE_NO_ACTION(Connection con)
  {
    System.out.println();
    System.out.println(
      "----------------------------------------------------------\n" +
      "USE THE SQL STATEMENTS:\n" +
      "  ALTER TABLE\n" +
      "  COMMIT\n" +
      "  UPDATE\n" +
      "  ROLLBACK\n" +
      "TO SHOW AN 'ON UPDATE NO ACTION' FOREIGN KEY.");

    // display the initial content of the 'deptmt' and 'empl' table
    FK_TwoTablesDisplay(con);

    // create an 'ON UPDATE NO ACTION' foreign key
    FK_Create("ON UPDATE NO ACTION", con);

    // update parent table
    try
    {
      System.out.println();
      System.out.println(
        "  UPDATE deptmt SET deptno = 'E01' WHERE deptno = 'A00'");

      Statement stmt = con.createStatement();
      stmt.executeUpdate(
        "UPDATE deptmt SET deptno = 'E01' WHERE deptno = 'A00' ");
      stmt.close();
    }
    catch (Exception e)
    {
      JdbcException jdbcExc = new JdbcException(e, con);
      jdbcExc.handleExpectedErr();
    }

    // update the parent table, 'deptmt'
    try
    {
      System.out.println();
      System.out.println(
        "  UPDATE deptmt\n" +
        "    SET deptno = CASE\n" +
        "                   WHEN deptno = 'A00' THEN 'B00'\n" +
        "                   WHEN deptno = 'B00' THEN 'A00'\n" +
        "                 END\n" +
        "    WHERE deptno = 'A00' OR deptno = 'B00'");

      Statement stmt1 = con.createStatement();
      stmt1.executeUpdate(
        "UPDATE deptmt " +
        "  SET deptno = CASE " +
        "                 WHEN deptno = 'A00' THEN 'B00' " +
        "                 WHEN deptno = 'B00' THEN 'A00' " +
        "               END " +
        "  WHERE deptno = 'A00' OR deptno = 'B00' ");
      stmt1.close();
    }
    catch (Exception e)
    {
      JdbcException jdbcExc = new JdbcException(e, con);
      jdbcExc.handle();
    }

    // update the child table, 'empl'
    try
    {
      System.out.println();
      System.out.println(
        "  UPDATE empl SET dept_no = 'G11' WHERE empname = 'Wheeler'");

      Statement stmt2 = con.createStatement();
      stmt2.executeUpdate(
        "UPDATE empl SET dept_no = 'G11' WHERE empname = 'Wheeler' ");
      stmt2.close();
    }
    catch (Exception e)
    {
      JdbcException jdbcExc = new JdbcException(e, con);
      jdbcExc.handleExpectedErr();
    }

    // display the final content of the 'deptmt' and 'empl' table
    FK_TwoTablesDisplay(con);

    // roll back the transaction
    try
    {
      System.out.println();
      System.out.println("  ROLLBACK");
      con.rollback();
    }
    catch (Exception e)
    {
      JdbcException jdbcExc = new JdbcException(e);
      jdbcExc.handle();
    }

    // drop the foreign key
    FK_Drop(con);
  } // demo_FK_ON_UPDATE_NO_ACTION

  // This function demonstrates how to use an 'ON UPDATE RESTRICT'
  // foreign key
  static void demo_FK_ON_UPDATE_RESTRICT(Connection con)
  {
    System.out.println();
    System.out.println(
      "----------------------------------------------------------\n" +
      "USE THE SQL STATEMENTS:\n" +
      "  ALTER TABLE\n" +
      "  COMMIT\n" +
      "  UPDATE\n" +
      "  ROLLBACK\n" +
      "TO SHOW AN 'ON UPDATE RESTRICT' FOREIGN KEY.");

    // display the initial content of the 'deptmt' and 'empl' table
    FK_TwoTablesDisplay(con);

    // create an 'ON UPDATE RESTRICT' foreign key
    FK_Create("ON UPDATE RESTRICT", con);

    // update the parent table, 'deptmt', with data that violates the 'ON
    // UPDATE RESTRICT' foreign key. An error is expected to be returned.
    try
    {
      System.out.println();
      System.out.println(
        "  UPDATE deptmt SET deptno = 'E01' WHERE deptno = 'A00'");

      Statement stmt = con.createStatement();
      stmt.executeUpdate(
        "UPDATE deptmt SET deptno = 'E01' WHERE deptno = 'A00' ");
      stmt.close();
    }
    catch (Exception e)
    {
      JdbcException jdbcExc = new JdbcException(e, con);
      jdbcExc.handleExpectedErr();
    }

    // update the parent table, 'deptmt', with data that violates the 'ON
    // UPDATE RESTRICT' foreign key. An error is expected to be returned.
    try
    {
      System.out.println();
      System.out.println(
        "  UPDATE deptmt\n" +
        "    SET deptno = CASE\n" +
        "                   WHEN deptno = 'A00' THEN 'B00'\n" +
        "                   WHEN deptno = 'B00' THEN 'A00'\n" +
        "                 END\n" +
        "    WHERE deptno = 'A00' OR deptno = 'B00'");

      Statement stmt1 = con.createStatement();
      stmt1.executeUpdate(
        "UPDATE deptmt " +
        "  SET deptno = CASE " +
        "                 WHEN deptno = 'A00' THEN 'B00' " +
        "                 WHEN deptno = 'B00' THEN 'A00' " +
        "               END " +
        "  WHERE deptno = 'A00' OR deptno = 'B00' ");
      stmt1.close();
    }
    catch (Exception e)
    {
      JdbcException jdbcExc = new JdbcException(e, con);
      jdbcExc.handleExpectedErr();
    }

    // update the child table, 'empl', with data that violates the 'ON
    // UPDATE RESTRICT' foreign key. An error is expected to be returned.
    try
    {
      System.out.println();
      System.out.println(
        "  UPDATE empl SET dept_no = 'G11' WHERE empname = 'Wheeler'");

      Statement stmt2 = con.createStatement();
      stmt2.executeUpdate(
        "UPDATE empl SET dept_no = 'G11' WHERE empname = 'Wheeler' ");
      stmt2.close();
    }
    catch (Exception e)
    {
      JdbcException jdbcExc = new JdbcException(e, con);
      jdbcExc.handleExpectedErr();
    }

    // display the final content of the 'deptmt' and 'empl' table
    FK_TwoTablesDisplay(con);

    // roll back the transaction
    try
    {
      System.out.println();
      System.out.println("  ROLLBACK");
      con.rollback();
    }
    catch (Exception e)
    {
      JdbcException jdbcExc = new JdbcException(e);
      jdbcExc.handle();
    }

    // drop the foreign key
    FK_Drop(con);

  } // demo_FK_ON_UPDATE_RESTRICT

  // This function demonstrates how to use an 'ON DELETE CASCADE' foreign key
  static void demo_FK_ON_DELETE_CASCADE(Connection con)
  {
    System.out.println();
    System.out.println(
      "----------------------------------------------------------\n" +
      "USE THE SQL STATEMENTS:\n" +
      "  ALTER TABLE\n" +
      "  COMMIT\n" +
      "  DELETE\n" +
      "  ROLLBACK\n" +
      "TO SHOW AN 'ON DELETE CASCADE' FOREIGN KEY.");

    // display the initial content of the 'deptmt' and 'empl' table
    FK_TwoTablesDisplay(con);

    // create an 'ON DELETE CASCADE' foreign key
    FK_Create("ON DELETE CASCADE", con);

    // delete from the parent table, 'deptmt'
    try
    {
      System.out.println();
      System.out.println("  DELETE FROM deptmt WHERE deptno = 'C00'");

      Statement stmt = con.createStatement();
      stmt.executeUpdate("DELETE FROM deptmt WHERE deptno = 'C00' ");
      stmt.close();
    }
    catch (Exception e)
    {
      JdbcException jdbcExc = new JdbcException(e, con);
      jdbcExc.handle();
    }

    // display the content of the 'deptmt' and 'empl' table
    FK_TwoTablesDisplay(con);

    // delete from the child table, 'empl'
    try
    {
      System.out.println();
      System.out.println("  DELETE FROM empl WHERE empname = 'Wheeler'");

      Statement stmt = con.createStatement();
      stmt.executeUpdate("DELETE FROM empl WHERE empname = 'Wheeler' ");
      stmt.close();
    }
    catch (Exception e)
    {
      JdbcException jdbcExc = new JdbcException(e, con);
      jdbcExc.handle();
    }

    // display the final content of the 'deptmt' and 'empl' table
    FK_TwoTablesDisplay(con);

    // roll back the transaction
    try
    {
      System.out.println();
      System.out.println("  ROLLBACK");
      con.rollback();
    }
    catch (Exception e)
    {
      JdbcException jdbcExc = new JdbcException(e);
      jdbcExc.handle();
    }

    // drop the foreign key
    FK_Drop(con);

  } // demo_FK_ON_DELETE_CASCADE

  // This function demonstrates how to use an 'ON DELETE SET NULL'
  // foreign key
  static void demo_FK_ON_DELETE_SET_NULL(Connection con)
  {
    System.out.println();
    System.out.println(
      "----------------------------------------------------------\n" +
      "USE THE SQL STATEMENTS:\n" +
      "  ALTER TABLE\n" +
      "  COMMIT\n" +
      "  DELETE\n" +
      "  ROLLBACK\n" +
      "TO SHOW AN 'ON DELETE SET NULL' FOREIGN KEY.");

    // display the initial content of the 'deptmt' and 'empl' table
    FK_TwoTablesDisplay(con);

    // create an 'ON DELETE SET NULL' foreign key
    FK_Create("ON DELETE SET NULL", con);

    // delete from the parent table, 'deptmt'
    try
    {
      System.out.println();
      System.out.println("  DELETE FROM deptmt WHERE deptno = 'C00'");

      Statement stmt = con.createStatement();
      stmt.executeUpdate("DELETE FROM deptmt WHERE deptno = 'C00' ");
      stmt.close();
    }
    catch (Exception e)
    {
      JdbcException jdbcExc = new JdbcException(e, con);
      jdbcExc.handle();
    }

    // display the content of the 'deptmt' and 'empl' table
    FK_TwoTablesDisplay(con);

    // delete from the child table, 'empl'
    try
    {
      System.out.println();
      System.out.println("  DELETE FROM empl WHERE empname = 'Wheeler'");

      Statement stmt1 = con.createStatement();
      stmt1.executeUpdate("DELETE FROM empl WHERE empname = 'Wheeler' ");
      stmt1.close();
    }
    catch (Exception e)
    {
      JdbcException jdbcExc = new JdbcException(e, con);
      jdbcExc.handle();
    }

    // display the final content of the 'deptmt' and 'empl' table
    FK_TwoTablesDisplay(con);

    // roll back the transaction
    try
    {
      System.out.println();
      System.out.println("  ROLLBACK");
      con.rollback();
    }
    catch (Exception e)
    {
      JdbcException jdbcExc = new JdbcException(e);
      jdbcExc.handle();
    }

    // drop the foreign key
    FK_Drop(con);

  } // demo_FK_ON_DELETE_SET_NULL

  // This function demonstrates how to use an 'ON DELETE NO ACTION'
  // foreign key
  static void demo_FK_ON_DELETE_NO_ACTION(Connection con)
  {
    System.out.println();
    System.out.println(
      "----------------------------------------------------------\n" +
      "USE THE SQL STATEMENTS:\n" +
      "  ALTER TABLE\n" +
      "  COMMIT\n" +
      "  DELETE\n" +
      "  ROLLBACK\n" +
      "TO SHOW AN 'ON DELETE NO ACTION' FOREIGN KEY.");

    // display the initial content of the 'deptmt' and 'empl' table
    FK_TwoTablesDisplay(con);

    // create an 'ON DELETE NO ACTION' foreign key
    FK_Create("ON DELETE NO ACTION", con);

    // delete from the parent table, 'deptmt'
    try
    {
      System.out.println();
      System.out.println("  DELETE FROM deptmt WHERE deptno = 'C00'");

      Statement stmt = con.createStatement();
      stmt.executeUpdate("DELETE FROM deptmt WHERE deptno = 'C00' ");
      stmt.close();
    }
    catch (Exception e)
    {
      JdbcException jdbcExc = new JdbcException(e, con);
      jdbcExc.handleExpectedErr();
    }

    // delete from the child table, 'empl'
    try
    {
      System.out.println();
      System.out.println("  DELETE FROM empl WHERE empname = 'Wheeler'");

      Statement stmt1 = con.createStatement();
      stmt1.executeUpdate("DELETE FROM empl WHERE empname = 'Wheeler' ");
      stmt1.close();
    }
    catch (Exception e)
    {
      JdbcException jdbcExc = new JdbcException(e, con);
      jdbcExc.handle();
    }

    // display the final content of the 'deptmt' and 'empl' table
    FK_TwoTablesDisplay(con);

    // roll back the transaction
    try
    {
      System.out.println();
      System.out.println("  ROLLBACK");
      con.rollback();
    }
    catch (Exception e)
    {
      JdbcException jdbcExc = new JdbcException(e);
      jdbcExc.handle();
    }

    // drop the foreign key
    FK_Drop(con);
  } // demo_FK_ON_DELETE_NO_ACTION
} // TbConstr