//***************************************************************************
// (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