//***************************************************************************
// (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.sqlj
//
// SAMPLE: How to create, use and drop constraints
//
// SQL Statements USED:
// CREATE TABLE
// DROP TABLE
// DELETE
// COMMIT
// ROLLBACK
//
// JAVA 2 CLASSES USED:
// Statement
//
// Classes used from Util.sqlj are:
// Db
// SqljException
//
//
// 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.*;
import sqlj.runtime.*;
import sqlj.runtime.ref.*;
class TbConstr
{
public static void main(String argv[])
{
DefaultContext ctx = null;
try
{
Db db = new Db(argv);
System.out.println();
System.out.println(
"THIS SAMPLE SHOWS HOW TO CREATE, USE AND DROP CONSTRAINTS.");
// connect to the 'sample' database
ctx = db.getDefaultContext();
demo_NOT_NULL( ctx.getConnection() );
demo_UNIQUE( ctx.getConnection() );
demo_PRIMARY_KEY( ctx.getConnection() );
demo_CHECK( ctx.getConnection() );
demo_CHECK_INFO();
demo_WITH_DEFAULT( ctx.getConnection() );
System.out.println();
System.out.println(
"----------------------------------------------------------\n" +
"#####################################################\n" +
"# Create tables for FOREIGN KEY sample functions #\n" +
"#####################################################");
FK_TwoTablesCreate( ctx.getConnection() );
demo_FK_OnInsertShow( ctx.getConnection() );
demo_FK_ON_UPDATE_NO_ACTION( ctx.getConnection() );
demo_FK_ON_UPDATE_RESTRICT( ctx.getConnection() );
demo_FK_ON_DELETE_CASCADE( ctx.getConnection() );
demo_FK_ON_DELETE_SET_NULL( ctx.getConnection() );
demo_FK_ON_DELETE_NO_ACTION( ctx.getConnection() );
System.out.println();
System.out.println(
"----------------------------------------------------------\n" +
"########################################################\n" +
"# Drop tables created for FOREIGN KEY sample functions #\n" +
"########################################################");
FK_TwoTablesDrop();
// disconnect from the 'sample' database
db.disconnect();
}
catch (Exception e)
{
SqljException sqljExc = new SqljException(e);
sqljExc.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 dept_details(deptno CHAR(3) NOT NULL,\n" +
" deptname VARCHAR(20),\n" +
" CONSTRAINT pk_dept\n" +
" PRIMARY KEY(deptno))");
#sql {CREATE TABLE dept_details(deptno CHAR(3) NOT NULL,
deptname VARCHAR(20),
CONSTRAINT pk_dept
PRIMARY KEY(deptno))};
System.out.println();
System.out.println(
" INSERT INTO dept_details VALUES('A00', 'ADMINISTRATION'),\n" +
" ('B00', 'DEVELOPMENT'),\n" +
" ('C00', 'SUPPORT')");
Statement stmt = con.createStatement();
stmt.executeUpdate(
"INSERT INTO dept_details VALUES('A00', 'ADMINISTRATION')," +
" ('B00', 'DEVELOPMENT')," +
" ('C00', 'SUPPORT')");
stmt.close();
System.out.println();
System.out.println(
" CREATE TABLE emp_details(empno CHAR(4),\n" +
" empname VARCHAR(10),\n" +
" dept_no CHAR(3))");
#sql {CREATE TABLE emp_details(empno CHAR(4),
empname VARCHAR(10),
dept_no CHAR(3))};
System.out.println();
System.out.println(
" INSERT INTO emp_details 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 stmt1 = con.createStatement();
stmt1.executeUpdate(
"INSERT INTO emp_details VALUES('0010', 'Smith', 'A00')," +
" ('0020', 'Ngan', 'B00')," +
" ('0030', 'Lu', 'B00')," +
" ('0040', 'Wheeler', 'B00')," +
" ('0050', 'Burke', 'C00')," +
" ('0060', 'Edwards', 'C00')," +
" ('0070', 'Lea', 'C00')");
stmt1.close();
System.out.println();
System.out.println(" COMMIT");
#sql {COMMIT};
}
catch (Exception e)
{
SqljException sqljExc = new SqljException(e);
sqljExc.handle();
}
} // FK_TwoTablesCreate
// helping function
static void FK_TwoTablesDisplay(Connection con)
{
try
{
System.out.println();
System.out.println(" SELECT * FROM dept_details");
System.out.println(" DEPTNO DEPTNAME\n" +
" ------- --------------");
Statement stmt = con.createStatement();
ResultSet rs = stmt.executeQuery("SELECT * FROM dept_details");
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 emp_details");
System.out.println(" EMPNO EMPNAME DEPT_NO\n" +
" ----- ---------- -------");
Statement stmt1 = con.createStatement();
ResultSet rs1 = stmt1.executeQuery("SELECT * FROM emp_details");
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)
{
SqljException sqljExc = new SqljException(e);
sqljExc.handle();
}
} // FK_TwoTablesDisplay
// helping function
static void FK_TwoTablesDrop()
{
try
{
System.out.println();
System.out.println(" DROP TABLE dept_details");
#sql {DROP TABLE dept_details};
System.out.println();
System.out.println(" DROP TABLE emp_details");
#sql {DROP TABLE emp_details};
System.out.println();
System.out.println(" COMMIT");
#sql {COMMIT};
}
catch (Exception e)
{
SqljException sqljExc = new SqljException(e);
sqljExc.handle();
}
} // FK_TwoTablesDrop
// helping function
static void FK_Create(String ruleClause, Connection con)
{
try
{
System.out.println();
System.out.println(" ALTER TABLE emp_details\n" +
" ADD CONSTRAINT fk_dept\n" +
" FOREIGN KEY(dept_no)\n" +
" REFERENCES dept_details(deptno)\n" +
" " + ruleClause);
Statement stmt = con.createStatement();
stmt.execute("ALTER TABLE emp_details" +
" ADD CONSTRAINT fk_dept" +
" FOREIGN KEY(dept_no)" +
" REFERENCES dept_details(deptno) " +
" " + ruleClause);
stmt.close();
System.out.println();
System.out.println(" COMMIT");
#sql {COMMIT};
}
catch (Exception e)
{
SqljException sqljExc = new SqljException(e);
sqljExc.handle();
}
} // FK_Create
// helping function
static void FK_Drop(Connection con)
{
try
{
System.out.println();
System.out.println(" ALTER TABLE emp_details DROP CONSTRAINT fk_dept");
Statement stmt = con.createStatement();
stmt.execute("ALTER TABLE emp_details DROP CONSTRAINT fk_dept");
stmt.close();
System.out.println();
System.out.println(" COMMIT");
#sql {COMMIT};
}
catch (Exception e)
{
SqljException sqljExc = new SqljException(e);
sqljExc.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 emp_sal with a 'NOT NULL' constraint
try
{
System.out.println();
System.out.println(
" CREATE TABLE emp_sal(lastname VARCHAR(10) NOT NULL,\n" +
" firstname VARCHAR(10),\n" +
" salary DECIMAL(7, 2))");
#sql {CREATE TABLE emp_sal(lastname VARCHAR(10) NOT NULL,
firstname VARCHAR(10),
salary DECIMAL(7, 2))};
System.out.println();
System.out.println(" COMMIT");
#sql {COMMIT};
}
catch (Exception e)
{
SqljException sqljExc = new SqljException(e);
sqljExc.handle();
}
// Insert a row in the table emp_sal with NULL as the lastname.
// This insert will fail with an expected error.
try
{
System.out.println();
System.out.println(
" INSERT INTO emp_sal VALUES(NULL, 'PHILIP', 17000.00)");
Statement stmt = con.createStatement();
stmt.executeUpdate(
"INSERT INTO emp_sal VALUES(NULL, 'PHILIP', 17000.00)");
stmt.close();
}
catch (Exception e)
{
SqljException sqljExc = new SqljException(e);
sqljExc.handleExpectedErr();
}
// drop the table emp_sal
try
{
System.out.println();
System.out.println(" DROP TABLE emp_sal");
#sql {DROP TABLE emp_sal};
}
catch (Exception e)
{
SqljException sqljExc = new SqljException(e);
sqljExc.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 emp_sal with a 'UNIQUE' constraint
try
{
System.out.println();
System.out.println(
" CREATE TABLE emp_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))");
#sql {CREATE TABLE emp_sal(lastname VARCHAR(10) NOT NULL,
firstname VARCHAR(10) NOT NULL,
salary DECIMAL(7, 2),
CONSTRAINT unique_cn
UNIQUE(lastname, firstname))};
System.out.println();
System.out.println(" COMMIT");
#sql {COMMIT};
}
catch (Exception e)
{
SqljException sqljExc = new SqljException(e);
sqljExc.handle();
}
// Insert two rows into the table emp_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 emp_sal VALUES('SMITH', 'PHILIP', 17000.00),\n" +
" ('SMITH', 'PHILIP', 21000.00)");
Statement stmt = con.createStatement();
stmt.executeUpdate(
"INSERT INTO emp_sal VALUES('SMITH', 'PHILIP', 17000.00), " +
" ('SMITH', 'PHILIP', 21000.00)");
stmt.close();
}
catch (Exception e)
{
SqljException sqljExc = new SqljException(e);
sqljExc.handleExpectedErr();
}
// drop the 'UNIQUE' constraint on the table emp_sal
try
{
System.out.println();
System.out.println(" ALTER TABLE emp_sal DROP CONSTRAINT unique_cn");
#sql {ALTER TABLE emp_sal DROP CONSTRAINT unique_cn};
}
catch (Exception e)
{
SqljException sqljExc = new SqljException(e);
sqljExc.handle();
}
// drop the table emp_sal
try
{
System.out.println();
System.out.println(" DROP TABLE emp_sal");
#sql {DROP TABLE emp_sal};
}
catch (Exception e)
{
SqljException sqljExc = new SqljException(e);
sqljExc.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 emp_sal with a 'PRIMARY KEY' constraint
try
{
System.out.println();
System.out.println(
" CREATE TABLE emp_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))");
#sql {CREATE TABLE emp_sal(lastname VARCHAR(10) NOT NULL,
firstname VARCHAR(10) NOT NULL,
salary DECIMAL(7, 2),
CONSTRAINT pk_cn
PRIMARY KEY(lastname, firstname))};
System.out.println();
System.out.println(" COMMIT");
#sql {COMMIT};
}
catch (Exception e)
{
SqljException sqljExc = new SqljException(e);
sqljExc.handle();
}
// Insert two rows into the table emp_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 emp_sal VALUES('SMITH', 'PHILIP', 17000.00),\n" +
" ('SMITH', 'PHILIP', 21000.00)");
Statement stmt = con.createStatement();
stmt.executeUpdate(
"INSERT INTO emp_sal VALUES('SMITH', 'PHILIP', 17000.00)," +
" ('SMITH', 'PHILIP', 21000.00)");
}
catch (Exception e)
{
SqljException sqljExc = new SqljException(e);
sqljExc.handleExpectedErr();
}
// drop the 'PRIMARY KEY' constraint on the table emp_sal
try
{
System.out.println();
System.out.println(" ALTER TABLE emp_sal DROP CONSTRAINT pk_cn");
#sql {ALTER TABLE emp_sal DROP CONSTRAINT pk_cn};
}
catch (Exception e)
{
SqljException sqljExc = new SqljException(e);
sqljExc.handle();
}
// drop the table emp_sal
try
{
System.out.println();
System.out.println(" DROP TABLE emp_sal");
#sql {DROP TABLE emp_sal};
}
catch (Exception e)
{
SqljException sqljExc = new SqljException(e);
sqljExc.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 emp_sal with a 'CHECK' constraint
try
{
System.out.println();
System.out.println(
" CREATE TABLE emp_sal(lastname VARCHAR(10),\n" +
" firstname VARCHAR(10),\n" +
" salary DECIMAL(7, 2),\n" +
" CONSTRAINT check_cn\n" +
" CHECK(salary < 25000.00))");
#sql {CREATE TABLE emp_sal(lastname VARCHAR(10),
firstname VARCHAR(10),
salary DECIMAL(7, 2),
CONSTRAINT check_cn
CHECK(salary < 25000.00))};
System.out.println();
System.out.println(" COMMIT");
#sql {COMMIT};
}
catch (Exception e)
{
SqljException sqljExc = new SqljException(e);
sqljExc.handle();
}
// Insert a row in the table emp_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 emp_sal VALUES('SMITH', 'PHILIP', 27000.00)");
Statement stmt = con.createStatement();
stmt.executeUpdate(
"INSERT INTO emp_sal VALUES('SMITH', 'PHILIP', 27000.00)");
}
catch (Exception e)
{
SqljException sqljExc = new SqljException(e);
sqljExc.handleExpectedErr();
}
// drop the 'CHECK' constraint on the table emp_sal
try
{
System.out.println();
System.out.println(" ALTER TABLE emp_sal DROP CONSTRAINT check_cn");
#sql {ALTER TABLE emp_sal DROP CONSTRAINT check_cn};
}
catch (Exception e)
{
SqljException sqljExc = new SqljException(e);
sqljExc.handle();
}
// drop the table emp_sal
try
{
System.out.println();
System.out.println(" DROP TABLE emp_sal");
#sql {DROP TABLE emp_sal};
}
catch (Exception e)
{
SqljException sqljExc = new SqljException(e);
sqljExc.handle();
}
} // demo_CHECK
// This function demonstrates how to use an 'INFORMATIONAL' constraint.
static void demo_CHECK_INFO()
{
try
{
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 emp_details with a 'CHECK' constraint
System.out.println(
"\n CREATE TABLE emp_details(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");
#sql {CREATE TABLE emp_details(empno INTEGER NOT NULL PRIMARY KEY,
name VARCHAR(10),
firstname VARCHAR(20),
salary INTEGER CONSTRAINT minsalary
CHECK (salary >= 25000)
NOT ENFORCED
ENABLE QUERY OPTIMIZATION)};
#sql {COMMIT};
System.out.println(" COMMIT");
}
catch (Exception e)
{
SqljException sqljExc = new SqljException(e);
sqljExc.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 emp_details VALUES(1, 'SMITH', 'PHILIP', 1000)\n");
#sql {INSERT INTO emp_details VALUES(1, 'SMITH', 'PHILIP', 1000)};
}
catch (Exception e)
{
SqljException sqljExc = new SqljException(e);
sqljExc.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 emp_details ALTER CHECK minsalary ENFORCED");
#sql {ALTER TABLE emp_details ALTER CHECK minsalary ENFORCED};
}
catch (Exception e)
{
SqljException sqljExc = new SqljException(e);
sqljExc.handleExpectedErr();
}
try
{
// delete entries from EMP_DETAILS Table
System.out.println("\n DELETE FROM emp_details");
#sql {DELETE FROM emp_details};
}
catch (Exception e)
{
SqljException sqljExc = new SqljException(e);
sqljExc.handle();
}
try
{
// alter the constraint to make it ENFORCED by database manager
System.out.println(
"\n\nTO SHOW ENFORCED OPTION\n" +
"\n ALTER TABLE emp_details ALTER CHECK minsalary ENFORCED\n ");
#sql {ALTER TABLE emp_details ALTER CHECK minsalary ENFORCED};
}
catch (Exception e)
{
SqljException sqljExc = new SqljException(e);
sqljExc.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 emp_details VALUES(1, 'SMITH', 'PHILIP', 1000)");
#sql {INSERT INTO emp_details VALUES(1, 'SMITH', 'PHILIP', 1000)};
}
catch (Exception e)
{
SqljException sqljExc = new SqljException(e);
sqljExc.handleExpectedErr();
}
try
{
// drop table
System.out.println("\n DROP TABLE emp_details");
#sql {DROP TABLE emp_details};
}
catch (Exception e)
{
SqljException sqljExc = new SqljException(e);
sqljExc.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 emp_sal with a 'WITH DEFAULT' constraint
try
{
System.out.println();
System.out.println(
" CREATE TABLE emp_sal(lastname VARCHAR(10),\n" +
" firstname VARCHAR(10),\n" +
" salary DECIMAL(7, 2) " +
"WITH DEFAULT 17000.00)");
#sql {
CREATE TABLE emp_sal(lastname VARCHAR(10),
firstname VARCHAR(10),
salary DECIMAL(7, 2) WITH DEFAULT 17000.00)};
System.out.println();
System.out.println(" COMMIT");
#sql {COMMIT};
}
catch (Exception e)
{
SqljException sqljExc = new SqljException(e);
sqljExc.handle();
}
// Insert three rows into the table emp_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
{
System.out.println();
System.out.println(
" INSERT INTO emp_sal(lastname, firstname)\n" +
" VALUES('SMITH', 'PHILIP'),\n" +
" ('PARKER', 'JOHN'),\n" +
" ('PEREZ', 'MARIA')");
Statement stmt = con.createStatement();
stmt.executeUpdate("INSERT INTO emp_sal(lastname, firstname)"+
" VALUES('SMITH', 'PHILIP')," +
" ('PARKER', 'JOHN')," +
" ('PEREZ', 'MARIA')");
stmt.close();
}
catch (Exception e)
{
SqljException sqljExc = new SqljException(e);
sqljExc.handle();
}
// retrieve and display the data in the table emp_sal
try
{
System.out.println();
System.out.println(" SELECT * FROM emp_sal");
System.out.println(" FIRSTNAME LASTNAME SALARY\n" +
" ---------- ---------- --------");
Statement stmt = con.createStatement();
ResultSet rs3 = stmt.executeQuery("SELECT * FROM emp_sal");
while (rs3.next())
{
System.out.println(" " +
Data.format(rs3.getString("firstname"),10) + " " +
Data.format(rs3.getString("lastname"),10) + " " +
Data.format(rs3.getDouble("salary"),7,2));
}
rs3.close();
stmt.close();
}
catch (Exception e)
{
SqljException sqljExc = new SqljException(e);
sqljExc.handle();
}
// drop the table emp_sal
try
{
System.out.println();
System.out.println(" DROP TABLE emp_sal");
#sql {DROP TABLE emp_sal};
}
catch (Exception e)
{
SqljException sqljExc = new SqljException(e);
sqljExc.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 'dept_details' and 'emp_details' table
FK_TwoTablesDisplay(con);
// create a foreign key on the 'emp_details' table that reference the
// 'dept_details' table
FK_Create("", con);
// insert an entry into the parent table, 'dept_details'
try
{
System.out.println();
System.out.println(" INSERT INTO dept_details VALUES('D00', 'SALES')");
Statement stmt = con.createStatement();
stmt.executeUpdate("INSERT INTO dept_details VALUES('D00', 'SALES')");
stmt.close();
}
catch (Exception e)
{
SqljException sqljExc = new SqljException(e);
sqljExc.handle();
}
// insert an entry into the child table, 'emp_details'
try
{
System.out.println();
System.out.println(
" INSERT INTO emp_details VALUES('0080', 'Pearce', 'E03')");
Statement stmt1 = con.createStatement();
stmt1.executeUpdate(
"INSERT INTO emp_details VALUES('0080', 'Pearce', 'E03')");
stmt1.close();
}
catch (Exception e)
{
SqljException sqljExc = new SqljException(e);
sqljExc.handleExpectedErr();
}
// display the final content of the 'dept_details' and 'emp_details' table
FK_TwoTablesDisplay(con);
// roll back the transaction
try
{
System.out.println();
System.out.println(" ROLLBACK");
#sql {ROLLBACK};
}
catch (Exception e)
{
SqljException sqljExc = new SqljException(e);
sqljExc.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 'dept_details' and 'emp_details' table
FK_TwoTablesDisplay(con);
// create an 'ON UPDATE NO ACTION' foreign key
FK_Create("ON UPDATE NO ACTION", con);
// update the parent table, 'dept_details'
try
{
System.out.println();
System.out.println(
" UPDATE dept_details SET deptno = 'E01' WHERE deptno = 'A00'");
Statement stmt = con.createStatement();
stmt.executeUpdate(
"UPDATE dept_details SET deptno = 'E01' WHERE deptno = 'A00' ");
stmt.close();
}
catch (Exception e)
{
SqljException sqljExc = new SqljException(e);
sqljExc.handleExpectedErr();
}
// update the parent table, 'dept_details'
try
{
System.out.println();
System.out.println(
" UPDATE dept_details\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 dept_details" +
" 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)
{
SqljException sqljExc = new SqljException(e);
sqljExc.handleExpectedErr();
}
// update the child table, 'emp_details'
try
{
System.out.println();
System.out.println(
" UPDATE emp_details SET dept_no = 'G11' WHERE empname = 'Wheeler'");
Statement stmt2 = con.createStatement();
stmt2.executeUpdate(
"UPDATE emp_details SET dept_no = 'G11' WHERE empname = 'Wheeler' ");
stmt2.close();
}
catch (Exception e)
{
SqljException sqljExc = new SqljException(e);
sqljExc.handleExpectedErr();
}
// display the final content of the 'dept_details' and 'emp_details' table
FK_TwoTablesDisplay(con);
// roll back the transaction
try
{
System.out.println();
System.out.println(" ROLLBACK");
#sql {ROLLBACK};
}
catch (Exception e)
{
SqljException sqljExc = new SqljException(e);
sqljExc.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 'dept_details' and 'emp_details' table
FK_TwoTablesDisplay(con);
// create an 'ON UPDATE RESTRICT' foreign key
FK_Create("ON UPDATE RESTRICT", con);
// update the parent table, 'dept_details', 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 dept_details SET deptno = 'E01' WHERE deptno = 'A00'");
Statement stmt = con.createStatement();
stmt.executeUpdate(
"UPDATE dept_details SET deptno = 'E01' WHERE deptno = 'A00' ");
stmt.close();
}
catch (Exception e)
{
SqljException sqljExc = new SqljException(e);
sqljExc.handleExpectedErr();
}
// update the parent table, 'dept_details', 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 dept_details\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 dept_details" +
" 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)
{
SqljException sqljExc = new SqljException(e);
sqljExc.handleExpectedErr();
}
// update the child table, 'emp_details', 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 emp_details SET dept_no = 'G11' WHERE empname = 'Wheeler'");
Statement stmt2 = con.createStatement();
stmt2.executeUpdate(
"UPDATE emp_details SET dept_no = 'G11' WHERE empname = 'Wheeler'");
stmt2.close();
}
catch (Exception e)
{
SqljException sqljExc = new SqljException(e);
sqljExc.handleExpectedErr();
}
// display the final content of the 'dept_details' and 'emp_details' table
FK_TwoTablesDisplay(con);
// roll back the transaction
try
{
System.out.println();
System.out.println(" ROLLBACK");
#sql {ROLLBACK};
}
catch (Exception e)
{
SqljException sqljExc = new SqljException(e);
sqljExc.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 'dept_details' and 'emp_details' table
FK_TwoTablesDisplay(con);
// create an 'ON DELETE CASCADE' foreign key
FK_Create("ON DELETE CASCADE", con);
// delete from the parent table, 'dept_details'
try
{
System.out.println();
System.out.println(" DELETE FROM dept_details WHERE deptno = 'C00'");
Statement stmt = con.createStatement();
stmt.executeUpdate("DELETE FROM dept_details WHERE deptno = 'C00'");
stmt.close();
}
catch (Exception e)
{
SqljException sqljExc = new SqljException(e);
sqljExc.handle();
}
// display the content of the 'dept_details' and 'emp_details' table
FK_TwoTablesDisplay(con);
// delete from the child table, 'emp_details'
try
{
System.out.println();
System.out.println(" DELETE FROM emp_details WHERE empname = 'Wheeler'");
Statement stmt1 = con.createStatement();
stmt1.executeUpdate(
"DELETE FROM emp_details WHERE empname = 'Wheeler' ");
stmt1.close();
}
catch (Exception e)
{
SqljException sqljExc = new SqljException(e);
sqljExc.handle();
}
// display the final content of the 'dept_details' and 'emp_details' table
FK_TwoTablesDisplay(con);
// roll back the transaction
try
{
System.out.println();
System.out.println(" ROLLBACK");
#sql {ROLLBACK};
}
catch (Exception e)
{
SqljException sqljExc = new SqljException(e);
sqljExc.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 'dept_details' and 'emp_details' table
FK_TwoTablesDisplay(con);
// create an 'ON DELETE SET NULL' foreign key
FK_Create("ON DELETE SET NULL",con);
// delete from the parent table, 'dept_details'
try
{
System.out.println();
System.out.println(" DELETE FROM dept_details WHERE deptno = 'C00'");
Statement stmt = con.createStatement();
stmt.executeUpdate("DELETE FROM dept_details WHERE deptno = 'C00' ");
stmt.close();
}
catch (Exception e)
{
SqljException sqljExc = new SqljException(e);
sqljExc.handle();
}
// display the content of the 'dept_details' and 'emp_details' table
FK_TwoTablesDisplay(con);
// delete from the child table, 'emp_details'
try
{
System.out.println();
System.out.println(" DELETE FROM emp_details WHERE empname = 'Wheeler'");
Statement stmt1 = con.createStatement();
stmt1.executeUpdate("DELETE FROM emp_details WHERE empname = 'Wheeler'");
stmt1.close();
}
catch (Exception e)
{
SqljException sqljExc = new SqljException(e);
sqljExc.handle();
}
// display the final content of the 'dept_details' and 'emp_details' table
FK_TwoTablesDisplay(con);
// roll back the transaction
try
{
System.out.println();
System.out.println(" ROLLBACK");
#sql {ROLLBACK};
}
catch (Exception e)
{
SqljException sqljExc = new SqljException(e);
sqljExc.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 'dept_details' and 'emp_details' table
FK_TwoTablesDisplay(con);
// create an 'ON DELETE NO ACTION' foreign key
FK_Create("ON DELETE NO ACTION",con);
// delete from the parent table, 'dept_details'
try
{
System.out.println();
System.out.println(" DELETE FROM dept_details WHERE deptno = 'C00'");
Statement stmt = con.createStatement();
stmt.executeUpdate("DELETE FROM dept_details WHERE deptno = 'C00' ");
stmt.close();
}
catch (Exception e)
{
SqljException sqljExc = new SqljException(e);
sqljExc.handleExpectedErr();
}
// delete from the child table, 'emp_details'
try
{
System.out.println();
System.out.println(" DELETE FROM emp_details WHERE empname = 'Wheeler'");
Statement stmt1 = con.createStatement();
stmt1.executeUpdate("DELETE FROM emp_details WHERE empname = 'Wheeler'");
stmt1.close();
}
catch (Exception e)
{
SqljException sqljExc = new SqljException(e);
sqljExc.handle();
}
// display the final content of the 'dept_details' and 'emp_details' table
FK_TwoTablesDisplay(con);
// roll back the transaction
try
{
System.out.println();
System.out.println(" ROLLBACK");
#sql {ROLLBACK};
}
catch (Exception e)
{
SqljException sqljExc = new SqljException(e);
sqljExc.handle();
}
// drop the foreign key
FK_Drop(con);
} // demo_FK_ON_DELETE_NO_ACTION
} // TbConstr