'****************************************************************************
' (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.vb
'
' 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.vb file with bldapp.bat by entering the following
' at the command prompt:
'
' bldapp TbConstr
'
' or compile TbConstr.vb 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
'
'****************************************************************************
Imports System
Imports System.Data
Imports Microsoft.VisualBasic
Imports IBM.Data.DB2
Public Class TbConstr
Public Shared Sub Main(args() As String)
' Declare a DB2Connection and a DB2Transaction
Dim conn As DB2Connection
Dim trans As DB2Transaction
Try
Console.WriteLine()
Console.WriteLine( _
"THIS SAMPLE SHOWS HOW TO CREATE, USE AND DROP CONSTRAINTS.")
Console.WriteLine()
' Connect to a database
Console.WriteLine(" 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( _
"----------------------------------------------------------" & _
vbNewLine & _
"#####################################################" & _
vbNewLine & _
"# Create tables for FOREIGN KEY sample functions #" & _
vbNewLine & _
"#####################################################")
' 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( _
"----------------------------------------------------------" & _
vbNewLine & _
"########################################################" & _
vbNewLine & _
"# Drop tables created for FOREIGN KEY sample functions #" & _
vbNewLine & _
"########################################################")
' Drop the tables created for the FOREIGN KEY sample functions
trans = conn.BeginTransaction()
FK_TwoTablesDrop(conn,trans)
' Disconnect from the database
Console.WriteLine(vbNewLine & _
" Disconnect from the database.")
conn.Close()
Catch e As Exception
Console.WriteLine(e.Message)
If Not (conn is System.DBNull.value) Then
conn.Close()
End If
End Try
End Sub ' Main
' This method establishes a connection to a database
Public Shared Function ConnectDb(argv() As String) As DB2Connection
Dim server As String
Dim dbalias As String
Dim userId As String
Dim password As String
Dim portNumber As Int32 = -1
Dim connectString As String
If (argv.Length > 5) Then
Throw new Exception( _
"Usage: prog_name [dbAlias] [userId passwd]" & vbNewLine & _
" prog_name [dbAlias] server portNum userId passwd")
Else
If (argv.Length = 1) Then
If( String.Compare(argv(0),"?") = 0 Or _
String.Compare(argv(0),"-?") = 0 Or _
String.Compare(argv(0),"/?") = 0 Or _
String.Compare(argv(0),"-h",true) = 0 Or _
String.Compare(argv(0),"/h",true) = 0 Or _
String.Compare(argv(0),"-help",true) = 0 Or _
String.Compare(argv(0),"/help",true) = 0 ) Then
Throw new Exception( _
"Usage: prog_name [dbAlias] [userId passwd]" & vbNewLine & _
" prog_name [dbAlias] server portNum userId passwd")
End If
End If
End If
Select Case (argv.Length)
Case 0 ' Use all defaults
dbalias = "sample"
userId = ""
password = ""
Case 1 ' dbAlias specified
dbalias = argv(0)
userId = ""
password = ""
Case 2 ' userId & passwd specified
dbalias = "sample"
userId = argv(0)
password = argv(1)
Case 3 ' dbAlias, userId & passwd specified
dbalias = argv(0)
userId = argv(1)
password = argv(2)
Case 4 ' use default dbAlias
dbalias = "sample"
server = argv(0)
portNumber = Convert.ToInt32(argv(1))
userId = argv(2)
password = argv(3)
Case 5 ' everything specified
dbalias = argv(0)
server = argv(1)
portNumber = Convert.ToInt32(argv(2))
userId = argv(3)
password = argv(4)
End Select
If(portNumber = -1) Then
connectString = "Database=" & dbalias
Else
connectString = "Server=" & server & ":" & portNumber & _
";Database=" & dbalias
End If
If (userId <> "")
connectString += ";UID=" & userId & ";PWD=" & password
End If
Dim conn As DB2Connection = new DB2Connection(connectString)
conn.Open()
Console.WriteLine(" Connected to the " & dbalias & " database")
Return conn
End Function ' ConnectDb
' Helping method: This method creates two foreign keys
Public Shared Sub FK_TwoTablesCreate(conn As DB2Connection, _
trans As DB2Transaction)
Try
' Create table 'dept'
Console.WriteLine()
Console.WriteLine( _
" CREATE TABLE dept(deptno CHAR(3) NOT NULL," & vbNewLine & _
" deptname VARCHAR(20)," & vbNewLine & _
" CONSTRAINT pk_dept" & vbNewLine & _
" PRIMARY KEY(deptno))")
Dim cmd As DB2Command = 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')," & vbNewLine & _
" ('B00', 'DEVELOPMENT')," & vbNewLine & _
" ('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)," & vbNewLine & _
" empname VARCHAR(10)," & vbNewLine & _
" 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')," & vbNewLine & _
" ('0020', 'Ngan', 'B00')," & vbNewLine & _
" ('0030', 'Lu', 'B00')," & vbNewLine & _
" ('0040', 'Wheeler', 'B00')," & vbNewLine & _
" ('0050', 'Burke', 'C00')," & vbNewLine & _
" ('0060', 'Edwards', 'C00')," & vbNewLine & _
" ('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 e As Exception
Console.WriteLine(e.Message)
End Try
End Sub ' FK_TwoTablesCreate
' Helping method: This method displays the 2 tables: 'dept' and 'emp'
Public Shared Sub FK_TwoTablesDisplay(conn As DB2Connection, _
trans As DB2Transaction)
Try
' Display the table 'dept'
Console.WriteLine()
Console.WriteLine(" SELECT * FROM dept")
Console.WriteLine(" DEPTNO DEPTNAME" & vbNewLine & _
" ------- --------------")
Dim cmd As DB2Command = conn.CreateCommand()
cmd.Transaction = trans
cmd.CommandText = "SELECT * FROM dept"
Dim reader As DB2DataReader = cmd.ExecuteReader()
Do While reader.Read()
Console.WriteLine(" " & _
reader.GetString(0).PadRight(7) & " " & _
reader.GetString(1).PadRight(20))
Loop
reader.Close()
' Display the table 'emp'
Console.WriteLine()
Console.WriteLine(" SELECT * FROM emp")
Console.WriteLine(" EMPNO EMPNAME DEPT_NO" & vbNewLine & _
" ----- ---------- -------")
cmd.CommandText = "SELECT * FROM emp"
reader = cmd.ExecuteReader()
Do While reader.Read()
Console.Write(" " & _
reader.GetString(0).PadRight(5) & " " & _
reader.GetString(1).PadRight(10))
If reader.IsDBNull(2) Then
Console.Write(" -")
Else
Console.Write(" " & reader.GetString(2).PadRight(3))
End If
Console.WriteLine()
Loop
reader.Close()
Catch e As Exception
Console.WriteLine(e.Message)
End Try
End Sub ' FK_TwoTablesDisplay
' Helping method: This method drops the 2 tables: 'dept' and 'emp'
Public Shared Sub FK_TwoTablesDrop(conn As DB2Connection, _
trans As DB2Transaction)
Try
' Drop table 'dept'
Console.WriteLine()
Console.WriteLine(" DROP TABLE dept")
Dim cmd As DB2Command = 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 e As Exception
Console.WriteLine(e.Message)
End Try
End Sub ' FK_TwoTablesDrop
' Helping method: This method creates a foreign key on the 'emp' table
' that references the 'dept' table
Public Shared Sub FK_Create(ruleClause As String, _
conn As DB2Connection, _
trans As DB2Transaction)
Try
Console.WriteLine()
Console.WriteLine(" ALTER TABLE emp" & vbNewLine & _
" ADD CONSTRAINT fk_dept" & vbNewLine & _
" FOREIGN KEY(dept_no)" & vbNewLine & _
" REFERENCES dept(deptno)" & vbNewLine & _
" " & ruleClause)
Dim cmd As DB2Command = 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 e As Exception
Console.WriteLine(e.Message)
End Try
End Sub ' FK_Create
' Helping method: This method drops a foreign key
Public Shared Sub FK_Drop(conn As DB2Connection, trans As DB2Transaction)
Try
Console.WriteLine()
Console.WriteLine(" ALTER TABLE emp DROP CONSTRAINT fk_dept")
Dim cmd As DB2Command = conn.CreateCommand()
cmd.CommandText = "ALTER TABLE emp DROP CONSTRAINT fk_dept"
cmd.Transaction = trans
cmd.ExecuteNonQuery()
Console.WriteLine()
Console.WriteLine(" COMMIT")
trans.Commit()
Catch e As Exception
Console.WriteLine(e.Message)
End Try
End Sub ' FK_Drop
' This method demonstrates how to use a 'NOT NULL' constraint.
Public Shared Sub Demo_NOT_NULL(conn As DB2Connection, _
trans As DB2Transaction)
Console.WriteLine()
Console.WriteLine( _
"----------------------------------------------------------" & _
vbNewLine & _
"USE THE SQL STATEMENTS:" & vbNewLine & _
" CREATE TABLE" & vbNewLine & _
" INSERT" & vbNewLine & _
" DROP TABLE" & vbNewLine & _
"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," & _
vbNewLine & _
" firstname VARCHAR(10)," & vbNewLine & _
" salary DECIMAL(7, 2))")
Dim cmd As DB2Command = 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 e As Exception
Console.WriteLine(e.Message)
End Try
' 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)")
Dim cmd As DB2Command = conn.CreateCommand()
cmd.CommandText = _
"INSERT INTO emp_sal VALUES(NULL, 'PHILIP', 17000.00) "
cmd.ExecuteNonQuery()
Catch e As Exception
Console.WriteLine()
Console.WriteLine( _
"**************** Expected Error ******************" & vbNewLine)
Console.WriteLine(e.Message)
Console.WriteLine( _
"**************************************************")
End Try
' Drop the table emp_sal
Try
Console.WriteLine()
Console.WriteLine(" DROP TABLE emp_sal")
Dim cmd As DB2Command = conn.CreateCommand()
cmd.CommandText = "DROP TABLE emp_sal"
cmd.ExecuteNonQuery()
Catch e As Exception
Console.WriteLine(e.Message)
End Try
End Sub ' Demo_NOT_NULL
' This method demonstrates how to use a 'UNIQUE' constraint.
Public Shared Sub Demo_UNIQUE(conn As DB2Connection, _
trans As DB2Transaction)
Console.WriteLine()
Console.WriteLine( _
"----------------------------------------------------------" & _
vbNewLine & _
"USE THE SQL STATEMENTS:" & vbNewLine & _
" CREATE TABLE" & vbNewLine & _
" INSERT" & vbNewLine & _
" ALTER TABLE" & vbNewLine & _
" DROP TABLE" & vbNewLine & _
"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," & _
vbNewLine & _
" firstname VARCHAR(10) NOT NULL," & _
vbNewLine & _
" salary DECIMAL(7, 2)," & vbNewLine & _
" CONSTRAINT unique_cn" & vbNewLine & _
" UNIQUE(lastname, firstname))")
Dim cmd As DB2Command = 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 e As Exception
Console.WriteLine(e.Message)
End Try
' 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)," & _
vbNewLine & _
" ('SMITH', 'PHILIP', 21000.00)")
Dim cmd As DB2Command = conn.CreateCommand()
cmd.CommandText = _
"INSERT INTO emp_sal VALUES('SMITH', 'PHILIP', 17000.00), " & _
" ('SMITH', 'PHILIP', 21000.00) "
cmd.ExecuteNonQuery()
Catch e As Exception
Console.WriteLine()
Console.WriteLine( _
"**************** Expected Error ******************" & vbNewLine)
Console.WriteLine(e.Message)
Console.WriteLine( _
"**************************************************")
End Try
' Drop the 'UNIQUE' constraint on the table emp_sal
Try
Console.WriteLine()
Console.WriteLine( _
" ALTER TABLE emp_sal DROP CONSTRAINT unique_cn")
Dim cmd As DB2Command = conn.CreateCommand()
cmd.CommandText = "ALTER TABLE emp_sal DROP CONSTRAINT unique_cn "
cmd.ExecuteNonQuery()
Catch e As Exception
Console.WriteLine(e.Message)
End Try
' Drop the table emp_sal
Try
Console.WriteLine()
Console.WriteLine(" DROP TABLE emp_sal")
Dim cmd As DB2Command = conn.CreateCommand()
cmd.CommandText = "DROP TABLE emp_sal"
cmd.ExecuteNonQuery()
Catch e As Exception
Console.WriteLine(e.Message)
End Try
End Sub ' Demo_UNIQUE
' This method demonstrates how to use a 'PRIMARY KEY' constraint.
Public Shared Sub Demo_PRIMARY_KEY(conn As DB2Connection, _
trans As DB2Transaction)
Console.WriteLine()
Console.WriteLine( _
"----------------------------------------------------------" & _
vbNewLine & _
"USE THE SQL STATEMENTS:" & vbNewLine & _
" CREATE TABLE" & vbNewLine & _
" INSERT" & vbNewLine & _
" ALTER TABLE" & vbNewLine & _
" DROP TABLE" & vbNewLine & _
"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," & _
vbNewLine & _
" firstname VARCHAR(10) NOT NULL," & _
vbNewLine & _
" salary DECIMAL(7, 2)," & vbNewLine & _
" CONSTRAINT pk_cn" & vbNewLine & _
" PRIMARY KEY(lastname, firstname))")
Dim cmd As DB2Command = 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 e As Exception
Console.WriteLine(e.Message)
End try
' 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)," & _
vbNewLine & _
" ('SMITH', 'PHILIP', 21000.00)")
Dim cmd As DB2Command = conn.CreateCommand()
cmd.CommandText = _
"INSERT INTO emp_sal VALUES('SMITH', 'PHILIP', 17000.00)," & _
" ('SMITH', 'PHILIP', 21000.00) "
cmd.ExecuteNonQuery()
Catch e As Exception
Console.WriteLine()
Console.WriteLine( _
"**************** Expected Error ******************" & vbNewLine)
Console.WriteLine(e.Message)
Console.WriteLine( _
"**************************************************")
End Try
' Drop the 'PRIMARY KEY' constraint on the table emp_sal
Try
Console.WriteLine()
Console.WriteLine(" ALTER TABLE emp_sal DROP CONSTRAINT pk_cn")
Dim cmd As DB2Command = conn.CreateCommand()
cmd.CommandText = "ALTER TABLE emp_sal DROP CONSTRAINT pk_cn"
cmd.ExecuteNonQuery()
Catch e As Exception
Console.WriteLine(e.Message)
End Try
' Drop the table emp_sal
Try
Console.WriteLine()
Console.WriteLine(" DROP TABLE emp_sal")
Dim cmd As DB2Command = conn.CreateCommand()
cmd.CommandText = "DROP TABLE emp_sal"
cmd.ExecuteNonQuery()
Catch e As Exception
Console.WriteLine(e.Message)
End Try
End Sub ' Demo_PRIMARY_KEY
' This method demonstrates how to use a 'CHECK' constraint.
Public Shared Sub Demo_CHECK(conn As DB2Connection, _
trans As DB2Transaction)
Console.WriteLine()
Console.WriteLine( _
"----------------------------------------------------------" & _
vbNewLine & _
"USE THE SQL STATEMENTS:" & vbNewLine & _
" CREATE TABLE" & vbNewLine & _
" INSERT" & vbNewLine & _
" ALTER TABLE" & vbNewLine & _
" DROP TABLE" & vbNewLine & _
"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)," & vbNewLine & _
" firstname VARCHAR(10)," & vbNewLine & _
" salary DECIMAL(7, 2)," & vbNewLine & _
" CONSTRAINT check_cn" & vbNewLine & _
" CHECK(salary < 25000.00))")
Dim cmd As DB2Command = 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 e As Exception
Console.WriteLine(e.Message)
End Try
' 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)")
Dim cmd As DB2Command = conn.CreateCommand()
cmd.CommandText = _
"INSERT INTO emp_sal VALUES('SMITH', 'PHILIP', 27000.00)"
cmd.ExecuteNonQuery()
Catch e As Exception
Console.WriteLine()
Console.WriteLine( _
"**************** Expected Error ******************" & vbNewLine)
Console.WriteLine(e.Message)
Console.WriteLine( _
"**************************************************")
End Try
' Drop the 'CHECK' constraint on the table emp_sal
Try
Console.WriteLine()
Console.WriteLine(" ALTER TABLE emp_sal DROP CONSTRAINT check_cn")
Dim cmd As DB2Command = conn.CreateCommand()
cmd.CommandText = _
"ALTER TABLE emp_sal DROP CONSTRAINT check_cn"
cmd.ExecuteNonQuery()
Catch e As Exception
Console.WriteLine(e.Message)
End Try
' Drop the table emp_sal
Try
Console.WriteLine()
Console.WriteLine(" DROP TABLE emp_sal")
Dim cmd As DB2Command = conn.CreateCommand()
cmd.CommandText = "DROP TABLE emp_sal"
cmd.ExecuteNonQuery()
Catch e As Exception
Console.WriteLine(e.Message)
End Try
End Sub ' Demo_CHECK
' This method demonstrates how to use a 'WITH DEFAULT' constraint.
Public Shared Sub Demo_WITH_DEFAULT(conn As DB2Connection, _
trans As DB2Transaction)
Console.WriteLine()
Console.WriteLine( _
"----------------------------------------------------------" & _
vbNewLine & _
"USE THE SQL STATEMENTS:" & vbNewLine & _
" CREATE TABLE" & vbNewLine & _
" INSERT" & vbNewLine & _
" DROP TABLE" & vbNewLine & _
"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)," & vbNewLine & _
" firstname VARCHAR(10)," & vbNewLine & _
" salary DECIMAL(7, 2) " & _
" WITH DEFAULT 17000.00)")
Dim cmd As DB2Command = 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 e As Exception
Console.WriteLine(e.Message)
End Try
' 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)" & vbNewLine & _
" VALUES('SMITH', 'PHILIP')," & vbNewLine & _
" ('PARKER', 'JOHN')," & vbNewLine & _
" ('PEREZ', 'MARIA')")
Dim cmd As DB2Command = conn.CreateCommand()
cmd.CommandText = _
"INSERT INTO emp_sal(lastname, firstname) " & _
" VALUES('SMITH' , 'PHILIP'), " & _
" ('PARKER', 'JOHN'), " & _
" ('PEREZ' , 'MARIA') "
cmd.ExecuteNonQuery()
Catch e As Exception
Console.WriteLine(e.Message)
End Try
' Retrieve and display the data in the table emp_sal
Try
Console.WriteLine()
Console.WriteLine(" SELECT * FROM emp_sal")
Console.WriteLine(" FIRSTNAME LASTNAME SALARY" & vbNewLine & _
" ---------- ---------- --------")
Dim cmd As DB2Command = conn.CreateCommand()
cmd.CommandText = "SELECT * FROM emp_sal"
Dim reader As DB2DataReader = cmd.ExecuteReader()
Do While reader.Read()
Console.WriteLine( _
" " & _
reader.GetString(1).PadRight(10) & " " & _
reader.GetString(0).PadRight(10) & " " & _
reader.GetDecimal(2).ToString().PadRight(10))
Loop
reader.Close()
Catch e As Exception
Console.WriteLine(e.Message)
End Try
' Drop the table emp_sal
Try
Console.WriteLine()
Console.WriteLine(" DROP TABLE emp_sal")
Dim cmd As DB2Command = conn.CreateCommand()
cmd.CommandText = "DROP TABLE emp_sal"
cmd.ExecuteNonQuery()
Catch e As Exception
Console.WriteLine(e.Message)
End Try
End Sub ' Demo_WITH_DEFAULT
' This method demonstrates how to insert into a foreign key
Public Shared Sub Demo_FK_OnInsertShow(conn As DB2Connection, _
trans As DB2Transaction)
Console.WriteLine()
Console.WriteLine( _
"----------------------------------------------------------" & _
vbNewLine & _
"USE THE SQL STATEMENTS:" & vbNewLine & _
" ALTER TABLE" & vbNewLine & _
" INSERT" & vbNewLine & _
"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')")
Dim cmd As DB2Command = conn.CreateCommand()
cmd.Transaction = trans
cmd.CommandText = "INSERT INTO dept VALUES('D00', 'SALES')"
cmd.ExecuteNonQuery()
Catch e As Exception
Console.WriteLine(e.Message)
End Try
' Insert an entry into the child table, 'emp'
Try
Console.WriteLine()
Console.WriteLine( _
" INSERT INTO emp VALUES('0080', 'Pearce', 'E03')")
Dim cmd As DB2Command = conn.CreateCommand()
cmd.Transaction = trans
cmd.CommandText = _
"INSERT INTO emp VALUES('0080', 'Pearce', 'E03')"
cmd.ExecuteNonQuery()
Catch e As Exception
Console.WriteLine()
Console.WriteLine( _
"**************** Expected Error ******************" & vbNewLine)
Console.WriteLine(e.Message)
Console.WriteLine( _
"**************************************************")
End Try
' 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 e As Exception
Console.WriteLine(e.Message)
End Try
' Drop the foreign key
trans = conn.BeginTransaction()
FK_Drop(conn, trans)
End Sub ' Demo_FK_OnInsertShow
' This method demonstrates how to use an 'ON UPDATE NO ACTION'
' foreign key
Public Shared Sub Demo_FK_ON_UPDATE_NO_ACTION(conn As DB2Connection, _
trans As DB2Transaction)
Console.WriteLine()
Console.WriteLine( _
"----------------------------------------------------------" & _
vbNewLine & _
"USE THE SQL STATEMENTS:" & vbNewLine & _
" ALTER TABLE" & vbNewLine & _
" UPDATE" & vbNewLine & _
"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'")
Dim cmd As DB2Command = conn.CreateCommand()
cmd.Transaction = trans
cmd.CommandText = _
"UPDATE dept SET deptno = 'E01' WHERE deptno = 'A00' "
cmd.ExecuteNonQuery()
Catch e As Exception
Console.WriteLine()
Console.WriteLine( _
"**************** Expected Error ******************")
Console.WriteLine(e.Message)
Console.WriteLine( _
"**************************************************")
End Try
' Update the parent table, 'dept'
Try
Console.WriteLine()
Console.WriteLine( _
" UPDATE dept" & vbNewLine & _
" SET deptno = CASE" & vbNewLine & _
" WHEN deptno = 'A00' THEN 'B00'" & vbNewLine & _
" WHEN deptno = 'B00' THEN 'A00'" & vbNewLine & _
" END" & vbNewLine & _
" WHERE deptno = 'A00' OR deptno = 'B00'")
Dim cmd As DB2Command = 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 e As Exception
Console.WriteLine(e.Message)
End Try
' Update the child table, 'emp'
Try
Console.WriteLine()
Console.WriteLine( _
" UPDATE emp SET dept_no = 'G11' WHERE empname = 'Wheeler'")
Dim cmd As DB2Command = conn.CreateCommand()
cmd.Transaction = trans
cmd.CommandText = _
"UPDATE emp SET dept_no = 'G11' WHERE empname = 'Wheeler' "
cmd.ExecuteNonQuery()
Catch e As Exception
Console.WriteLine()
Console.WriteLine( _
"**************** Expected Error ******************" & vbNewLine)
Console.WriteLine(e.Message)
Console.WriteLine( _
"**************************************************")
End Try
' 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 e As Exception
Console.WriteLine(e.Message)
End Try
' Drop the foreign key
trans = conn.BeginTransaction()
FK_Drop(conn, trans)
End Sub ' Demo_FK_ON_UPDATE_NO_ACTION
' This method demonstrates how to use an 'ON UPDATE RESTRICT'
' foreign key
Public Shared Sub Demo_FK_ON_UPDATE_RESTRICT(conn As DB2Connection, _
trans As DB2Transaction)
Console.WriteLine()
Console.WriteLine( _
"----------------------------------------------------------" & _
vbNewLine & _
"USE THE SQL STATEMENTS:" & vbNewLine & _
" ALTER TABLE" & vbNewLine & _
" UPDATE" & vbNewLine & _
"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'")
Dim cmd As DB2Command = conn.CreateCommand()
cmd.Transaction = trans
cmd.CommandText = _
"UPDATE dept SET deptno = 'E01' WHERE deptno = 'A00' "
cmd.ExecuteNonQuery()
Catch e As Exception
Console.WriteLine()
Console.WriteLine( _
"**************** Expected Error ******************" & vbNewLine)
Console.WriteLine(e.Message)
Console.WriteLine( _
"**************************************************")
End Try
' 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" & vbNewLine & _
" SET deptno = CASE" & vbNewLine & _
" WHEN deptno = 'A00' THEN 'B00'" & vbNewLine & _
" WHEN deptno = 'B00' THEN 'A00'" & vbNewLine & _
" END" & vbNewLine & _
" WHERE deptno = 'A00' OR deptno = 'B00'")
Dim cmd As DB2Command = 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 e As Exception
Console.WriteLine()
Console.WriteLine( _
"**************** Expected Error ******************" & vbNewLine)
Console.WriteLine(e.Message)
Console.WriteLine( _
"**************************************************")
End Try
' 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'")
Dim cmd As DB2Command = conn.CreateCommand()
cmd.Transaction = trans
cmd.CommandText = _
"UPDATE emp SET dept_no = 'G11' WHERE empname = 'Wheeler' "
cmd.ExecuteNonQuery()
Catch e As Exception
Console.WriteLine()
Console.WriteLine( _
"**************** Expected Error ******************" & vbNewLine)
Console.WriteLine(e.Message)
Console.WriteLine( _
"**************************************************")
End Try
' 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 e As Exception
Console.WriteLine(e.Message)
End Try
' Drop the foreign key
trans = conn.BeginTransaction()
FK_Drop(conn,trans)
End Sub ' Demo_FK_ON_UPDATE_RESTRICT
' This method demonstrates how to use an 'ON DELETE CASCADE' foreign key
Public Shared Sub Demo_FK_ON_DELETE_CASCADE(conn As DB2Connection, _
trans As DB2Transaction)
Console.WriteLine()
Console.WriteLine( _
"----------------------------------------------------------" & _
vbNewLine & _
"USE THE SQL STATEMENTS:" & vbNewLine & _
" ALTER TABLE" & vbNewLine & _
" DELETE" & vbNewLine & _
"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'")
Dim cmd As DB2Command = conn.CreateCommand()
cmd.Transaction = trans
cmd.CommandText = "DELETE FROM dept WHERE deptno = 'C00' "
cmd.ExecuteNonQuery()
Catch e As Exception
Console.WriteLine(e.Message)
End Try
' 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'")
Dim cmd As DB2Command = conn.CreateCommand()
cmd.Transaction = trans
cmd.CommandText = "DELETE FROM emp WHERE empname = 'Wheeler' "
cmd.ExecuteNonQuery()
Catch e As Exception
Console.WriteLine(e.Message)
End Try
' 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 e As Exception
Console.WriteLine(e.Message)
End Try
' Drop the foreign key
trans = conn.BeginTransaction()
FK_Drop(conn,trans)
End Sub ' Demo_FK_ON_DELETE_CASCADE
' This method demonstrates how to use an 'ON DELETE SET NULL'
' foreign key
Public Shared Sub Demo_FK_ON_DELETE_SET_NULL(conn As DB2Connection, _
trans As DB2Transaction)
Console.WriteLine()
Console.WriteLine( _
"----------------------------------------------------------" & _
vbNewLine & _
"USE THE SQL STATEMENTS:" & vbNewLine & _
" ALTER TABLE" & vbNewLine & _
" DELETE" & vbNewLine & _
"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'")
Dim cmd As DB2Command = conn.CreateCommand()
cmd.Transaction = trans
cmd.CommandText = "DELETE FROM dept WHERE deptno = 'C00' "
cmd.ExecuteNonQuery()
Catch e As Exception
Console.WriteLine(e.Message)
End Try
' 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'")
Dim cmd As DB2Command = conn.CreateCommand()
cmd.Transaction = trans
cmd.CommandText = "DELETE FROM emp WHERE empname = 'Wheeler' "
cmd.ExecuteNonQuery()
Catch e As Exception
Console.WriteLine(e.Message)
End Try
' 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 e As Exception
Console.WriteLine(e.Message)
End Try
' Drop the foreign key
trans = conn.BeginTransaction()
FK_Drop(conn,trans)
End Sub ' Demo_FK_ON_DELETE_SET_NULL
' This method demonstrates how to use an 'ON DELETE NO ACTION'
' foreign key
Public Shared Sub Demo_FK_ON_DELETE_NO_ACTION(conn As DB2Connection, _
trans As DB2Transaction)
Console.WriteLine()
Console.WriteLine( _
"----------------------------------------------------------" & _
vbNewLine & _
"USE THE SQL STATEMENTS:" & vbNewLine & _
" ALTER TABLE" & vbNewLine & _
" DELETE" & vbNewLine & _
"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'")
Dim cmd As DB2Command = conn.CreateCommand()
cmd.Transaction = trans
cmd.CommandText = "DELETE FROM dept WHERE deptno = 'C00' "
cmd.ExecuteNonQuery()
Catch e As Exception
Console.WriteLine()
Console.WriteLine( _
"**************** Expected Error ******************" & vbNewLine)
Console.WriteLine(e.Message)
Console.WriteLine( _
"**************************************************")
End Try
' Delete from the child table, 'emp'
Try
Console.WriteLine()
Console.WriteLine(" DELETE FROM emp WHERE empname = 'Wheeler'")
Dim cmd As DB2Command = conn.CreateCommand()
cmd.Transaction = trans
cmd.CommandText = "DELETE FROM emp WHERE empname = 'Wheeler' "
cmd.ExecuteNonQuery()
Catch e As Exception
Console.WriteLine(e.Message)
End Try
' 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 e As Exception
Console.WriteLine(e.Message)
End Try
' Drop the foreign key
trans = conn.BeginTransaction()
FK_Drop(conn,trans)
End Sub ' Demo_FK_ON_DELETE_NO_ACTION
End Class ' TbConstr