/****************************************************************************
** (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.cs
**
** SAMPLE: How to create, use and drop table constraints
** with the DB2 .Net Data Provider
**
** SQL Statements USED:
** CREATE TABLE
** ALTER TABLE
** DROP TABLE
** INSERT
** SELECT
** DELETE
** UPDATE
**
** DB2 .NET Data Provider Classes USED:
** DB2Connection
** DB2Command
** DB2Transaction
**
**
*****************************************************************************
**
** Building and Running the sample program
**
** 1. Compile the TbConstr.cs file with bldapp.bat by entering the following
** at the command prompt:
**
** bldapp TbConstr
**
** or compile TbConstr.cs with the makefile by entering the following at
** the command prompt:
**
** nmake TbConstr
**
** 2. Run the TbConstr program by entering the program name at the command
** prompt:
**
** TbConstr
**
*****************************************************************************
**
** For more information on the sample programs, see the README file.
**
** For information on developing applications, see the Application
** Development Guide.
**
** 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
**
****************************************************************************/
using System;
using System.Data;
using System.IO;
using IBM.Data.DB2;
class TbConstr
{
public static void Main(String[] args)
{
// Declare a DB2Connection and a DB2Transaction
DB2Connection conn = null;
DB2Transaction trans = null;
try
{
Console.WriteLine();
Console.WriteLine(
"THIS SAMPLE SHOWS HOW TO CREATE, USE AND DROP CONSTRAINTS.");
// Connect to a database
Console.WriteLine("\n Connecting to a database ...");
conn = ConnectDb(args);
// Demonstrate how to use a 'NOT NULL' constraint
trans = conn.BeginTransaction();
Demo_NOT_NULL(conn,trans);
// Demonstrate how to use a 'UNIQUE' constraint
trans = conn.BeginTransaction();
Demo_UNIQUE(conn, trans);
// Demonstrate how to use a 'PRIMARY KEY' constraint
trans = conn.BeginTransaction();
Demo_PRIMARY_KEY(conn,trans);
// Demonstrate how to use a 'CHECK' constraint
trans = conn.BeginTransaction();
Demo_CHECK(conn,trans);
// Demonstrate how to use a 'WITH DEFAULT' constraint
trans = conn.BeginTransaction();
Demo_WITH_DEFAULT(conn,trans);
Console.WriteLine();
Console.WriteLine(
"----------------------------------------------------------\n" +
"#####################################################\n" +
"# Create tables for FOREIGN KEY sample functions #\n" +
"#####################################################");
// Create two foreign keys
trans = conn.BeginTransaction();
FK_TwoTablesCreate(conn,trans);
// Demonstrate how to insert into a foreign key
trans = conn.BeginTransaction();
Demo_FK_OnInsertShow(conn,trans);
// Demonstrate how to use an 'ON UPDATE NO ACTION' foreign key
trans = conn.BeginTransaction();
Demo_FK_ON_UPDATE_NO_ACTION(conn,trans);
// Demonstrate how to use an 'ON UPDATE RESTRICT' foreign key
trans = conn.BeginTransaction();
Demo_FK_ON_UPDATE_RESTRICT(conn,trans);
// Demonstrate how to use an 'ON DELETE CASCADE' foreign key
trans = conn.BeginTransaction();
Demo_FK_ON_DELETE_CASCADE(conn,trans);
// Demonstrate how to use an 'ON DELETE SET NULL' foreign key
trans = conn.BeginTransaction();
Demo_FK_ON_DELETE_SET_NULL(conn,trans);
// Demonstrate how to use an 'ON DELETE NO ACTION' foreign key
trans = conn.BeginTransaction();
Demo_FK_ON_DELETE_NO_ACTION(conn,trans);
Console.WriteLine();
Console.WriteLine(
"----------------------------------------------------------\n" +
"########################################################\n" +
"# Drop tables created for FOREIGN KEY sample functions #\n" +
"########################################################");
// Drop the tables created for the FOREIGN KEY sample functions
trans = conn.BeginTransaction();
FK_TwoTablesDrop(conn,trans);
// Disconnect from the database
Console.WriteLine("\n Disconnect from the database.");
conn.Close();
}
catch (Exception e)
{
Console.WriteLine(e.Message);
conn.Close();
}
} // Main
// Helping method: This method establishes a connection to a database
public static DB2Connection ConnectDb(String[] argv)
{
String server = "";
String alias = "";
String userId = "";
String password = "";
Int32 portNumber = -1;
String connectString;
if( argv.Length > 5 ||
( argv.Length == 1 &&
( String.Compare(argv[0],"?") == 0 ||
String.Compare(argv[0],"-?") == 0 ||
String.Compare(argv[0],"/?") == 0 ||
String.Compare(argv[0],"-h",true) == 0 ||
String.Compare(argv[0],"/h",true) == 0 ||
String.Compare(argv[0],"-help",true) == 0 ||
String.Compare(argv[0],"/help",true) == 0 ) ) )
{
throw new Exception(
"Usage: prog_name [dbAlias] [userId passwd] \n" +
" prog_name [dbAlias] server portNum userId passwd");
}
switch (argv.Length)
{
case 0: // Use all defaults
alias = "sample";
userId = "";
password = "";
break;
case 1: // dbAlias specified
alias = argv[0];
userId = "";
password = "";
break;
case 2: // userId & passwd specified
alias = "sample";
userId = argv[0];
password = argv[1];
break;
case 3: // dbAlias, userId & passwd specified
alias = argv[0];
userId = argv[1];
password = argv[2];
break;
case 4: // use default dbAlias
alias = "sample";
server = argv[0];
portNumber = Convert.ToInt32(argv[1]);
userId = argv[2];
password = argv[3];
break;
case 5: // everything specified
alias = argv[0];
server = argv[1];
portNumber = Convert.ToInt32(argv[2]);
userId = argv[3];
password = argv[4];
break;
}
if(portNumber==-1)
{
connectString = "Database=" + alias;
}
else
{
connectString = "Server=" + server + ":" + portNumber +
";Database=" + alias;
}
if(userId != "")
{
connectString += ";UID=" + userId + ";PWD=" + password;
}
DB2Connection conn = new DB2Connection(connectString);
conn.Open();
Console.WriteLine(" Connected to the " + alias + " database");
return conn;
} // ConnectDb
// Helping method: This method creates two foreign keys
public static void FK_TwoTablesCreate(DB2Connection conn,
DB2Transaction trans)
{
try
{
// Create table 'dept'
Console.WriteLine();
Console.WriteLine(
" CREATE TABLE dept(deptno CHAR(3) NOT NULL,\n" +
" deptname VARCHAR(20),\n" +
" CONSTRAINT pk_dept\n" +
" PRIMARY KEY(deptno))");
DB2Command cmd = conn.CreateCommand();
cmd.Transaction = trans;
cmd.CommandText =
"CREATE TABLE dept(deptno CHAR(3) NOT NULL, " +
" deptname VARCHAR(20), " +
" CONSTRAINT pk_dept " +
" PRIMARY KEY(deptno))";
cmd.ExecuteNonQuery();
// Insert some values into 'dept'
Console.WriteLine();
Console.WriteLine(
" INSERT INTO dept VALUES('A00', 'ADMINISTRATION'),\n" +
" ('B00', 'DEVELOPMENT'),\n" +
" ('C00', 'SUPPORT')");
cmd.CommandText =
"INSERT INTO dept VALUES('A00', 'ADMINISTRATION'), " +
" ('B00', 'DEVELOPMENT'), " +
" ('C00', 'SUPPORT') ";
cmd.ExecuteNonQuery();
// Create table 'emp'
Console.WriteLine();
Console.WriteLine(
" CREATE TABLE emp(empno CHAR(4),\n" +
" empname VARCHAR(10),\n" +
" dept_no CHAR(3))");
cmd.CommandText = "CREATE TABLE emp(empno CHAR(4), " +
" empname VARCHAR(10), " +
" dept_no CHAR(3))";
cmd.ExecuteNonQuery();
// Insert values into 'emp'
Console.WriteLine();
Console.WriteLine(
" INSERT INTO emp 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')");
cmd.CommandText =
"INSERT INTO emp VALUES('0010', 'Smith', 'A00'), " +
" ('0020', 'Ngan', 'B00'), " +
" ('0030', 'Lu', 'B00'), " +
" ('0040', 'Wheeler', 'B00'), " +
" ('0050', 'Burke', 'C00'), " +
" ('0060', 'Edwards', 'C00'), " +
" ('0070', 'Lea', 'C00') ";
cmd.ExecuteNonQuery();
// Commit the transaction
trans.Commit();
}
catch (Exception e)
{
Console.WriteLine(e.Message);
}
} // FK_TwoTablesCreate
// Helping method: This method displays the 2 tables: 'dept' and 'emp'
public static void FK_TwoTablesDisplay(DB2Connection conn,
DB2Transaction trans)
{
try
{
// Display the table 'dept'
Console.WriteLine();
Console.WriteLine(" SELECT * FROM dept");
Console.WriteLine(" DEPTNO DEPTNAME\n" +
" ------- --------------");
DB2Command cmd = conn.CreateCommand();
cmd.Transaction = trans;
cmd.CommandText = "SELECT * FROM dept";
DB2DataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
Console.WriteLine(" " +
reader.GetString(0).PadRight(7) + " " +
reader.GetString(1).PadRight(20));
}
reader.Close();
// Display the table 'emp'
Console.WriteLine();
Console.WriteLine(" SELECT * FROM emp");
Console.WriteLine(" EMPNO EMPNAME DEPT_NO\n" +
" ----- ---------- -------");
cmd.CommandText = "SELECT * FROM emp";
reader = cmd.ExecuteReader();
while (reader.Read())
{
Console.Write(" " +
reader.GetString(0).PadRight(5) + " " +
reader.GetString(1).PadRight(10));
if (reader.IsDBNull(2))
{
Console.Write(" -");
}
else
{
Console.Write(" " + reader.GetString(2).PadRight(3));
}
Console.WriteLine();
}
reader.Close();
}
catch (Exception e)
{
Console.WriteLine(e.Message);
}
} // FK_TwoTablesDisplay
// Helping method: This method drops the 2 tables: 'dept' and 'emp'
public static void FK_TwoTablesDrop(DB2Connection conn,
DB2Transaction trans)
{
try
{
// Drop table 'dept'
Console.WriteLine();
Console.WriteLine(" DROP TABLE dept");
DB2Command cmd = conn.CreateCommand();
cmd.CommandText = "DROP TABLE dept";
cmd.Transaction = trans;
cmd.ExecuteNonQuery();
// Drop table 'emp'
Console.WriteLine();
Console.WriteLine(" DROP TABLE emp");
cmd.CommandText = "DROP TABLE emp";
cmd.ExecuteNonQuery();
trans.Commit();
}
catch (Exception e)
{
Console.WriteLine(e.Message);
}
} // FK_TwoTablesDrop
// Helping method: This method creates a foreign key on the 'emp' table
// that references the 'dept' table
public static void FK_Create(String ruleClause,
DB2Connection conn,
DB2Transaction trans)
{
try
{
Console.WriteLine();
Console.WriteLine(" ALTER TABLE emp\n" +
" ADD CONSTRAINT fk_dept\n" +
" FOREIGN KEY(dept_no)\n" +
" REFERENCES dept(deptno)\n" +
" " + ruleClause);
DB2Command cmd = conn.CreateCommand();
cmd.Transaction = trans;
cmd.CommandText = "ALTER TABLE emp " +
" ADD CONSTRAINT fk_dept " +
" FOREIGN KEY(dept_no) " +
" REFERENCES dept(deptno) " +
ruleClause;
cmd.ExecuteNonQuery();
Console.WriteLine();
Console.WriteLine(" COMMIT");
trans.Commit();
}
catch (Exception e)
{
Console.WriteLine(e.Message);
}
} // FK_Create
// Helping method: This method drops a foreign key
public static void FK_Drop(DB2Connection conn, DB2Transaction trans)
{
try
{
Console.WriteLine();
Console.WriteLine(" ALTER TABLE emp DROP CONSTRAINT fk_dept");
DB2Command cmd = conn.CreateCommand();
cmd.CommandText = "ALTER TABLE emp DROP CONSTRAINT fk_dept";
cmd.Transaction = trans;
cmd.ExecuteNonQuery();
Console.WriteLine();
Console.WriteLine(" COMMIT");
trans.Commit();
}
catch (Exception e)
{
Console.WriteLine(e.Message);
}
} // FK_Drop
// This method demonstrates how to use a 'NOT NULL' constraint.
public static void Demo_NOT_NULL(DB2Connection conn, DB2Transaction trans)
{
Console.WriteLine();
Console.WriteLine(
"----------------------------------------------------------\n" +
"USE THE SQL STATEMENTS:\n" +
" CREATE TABLE\n" +
" INSERT\n" +
" DROP TABLE\n" +
"TO SHOW A 'NOT NULL' CONSTRAINT.");
// Create a table called emp_sal with a 'NOT NULL' constraint
try
{
Console.WriteLine();
Console.WriteLine(
" CREATE TABLE emp_sal(lastname VARCHAR(10) NOT NULL,\n" +
" firstname VARCHAR(10),\n" +
" salary DECIMAL(7, 2))");
DB2Command cmd = conn.CreateCommand();
cmd.CommandText =
"CREATE TABLE emp_sal(lastname VARCHAR(10) NOT NULL, " +
" firstname VARCHAR(10), " +
" salary DECIMAL(7, 2))";
cmd.Transaction = trans;
cmd.ExecuteNonQuery();
Console.WriteLine();
Console.WriteLine(" COMMIT");
trans.Commit();
}
catch (Exception e)
{
Console.WriteLine(e.Message);
}
// Insert a row in the table emp_sal with NULL as the lastname.
// This insert will fail with an expected error.
try
{
Console.WriteLine();
Console.WriteLine(
" INSERT INTO emp_sal VALUES(NULL, 'PHILIP', 17000.00)");
DB2Command cmd = conn.CreateCommand();
cmd.CommandText =
"INSERT INTO emp_sal VALUES(NULL, 'PHILIP', 17000.00) ";
cmd.ExecuteNonQuery();
}
catch (Exception e)
{
Console.WriteLine();
Console.WriteLine(
"**************** Expected Error ******************\n");
Console.WriteLine(e.Message);
Console.WriteLine(
"**************************************************");
}
// Drop the table emp_sal
try
{
Console.WriteLine();
Console.WriteLine(" DROP TABLE emp_sal");
DB2Command cmd = conn.CreateCommand();
cmd.CommandText = "DROP TABLE emp_sal";
cmd.ExecuteNonQuery();
}
catch (Exception e)
{
Console.WriteLine(e.Message);
}
} // Demo_NOT_NULL
// This method demonstrates how to use a 'UNIQUE' constraint.
public static void Demo_UNIQUE(DB2Connection conn, DB2Transaction trans)
{
Console.WriteLine();
Console.WriteLine(
"----------------------------------------------------------\n" +
"USE THE SQL STATEMENTS:\n" +
" CREATE TABLE\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
{
Console.WriteLine();
Console.WriteLine(
" 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))");
DB2Command cmd = conn.CreateCommand();
cmd.Transaction = trans;
cmd.CommandText =
"CREATE TABLE emp_sal(lastname VARCHAR(10) NOT NULL, " +
" firstname VARCHAR(10) NOT NULL, " +
" salary DECIMAL(7, 2), " +
" CONSTRAINT unique_cn " +
" UNIQUE(lastname, firstname))";
cmd.ExecuteNonQuery();
Console.WriteLine();
Console.WriteLine(" COMMIT");
trans.Commit();
}
catch (Exception e)
{
Console.WriteLine(e.Message);
}
// 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
{
Console.WriteLine();
Console.WriteLine(
" INSERT INTO emp_sal VALUES('SMITH', 'PHILIP', 17000.00),\n" +
" ('SMITH', 'PHILIP', 21000.00)");
DB2Command cmd = conn.CreateCommand();
cmd.CommandText =
"INSERT INTO emp_sal VALUES('SMITH', 'PHILIP', 17000.00), " +
" ('SMITH', 'PHILIP', 21000.00) ";
cmd.ExecuteNonQuery();
}
catch (Exception e)
{
Console.WriteLine();
Console.WriteLine(
"**************** Expected Error ******************\n");
Console.WriteLine(e.Message);
Console.WriteLine(
"**************************************************");
}
// Drop the 'UNIQUE' constraint on the table emp_sal
try
{
Console.WriteLine();
Console.WriteLine(
" ALTER TABLE emp_sal DROP CONSTRAINT unique_cn");
DB2Command cmd = conn.CreateCommand();
cmd.CommandText = "ALTER TABLE emp_sal DROP CONSTRAINT unique_cn ";
cmd.ExecuteNonQuery();
}
catch (Exception e)
{
Console.WriteLine(e.Message);
}
// Drop the table emp_sal
try
{
Console.WriteLine();
Console.WriteLine(" DROP TABLE emp_sal");
DB2Command cmd = conn.CreateCommand();
cmd.CommandText = "DROP TABLE emp_sal";
cmd.ExecuteNonQuery();
}
catch (Exception e)
{
Console.WriteLine(e.Message);
}
} // Demo_UNIQUE
// This method demonstrates how to use a 'PRIMARY KEY' constraint.
public static void Demo_PRIMARY_KEY(DB2Connection conn,
DB2Transaction trans)
{
Console.WriteLine();
Console.WriteLine(
"----------------------------------------------------------\n" +
"USE THE SQL STATEMENTS:\n" +
" CREATE TABLE\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
{
Console.WriteLine();
Console.WriteLine(
" 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))");
DB2Command cmd = conn.CreateCommand();
cmd.Transaction = trans;
cmd.CommandText =
"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))";
cmd.ExecuteNonQuery();
Console.WriteLine();
Console.WriteLine(" COMMIT");
trans.Commit();
}
catch (Exception e)
{
Console.WriteLine(e.Message);
}
// 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
{
Console.WriteLine();
Console.WriteLine(
" INSERT INTO emp_sal VALUES('SMITH', 'PHILIP', 17000.00),\n" +
" ('SMITH', 'PHILIP', 21000.00)");
DB2Command cmd = conn.CreateCommand();
cmd.CommandText =
"INSERT INTO emp_sal VALUES('SMITH', 'PHILIP', 17000.00)," +
" ('SMITH', 'PHILIP', 21000.00) ";
cmd.ExecuteNonQuery();
}
catch (Exception e)
{
Console.WriteLine();
Console.WriteLine(
"**************** Expected Error ******************\n");
Console.WriteLine(e.Message);
Console.WriteLine(
"**************************************************");
}
// Drop the 'PRIMARY KEY' constraint on the table emp_sal
try
{
Console.WriteLine();
Console.WriteLine(" ALTER TABLE emp_sal DROP CONSTRAINT pk_cn");
DB2Command cmd = conn.CreateCommand();
cmd.CommandText = "ALTER TABLE emp_sal DROP CONSTRAINT pk_cn";
cmd.ExecuteNonQuery();
}
catch (Exception e)
{
Console.WriteLine(e.Message);
}
// Drop the table emp_sal
try
{
Console.WriteLine();
Console.WriteLine(" DROP TABLE emp_sal");
DB2Command cmd = conn.CreateCommand();
cmd.CommandText = "DROP TABLE emp_sal";
cmd.ExecuteNonQuery();
}
catch (Exception e)
{
Console.WriteLine(e.Message);
}
} // Demo_PRIMARY_KEY
// This method demonstrates how to use a 'CHECK' constraint.
public static void Demo_CHECK(DB2Connection conn, DB2Transaction trans)
{
Console.WriteLine();
Console.WriteLine(
"----------------------------------------------------------\n" +
"USE THE SQL STATEMENTS:\n" +
" CREATE TABLE\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
{
Console.WriteLine();
Console.WriteLine(
" 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))");
DB2Command cmd = conn.CreateCommand();
cmd.Transaction = trans;
cmd.CommandText =
"CREATE TABLE emp_sal(lastname VARCHAR(10), " +
" firstname VARCHAR(10), " +
" salary DECIMAL(7, 2), " +
" CONSTRAINT check_cn " +
" CHECK(salary < 25000.00))";
cmd.ExecuteNonQuery();
Console.WriteLine();
Console.WriteLine(" COMMIT");
trans.Commit();
}
catch (Exception e)
{
Console.WriteLine(e.Message);
}
// 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
{
Console.WriteLine();
Console.WriteLine(
" INSERT INTO emp_sal VALUES('SMITH', 'PHILIP', 27000.00)");
DB2Command cmd = conn.CreateCommand();
cmd.CommandText = "INSERT INTO emp_sal" +
" VALUES('SMITH', 'PHILIP', 27000.00)";
cmd.ExecuteNonQuery();
}
catch (Exception e)
{
Console.WriteLine();
Console.WriteLine(
"**************** Expected Error ******************\n");
Console.WriteLine(e.Message);
Console.WriteLine(
"**************************************************");
}
// Drop the 'CHECK' constraint on the table emp_sal
try
{
Console.WriteLine();
Console.WriteLine(" ALTER TABLE emp_sal DROP CONSTRAINT check_cn");
DB2Command cmd = conn.CreateCommand();
cmd.CommandText = "ALTER TABLE emp_sal DROP CONSTRAINT check_cn";
cmd.ExecuteNonQuery();
}
catch (Exception e)
{
Console.WriteLine(e.Message);
}
// Drop the table emp_sal
try
{
Console.WriteLine();
Console.WriteLine(" DROP TABLE emp_sal");
DB2Command cmd = conn.CreateCommand();
cmd.CommandText = "DROP TABLE emp_sal";
cmd.ExecuteNonQuery();
}
catch (Exception e)
{
Console.WriteLine(e.Message);
}
} // Demo_CHECK
// This method demonstrates how to use a 'WITH DEFAULT' constraint.
public static void Demo_WITH_DEFAULT(DB2Connection conn,
DB2Transaction trans)
{
Console.WriteLine();
Console.WriteLine(
"----------------------------------------------------------\n" +
"USE THE SQL STATEMENTS:\n" +
" CREATE TABLE\n" +
" INSERT\n" +
" DROP TABLE\n" +
"TO SHOW A 'WITH DEFAULT' CONSTRAINT.");
// Create a table called emp_sal with a 'WITH DEFAULT' constraint
try
{
Console.WriteLine();
Console.WriteLine(
" CREATE TABLE emp_sal(lastname VARCHAR(10),\n" +
" firstname VARCHAR(10),\n" +
" salary DECIMAL(7, 2)" +
" WITH DEFAULT 17000.00)");
DB2Command cmd = conn.CreateCommand();
cmd.Transaction = trans;
cmd.CommandText =
"CREATE TABLE emp_sal(lastname VARCHAR(10), " +
" firstname VARCHAR(10), " +
" salary DECIMAL(7, 2) WITH DEFAULT 17000.00)";
cmd.ExecuteNonQuery();
Console.WriteLine();
Console.WriteLine(" COMMIT");
trans.Commit();
}
catch (Exception e)
{
Console.WriteLine(e.Message);
}
// Insert three rows into the table emp_sal, without any value for 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
{
Console.WriteLine();
Console.WriteLine(" INSERT INTO emp_sal(lastname, firstname)\n" +
" VALUES('SMITH', 'PHILIP'),\n" +
" ('PARKER', 'JOHN'),\n" +
" ('PEREZ', 'MARIA')");
DB2Command cmd = conn.CreateCommand();
cmd.CommandText = "INSERT INTO emp_sal(lastname, firstname) " +
" VALUES('SMITH' , 'PHILIP'), " +
" ('PARKER', 'JOHN'), " +
" ('PEREZ' , 'MARIA') ";
cmd.ExecuteNonQuery();
}
catch (Exception e)
{
Console.WriteLine(e.Message);
}
// Retrieve and display the data in the table emp_sal
try
{
Console.WriteLine();
Console.WriteLine(" SELECT * FROM emp_sal");
Console.WriteLine(" FIRSTNAME LASTNAME SALARY\n" +
" ---------- ---------- --------");
DB2Command cmd = conn.CreateCommand();
cmd.CommandText = "SELECT * FROM emp_sal";
DB2DataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
Console.WriteLine(
" " +
reader.GetString(1).PadRight(10) + " " +
reader.GetString(0).PadRight(10) + " " +
reader.GetDecimal(2).ToString().PadRight(10));
}
reader.Close();
}
catch (Exception e)
{
Console.WriteLine(e.Message);
}
// Drop the table emp_sal
try
{
Console.WriteLine();
Console.WriteLine(" DROP TABLE emp_sal");
DB2Command cmd = conn.CreateCommand();
cmd.CommandText = "DROP TABLE emp_sal";
cmd.ExecuteNonQuery();
}
catch (Exception e)
{
Console.WriteLine(e.Message);
}
} // Demo_WITH_DEFAULT
// This method demonstrates how to insert into a foreign key
public static void Demo_FK_OnInsertShow(DB2Connection conn,
DB2Transaction trans)
{
Console.WriteLine();
Console.WriteLine(
"----------------------------------------------------------\n" +
"USE THE SQL STATEMENTS:\n" +
" ALTER TABLE\n" +
" INSERT\n" +
"TO SHOW HOW TO INSERT INTO A FOREIGN KEY.");
// Display the initial content of the 'dept' and 'emp' table
FK_TwoTablesDisplay(conn, trans);
// Create a foreign key on the 'emp' table that reference the 'dept'
// table
FK_Create("", conn, trans);
trans = conn.BeginTransaction();
// Insert an entry into the parent table, 'dept'
try
{
Console.WriteLine();
Console.WriteLine(" INSERT INTO dept VALUES('D00', 'SALES')");
DB2Command cmd = conn.CreateCommand();
cmd.Transaction = trans;
cmd.CommandText = "INSERT INTO dept VALUES('D00', 'SALES')";
cmd.ExecuteNonQuery();
}
catch (Exception e)
{
Console.WriteLine(e.Message);
}
// Insert an entry into the child table, 'emp'
try
{
Console.WriteLine();
Console.WriteLine(
" INSERT INTO emp VALUES('0080', 'Pearce', 'E03')");
DB2Command cmd = conn.CreateCommand();
cmd.Transaction = trans;
cmd.CommandText =
"INSERT INTO emp VALUES('0080', 'Pearce', 'E03')";
cmd.ExecuteNonQuery();
}
catch (Exception e)
{
Console.WriteLine();
Console.WriteLine(
"**************** Expected Error ******************\n");
Console.WriteLine(e.Message);
Console.WriteLine(
"**************************************************");
}
// Display the final content of the 'dept' and 'emp' table
FK_TwoTablesDisplay(conn, trans);
// Roll back the transaction
try
{
Console.WriteLine();
Console.WriteLine(" ROLLBACK");
trans.Rollback();
}
catch (Exception e)
{
Console.WriteLine(e.Message);
}
// Drop the foreign key
trans = conn.BeginTransaction();
FK_Drop(conn, trans);
} // Demo_FK_OnInsertShow
// This method demonstrates how to use an 'ON UPDATE NO ACTION'
// foreign key
public static void Demo_FK_ON_UPDATE_NO_ACTION(DB2Connection conn,
DB2Transaction trans)
{
Console.WriteLine();
Console.WriteLine(
"----------------------------------------------------------\n" +
"USE THE SQL STATEMENTS:\n" +
" ALTER TABLE\n" +
" UPDATE\n" +
"TO SHOW HOW TO USE AN 'ON UPDATE NO ACTION' FOREIGN KEY.");
// Display the initial content of the 'dept' and 'emp' table
FK_TwoTablesDisplay(conn, trans);
// Create an 'ON UPDATE NO ACTION' foreign key
FK_Create("ON UPDATE NO ACTION", conn, trans);
trans = conn.BeginTransaction();
// Update parent table
try
{
Console.WriteLine();
Console.WriteLine(
" UPDATE dept SET deptno = 'E01' WHERE deptno = 'A00'");
DB2Command cmd = conn.CreateCommand();
cmd.Transaction = trans;
cmd.CommandText =
"UPDATE dept SET deptno = 'E01' WHERE deptno = 'A00' ";
cmd.ExecuteNonQuery();
}
catch (Exception e)
{
Console.WriteLine();
Console.WriteLine(
"**************** Expected Error ******************\n");
Console.WriteLine(e.Message);
Console.WriteLine(
"**************************************************");
}
// Update the parent table, 'dept'
try
{
Console.WriteLine();
Console.WriteLine(
" UPDATE dept\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'");
DB2Command cmd = conn.CreateCommand();
cmd.Transaction = trans;
cmd.CommandText =
"UPDATE dept " +
" SET deptno = CASE " +
" WHEN deptno = 'A00' THEN 'B00' " +
" WHEN deptno = 'B00' THEN 'A00' " +
" END " +
" WHERE deptno = 'A00' OR deptno = 'B00' ";
cmd.ExecuteNonQuery();
}
catch (Exception e)
{
Console.WriteLine(e.Message);
}
// Update the child table, 'emp'
try
{
Console.WriteLine();
Console.WriteLine(
" UPDATE emp SET dept_no = 'G11' WHERE empname = 'Wheeler'");
DB2Command cmd = conn.CreateCommand();
cmd.Transaction = trans;
cmd.CommandText =
"UPDATE emp SET dept_no = 'G11' WHERE empname = 'Wheeler' ";
cmd.ExecuteNonQuery();
}
catch (Exception e)
{
Console.WriteLine();
Console.WriteLine(
"**************** Expected Error ******************\n");
Console.WriteLine(e.Message);
Console.WriteLine(
"**************************************************");
}
// Display the final content of the 'dept' and 'emp' table
FK_TwoTablesDisplay(conn, trans);
// Roll back the transaction
try
{
Console.WriteLine();
Console.WriteLine(" ROLLBACK");
trans.Rollback();
}
catch (Exception e)
{
Console.WriteLine(e.Message);
}
// Drop the foreign key
trans = conn.BeginTransaction();
FK_Drop(conn, trans);
} // Demo_FK_ON_UPDATE_NO_ACTION
// This method demonstrates how to use an 'ON UPDATE RESTRICT'
// foreign key
public static void Demo_FK_ON_UPDATE_RESTRICT(DB2Connection conn,
DB2Transaction trans)
{
Console.WriteLine();
Console.WriteLine(
"----------------------------------------------------------\n" +
"USE THE SQL STATEMENTS:\n" +
" ALTER TABLE\n" +
" UPDATE\n" +
"TO SHOW HOW TO USE AN 'ON UPDATE RESTRICT' FOREIGN KEY.");
// Display the initial content of the 'dept' and 'emp' table
FK_TwoTablesDisplay(conn, trans);
// Create an 'ON UPDATE RESTRICT' foreign key
FK_Create("ON UPDATE RESTRICT", conn, trans);
trans = conn.BeginTransaction();
// Update the parent table, 'dept', with data that violates the 'ON
// UPDATE RESTRICT' foreign key. An error is expected to be returned.
try
{
Console.WriteLine();
Console.WriteLine(
" UPDATE dept SET deptno = 'E01' WHERE deptno = 'A00'");
DB2Command cmd = conn.CreateCommand();
cmd.Transaction = trans;
cmd.CommandText =
"UPDATE dept SET deptno = 'E01' WHERE deptno = 'A00' ";
cmd.ExecuteNonQuery();
}
catch (Exception e)
{
Console.WriteLine();
Console.WriteLine(
"**************** Expected Error ******************\n");
Console.WriteLine(e.Message);
Console.WriteLine(
"**************************************************");
}
// Update the parent table, 'dept', with data that violates the 'ON
// UPDATE RESTRICT' foreign key. An error is expected to be returned.
try
{
Console.WriteLine();
Console.WriteLine(
" UPDATE dept\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'");
DB2Command cmd = conn.CreateCommand();
cmd.Transaction = trans;
cmd.CommandText =
"UPDATE dept " +
" SET deptno = CASE " +
" WHEN deptno = 'A00' THEN 'B00' " +
" WHEN deptno = 'B00' THEN 'A00' " +
" END " +
" WHERE deptno = 'A00' OR deptno = 'B00' ";
cmd.ExecuteNonQuery();
}
catch (Exception e)
{
Console.WriteLine();
Console.WriteLine(
"**************** Expected Error ******************\n");
Console.WriteLine(e.Message);
Console.WriteLine(
"**************************************************");
}
// Update the child table, 'emp', with data that violates the 'ON
// UPDATE RESTRICT' foreign key. An error is expected to be returned.
try
{
Console.WriteLine();
Console.WriteLine(
" UPDATE emp SET dept_no = 'G11' WHERE empname = 'Wheeler'");
DB2Command cmd = conn.CreateCommand();
cmd.Transaction = trans;
cmd.CommandText =
"UPDATE emp SET dept_no = 'G11' WHERE empname = 'Wheeler' ";
cmd.ExecuteNonQuery();
}
catch (Exception e)
{
Console.WriteLine();
Console.WriteLine(
"**************** Expected Error ******************\n");
Console.WriteLine(e.Message);
Console.WriteLine(
"**************************************************");
}
// Display the final content of the 'dept' and 'emp' table
FK_TwoTablesDisplay(conn, trans);
// Roll back the transaction
try
{
Console.WriteLine();
Console.WriteLine(" ROLLBACK");
trans.Rollback();
}
catch (Exception e)
{
Console.WriteLine(e.Message);
}
// Drop the foreign key
trans = conn.BeginTransaction();
FK_Drop(conn,trans);
} // Demo_FK_ON_UPDATE_RESTRICT
// This method demonstrates how to use an 'ON DELETE CASCADE' foreign key
public static void Demo_FK_ON_DELETE_CASCADE(DB2Connection conn,
DB2Transaction trans)
{
Console.WriteLine();
Console.WriteLine(
"----------------------------------------------------------\n" +
"USE THE SQL STATEMENTS:\n" +
" ALTER TABLE\n" +
" DELETE\n" +
"TO SHOW HOW TO USE AN 'ON DELETE CASCADE' FOREIGN KEY.");
// Display the initial content of the 'dept' and 'emp' table
FK_TwoTablesDisplay(conn, trans);
// Create an 'ON DELETE CASCADE' foreign key
FK_Create("ON DELETE CASCADE", conn, trans);
trans = conn.BeginTransaction();
// Delete from the parent table, 'dept'
try
{
Console.WriteLine();
Console.WriteLine(" DELETE FROM dept WHERE deptno = 'C00'");
DB2Command cmd = conn.CreateCommand();
cmd.Transaction = trans;
cmd.CommandText = "DELETE FROM dept WHERE deptno = 'C00' ";
cmd.ExecuteNonQuery();
}
catch (Exception e)
{
Console.WriteLine(e.Message);
}
// Display the content of the 'dept' and 'emp' table
FK_TwoTablesDisplay(conn, trans);
// Delete from the child table, 'emp'
try
{
Console.WriteLine();
Console.WriteLine(" DELETE FROM emp WHERE empname = 'Wheeler'");
DB2Command cmd = conn.CreateCommand();
cmd.Transaction = trans;
cmd.CommandText = "DELETE FROM emp WHERE empname = 'Wheeler' ";
cmd.ExecuteNonQuery();
}
catch (Exception e)
{
Console.WriteLine(e.Message);
}
// Display the final content of the 'dept' and 'emp' table
FK_TwoTablesDisplay(conn, trans);
// Roll back the transaction
try
{
Console.WriteLine();
Console.WriteLine(" ROLLBACK");
trans.Rollback();
}
catch (Exception e)
{
Console.WriteLine(e.Message);
}
// Drop the foreign key
trans = conn.BeginTransaction();
FK_Drop(conn,trans);
} // Demo_FK_ON_DELETE_CASCADE
// This method demonstrates how to use an 'ON DELETE SET NULL'
// foreign key
public static void Demo_FK_ON_DELETE_SET_NULL(DB2Connection conn,
DB2Transaction trans)
{
Console.WriteLine();
Console.WriteLine(
"----------------------------------------------------------\n" +
"USE THE SQL STATEMENTS:\n" +
" ALTER TABLE\n" +
" DELETE\n" +
"TO SHOW HOW TO USE AN 'ON DELETE SET NULL' FOREIGN KEY.");
// Display the initial content of the 'dept' and 'emp' table
FK_TwoTablesDisplay(conn, trans);
// Create an 'ON DELETE SET NULL' foreign key
FK_Create("ON DELETE SET NULL", conn, trans);
trans = conn.BeginTransaction();
// Delete from the parent table, 'dept'
try
{
Console.WriteLine();
Console.WriteLine(" DELETE FROM dept WHERE deptno = 'C00'");
DB2Command cmd = conn.CreateCommand();
cmd.Transaction = trans;
cmd.CommandText = "DELETE FROM dept WHERE deptno = 'C00' ";
cmd.ExecuteNonQuery();
}
catch (Exception e)
{
Console.WriteLine(e.Message);
}
// Display the content of the 'dept' and 'emp' table
FK_TwoTablesDisplay(conn, trans);
// Delete from the child table, 'emp'
try
{
Console.WriteLine();
Console.WriteLine(" DELETE FROM emp WHERE empname = 'Wheeler'");
DB2Command cmd = conn.CreateCommand();
cmd.Transaction = trans;
cmd.CommandText = "DELETE FROM emp WHERE empname = 'Wheeler' ";
cmd.ExecuteNonQuery();
}
catch (Exception e)
{
Console.WriteLine(e.Message);
}
// Display the final content of the 'dept' and 'emp' table
FK_TwoTablesDisplay(conn, trans);
// Roll back the transaction
try
{
Console.WriteLine();
Console.WriteLine(" ROLLBACK");
trans.Rollback();
}
catch (Exception e)
{
Console.WriteLine(e.Message);
}
// Drop the foreign key
trans = conn.BeginTransaction();
FK_Drop(conn,trans);
} // Demo_FK_ON_DELETE_SET_NULL
// This method demonstrates how to use an 'ON DELETE NO ACTION'
// foreign key
public static void Demo_FK_ON_DELETE_NO_ACTION(DB2Connection conn,
DB2Transaction trans)
{
Console.WriteLine();
Console.WriteLine(
"----------------------------------------------------------\n" +
"USE THE SQL STATEMENTS:\n" +
" ALTER TABLE\n" +
" DELETE\n" +
"TO SHOW HOW TO USE AN 'ON DELETE NO ACTION' FOREIGN KEY.");
// Display the initial content of the 'dept' and 'emp' table
FK_TwoTablesDisplay(conn, trans);
// Create an 'ON DELETE NO ACTION' foreign key
FK_Create("ON DELETE NO ACTION", conn, trans);
trans = conn.BeginTransaction();
// Delete from the parent table, 'dept'
try
{
Console.WriteLine();
Console.WriteLine(" DELETE FROM dept WHERE deptno = 'C00'");
DB2Command cmd = conn.CreateCommand();
cmd.Transaction = trans;
cmd.CommandText = "DELETE FROM dept WHERE deptno = 'C00' ";
cmd.ExecuteNonQuery();
}
catch (Exception e)
{
Console.WriteLine();
Console.WriteLine(
"**************** Expected Error ******************\n");
Console.WriteLine(e.Message);
Console.WriteLine(
"**************************************************");
}
// Delete from the child table, 'emp'
try
{
Console.WriteLine();
Console.WriteLine(" DELETE FROM emp WHERE empname = 'Wheeler'");
DB2Command cmd = conn.CreateCommand();
cmd.Transaction = trans;
cmd.CommandText = "DELETE FROM emp WHERE empname = 'Wheeler' ";
cmd.ExecuteNonQuery();
}
catch (Exception e)
{
Console.WriteLine(e.Message);
}
// Display the final content of the 'dept' and 'emp' table
FK_TwoTablesDisplay(conn, trans);
// Roll back the transaction
try
{
Console.WriteLine();
Console.WriteLine(" ROLLBACK");
trans.Rollback();
}
catch (Exception e)
{
Console.WriteLine(e.Message);
}
// Drop the foreign key
trans = conn.BeginTransaction();
FK_Drop(conn,trans);
} // Demo_FK_ON_DELETE_NO_ACTION
} // TbConstr