----------------------------------------------------------------------------- -- (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.db2 -- -- SAMPLE: How to create, use, and drop constraints -- -- SQL STATEMENTS USED: -- ALTER TABLE -- CREATE TABLE -- DELETE -- DROP TABLE -- INSERT -- SELECT -- TERMINATE -- UPDATE -- -- ----------------------------------------------------------------------------- -- -- For more information about the command line processor (CLP) scripts, -- see the README file. -- -- For information on using SQL statements, see the SQL Reference. -- -- For the latest information on programming, building, and running DB2 -- applications, visit the DB2 Information Center: -- http://publib.boulder.ibm.com/infocenter/db2luw/v9r7/index.jsp ----------------------------------------------------------------------------- -- turn off the Auto-commit option UPDATE COMMAND OPTIONS USING c OFF; ----------------------------------------------------------------------------- -- Illustration of 'NOT NULL' constraint ----------------------------------------------------------------------------- -- turn off the Echo Current Command option to suppress the printing of the -- echo command UPDATE COMMAND OPTIONS USING v OFF; ! echo ------------------NOT NULL constraint-----------------------; -- turn on the Echo Current Command option UPDATE COMMAND OPTIONS USING v ON; -- create a table CREATE TABLE emp_sal(lastname VARCHAR(10) NOT NULL, firstname VARCHAR(10), salary DECIMAL(7, 2)); COMMIT; -- insert values into the table that violate the constraint INSERT INTO emp_sal VALUES(NULL, 'PHILIP', 17000.00); -- drop the table DROP TABLE emp_sal; -- turn off the Echo Current Command option to suppress the printing of the -- echo command UPDATE COMMAND OPTIONS USING v OFF; ! echo --------------------------------------------------------------; -- turn on the Echo Current Command option UPDATE COMMAND OPTIONS USING v ON; ----------------------------------------------------------------------------- -- Illustration of 'UNIQUE' constraint ----------------------------------------------------------------------------- UPDATE COMMAND OPTIONS USING v OFF; ! echo ------------------UNIQUE constraint-----------------------; UPDATE COMMAND OPTIONS USING v ON; -- create a table CREATE TABLE emp_sal(lastname VARCHAR(10) NOT NULL, firstname VARCHAR(10) NOT NULL, salary DECIMAL(7, 2), CONSTRAINT unique_cn UNIQUE(lastname, firstname)); COMMIT; -- insert values into the table that violate the constraint INSERT INTO emp_sal VALUES('SMITH', 'PHILIP', 17000.00), ('SMITH', 'PHILIP', 21000.00); -- drop the constraint ALTER TABLE emp_sal DROP CONSTRAINT unique_cn; -- drop the table DROP TABLE emp_sal; UPDATE COMMAND OPTIONS USING v OFF; ! echo --------------------------------------------------------------; UPDATE COMMAND OPTIONS USING v ON; ----------------------------------------------------------------------------- -- Illustration of 'PRIMARY KEY' constraint ----------------------------------------------------------------------------- UPDATE COMMAND OPTIONS USING v OFF; ! echo ---------------PRIMARY KEY constraint----------------------; UPDATE COMMAND OPTIONS USING v ON; -- create a table 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)); COMMIT; -- insert values into the table that violate the constraint INSERT INTO emp_sal VALUES('SMITH', 'PHILIP', 17000.00), ('SMITH', 'PHILIP', 21000.00); -- drop the constraint ALTER TABLE emp_sal DROP CONSTRAINT pk_cn; -- drop the table DROP TABLE emp_sal; UPDATE COMMAND OPTIONS USING v OFF; ! echo --------------------------------------------------------------; UPDATE COMMAND OPTIONS USING v ON; ----------------------------------------------------------------------------- -- Illustration of 'CHECK' constraint ----------------------------------------------------------------------------- UPDATE COMMAND OPTIONS USING v OFF; ! echo -------------------CHECK constraint-----------------------; UPDATE COMMAND OPTIONS USING v ON; -- create a table CREATE TABLE emp_sal(lastname VARCHAR(10), firstname VARCHAR(10), salary DECIMAL(7, 2), CONSTRAINT check_cn CHECK(salary < 25000.00)); COMMIT; -- insert values into the table that violate the constraint INSERT INTO emp_sal VALUES('SMITH', 'PHILIP', 27000.00); -- drop the constraint ALTER TABLE emp_sal DROP CONSTRAINT check_cn; -- drop the table DROP TABLE emp_sal; UPDATE COMMAND OPTIONS USING v OFF; ! echo --------------------------------------------------------------; UPDATE COMMAND OPTIONS USING v ON; ----------------------------------------------------------------------------- -- Illustration of 'INFORMATIONAL' constraint ----------------------------------------------------------------------------- UPDATE COMMAND OPTIONS USING v OFF; ! echo ------------------INFORMATIONAL constraint------------------; UPDATE COMMAND OPTIONS USING v ON; -- create a table CREATE TABLE tab_emp (empno INTEGER NOT NULL PRIMARY KEY, name VARCHAR(10), firstname VARCHAR(20), salary INTEGER CONSTRAINT minsalary CHECK (salary >= 25000) NOT ENFORCED ENABLE QUERY OPTIMIZATION); COMMIT; -- insert data that doesn't satisfy the constraint 'minsalary'. -- database manager does not enforce the constraint for IUD operations INSERT INTO tab_emp VALUES(1, 'SMITH', 'PHILIP', 1000); -- alter the constraint to make it ENFORCED by database manager ALTER TABLE tab_emp ALTER CHECK minsalary ENFORCED; -- delete entries from 'tab_emp' Table DELETE FROM tab_emp; -- alter the constraint to make it ENFORCED by database manager ALTER TABLE tab_emp ALTER CHECK minsalary ENFORCED; -- insert into the table with data not conforming to the constraint -- 'minsalary'. Database manager now enforces the constraint for IUD -- operations INSERT INTO tab_emp VALUES(1, 'SMITH', 'PHILIP', 1000); -- drop the table DROP TABLE tab_emp; UPDATE COMMAND OPTIONS USING v OFF; ! echo --------------------------------------------------------------; UPDATE COMMAND OPTIONS USING v ON; ----------------------------------------------------------------------------- -- Illustration of 'WITH DEFAULT' constraint ----------------------------------------------------------------------------- UPDATE COMMAND OPTIONS USING v OFF; ! echo ------------------WITH DEFAULT constraint-------------------; UPDATE COMMAND OPTIONS USING v ON; -- create a table CREATE TABLE emp_sal(lastname VARCHAR(10), firstname VARCHAR(10), salary DECIMAL(7, 2) WITH DEFAULT 17000.00); COMMIT; -- insert into the table INSERT INTO emp_sal(lastname, firstname) VALUES('SMITH' , 'PHILIP'), ('PARKER', 'JOHN'), ('PEREZ' , 'MARIA'); -- display the content of the table SELECT * FROM emp_sal; -- drop the table DROP TABLE emp_sal; UPDATE COMMAND OPTIONS USING v OFF; ! echo --------------------------------------------------------------; UPDATE COMMAND OPTIONS USING v ON; ----------------------------------------------------------------------------- -- create two tables 'tab_dept' and 'tab_emp' for illustrating the FOREIGN KEY -- constraint ----------------------------------------------------------------------------- UPDATE COMMAND OPTIONS USING v OFF; ! echo ------------Create Tables for FOREIGN KEY---------------------; UPDATE COMMAND OPTIONS USING v ON; CREATE TABLE tab_dept (deptno CHAR(3) NOT NULL, deptname VARCHAR(20), CONSTRAINT pk_tab_dept PRIMARY KEY(deptno)); -- insert rows into the table INSERT INTO tab_dept VALUES('A00', 'ADMINISTRATION'), ('B00', 'DEVELOPMENT'), ('C00', 'SUPPORT'); CREATE TABLE tab_emp (empno CHAR(4), empname VARCHAR(10), dept_no CHAR(3)); -- insert rows into the table INSERT INTO tab_emp VALUES('0010', 'Smith', 'A00'), ('0020', 'Ngan', 'B00'), ('0030', 'Lu', 'B00'), ('0040', 'Wheeler', 'B00'), ('0050', 'Burke', 'C00'), ('0060', 'Edwards', 'C00'), ('0070', 'Lea', 'C00'); COMMIT; UPDATE COMMAND OPTIONS USING v OFF; ! echo --------------------------------------------------------------; UPDATE COMMAND OPTIONS USING v ON; ----------------------------------------------------------------------------- -- Illustration of how FOREIGN key works on INSERT ----------------------------------------------------------------------------- UPDATE COMMAND OPTIONS USING v OFF; ! echo ---------How FOREIGN KEY works on INSERT----------------------; UPDATE COMMAND OPTIONS USING v ON; -- display initial content of the tables 'tab_dept' and 'tab_emp' SELECT * FROM tab_dept; SELECT * FROM tab_emp; -- create a foreign key ALTER TABLE tab_emp ADD CONSTRAINT fk_dept FOREIGN KEY(dept_no) REFERENCES tab_dept(deptno); COMMIT; -- insert into the parent table INSERT INTO tab_dept VALUES('D00', 'SALES'); -- insert into the child table INSERT INTO tab_emp VALUES('0080', 'Pearce', 'E03'); -- display the final content of the tables 'tab_dept' and 'tab_emp' SELECT * FROM tab_dept; SELECT * FROM tab_emp; -- rollback the transaction ROLLBACK; -- drop the foreign key ALTER TABLE tab_emp DROP CONSTRAINT fk_dept; COMMIT; UPDATE COMMAND OPTIONS USING v OFF; ! echo --------------------------------------------------------------; UPDATE COMMAND OPTIONS USING v ON; ----------------------------------------------------------------------------- -- Illustration of 'ON UPDATE NO ACTION' FOREIGN KEY ----------------------------------------------------------------------------- UPDATE COMMAND OPTIONS USING v OFF; ! echo -----------ON UPDATE NO ACTION FOREIGN KEY-----------------; UPDATE COMMAND OPTIONS USING v ON; -- display initial content of the tables 'tab_dept' and 'tab_emp' SELECT * FROM tab_dept; SELECT * FROM tab_emp; -- create a foreign key ALTER TABLE tab_emp ADD CONSTRAINT fk_dept FOREIGN KEY(dept_no) REFERENCES tab_dept(deptno) ON UPDATE NO ACTION; COMMIT; -- update the parent table UPDATE tab_dept SET deptno = 'E01' WHERE deptno = 'A00'; UPDATE tab_dept SET deptno = CASE WHEN deptno = 'A00' THEN 'B00' WHEN deptno = 'B00' THEN 'A00' END WHERE deptno = 'A00' OR deptno = 'B00'; -- update the child table UPDATE tab_emp SET dept_no = 'G11' WHERE empname = 'Wheeler'; -- display the final content of the tables 'tab_dept' and 'tab_emp' SELECT * FROM tab_dept; SELECT * FROM tab_emp; -- rollback the transaction ROLLBACK; -- drop the foreign key ALTER TABLE tab_emp DROP CONSTRAINT fk_dept; COMMIT; UPDATE COMMAND OPTIONS USING v OFF; ! echo --------------------------------------------------------------; UPDATE COMMAND OPTIONS USING v ON; ----------------------------------------------------------------------------- -- Illustration of 'ON UPDATE RESTRICT' FOREIGN KEY ----------------------------------------------------------------------------- UPDATE COMMAND OPTIONS USING v OFF; ! echo -----------ON UPDATE RESTRICT FOREIGN KEY-------------------; UPDATE COMMAND OPTIONS USING v ON; -- display initial content of the tables 'tab_dept' and 'tab_emp' SELECT * FROM tab_dept; SELECT * FROM tab_emp; -- create a foreign key ALTER TABLE tab_emp ADD CONSTRAINT fk_dept FOREIGN KEY(dept_no) REFERENCES tab_dept(deptno) ON UPDATE RESTRICT; COMMIT; -- update the parent table UPDATE tab_dept SET deptno = 'E01' WHERE deptno = 'A00'; UPDATE tab_dept SET deptno = CASE WHEN deptno = 'A00' THEN 'B00' WHEN deptno = 'B00' THEN 'A00' END WHERE deptno = 'A00' OR deptno = 'B00'; -- update the child table UPDATE tab_emp SET dept_no = 'G11' WHERE empname = 'Wheeler'; -- display the final content of the tables 'tab_dept' and 'tab_emp' SELECT * FROM tab_dept; SELECT * FROM tab_emp; -- rollback the transaction ROLLBACK; -- drop the foreign key ALTER TABLE tab_emp DROP CONSTRAINT fk_dept; COMMIT; UPDATE COMMAND OPTIONS USING v OFF; ! echo --------------------------------------------------------------; UPDATE COMMAND OPTIONS USING v ON; ----------------------------------------------------------------------------- -- Illustration of 'ON DELETE CASCADE' FOREIGN KEY ----------------------------------------------------------------------------- UPDATE COMMAND OPTIONS USING v OFF; ! echo -----------ON DELETE CASCADE FOREIGN KEY--------------------; UPDATE COMMAND OPTIONS USING v ON; -- display initial content of the tables 'tab_dept' and 'tab_emp' SELECT * FROM tab_dept; SELECT * FROM tab_emp; -- create a foreign key ALTER TABLE tab_emp ADD CONSTRAINT fk_dept FOREIGN KEY(dept_no) REFERENCES tab_dept(deptno) ON DELETE CASCADE; COMMIT; -- delete from the parent table DELETE FROM tab_dept WHERE deptno = 'C00'; -- display the content of the tables 'tab_dept' and 'tab_emp' SELECT * FROM tab_dept; SELECT * FROM tab_emp; -- delete from the child table DELETE FROM tab_emp WHERE empname = 'Wheeler'; -- display the final content of the tables 'tab_dept' and 'tab_emp' SELECT * FROM tab_dept; SELECT * FROM tab_emp; -- rollback the transaction ROLLBACK; -- drop the foreign key ALTER TABLE tab_emp DROP CONSTRAINT fk_dept; COMMIT; UPDATE COMMAND OPTIONS USING v OFF; ! echo --------------------------------------------------------------; UPDATE COMMAND OPTIONS USING v ON; ----------------------------------------------------------------------------- -- Illustration of 'ON DELETE SET NULL' FOREIGN KEY ----------------------------------------------------------------------------- UPDATE COMMAND OPTIONS USING v OFF; ! echo -----------ON DELETE SET NULL FOREIGN KEY-------------------; UPDATE COMMAND OPTIONS USING v ON; -- display initial content of the tables 'tab_dept' and 'tab_emp' SELECT * FROM tab_dept; SELECT * FROM tab_emp; -- create a foreign key ALTER TABLE tab_emp ADD CONSTRAINT fk_dept FOREIGN KEY(dept_no) REFERENCES tab_dept(deptno) ON DELETE SET NULL; COMMIT; -- delete from the parent table DELETE FROM tab_dept WHERE deptno = 'C00'; -- display the content of the tables 'tab_dept' and 'tab_emp' SELECT * FROM tab_dept; SELECT * FROM tab_emp; -- delete from the child table DELETE FROM tab_emp WHERE empname = 'Wheeler'; -- display the final content of the tables 'tab_dept' and 'tab_emp' SELECT * FROM tab_dept; SELECT * FROM tab_emp; -- rollback the transaction ROLLBACK; -- drop the foreign key ALTER TABLE tab_emp DROP CONSTRAINT fk_dept; COMMIT; UPDATE COMMAND OPTIONS USING v OFF; ! echo --------------------------------------------------------------; UPDATE COMMAND OPTIONS USING v ON; ----------------------------------------------------------------------------- -- Illustration of 'ON DELETE NO ACTION' FOREIGN KEY ----------------------------------------------------------------------------- UPDATE COMMAND OPTIONS USING v OFF; ! echo -----------ON DELETE NO ACTION FOREIGN KEY------------------; UPDATE COMMAND OPTIONS USING v ON; -- display initial content of the tables 'tab_dept' and 'tab_emp' SELECT * FROM tab_dept; SELECT * FROM tab_emp; -- create a foreign key ALTER TABLE tab_emp ADD CONSTRAINT fk_dept FOREIGN KEY(dept_no) REFERENCES tab_dept(deptno) ON DELETE NO ACTION; COMMIT; -- delete from the parent table DELETE FROM tab_dept WHERE deptno = 'C00'; -- delete from the child table DELETE FROM tab_emp WHERE empname = 'Wheeler'; -- display the final content of the tables 'tab_dept' and 'tab_emp' SELECT * FROM tab_dept; SELECT * FROM tab_emp; -- rollback the transaction ROLLBACK; -- drop the foreign key ALTER TABLE tab_emp DROP CONSTRAINT fk_dept; COMMIT; UPDATE COMMAND OPTIONS USING v OFF; ! echo --------------------------------------------------------------; UPDATE COMMAND OPTIONS USING v ON; ----------------------------------------------------------------------------- -- drop the two tables 'tab_dept' and 'tab_emp' ----------------------------------------------------------------------------- UPDATE COMMAND OPTIONS USING v OFF; ! echo -------------Drop the Tables created for FOREIGN KEY----------; UPDATE COMMAND OPTIONS USING v ON; DROP TABLE tab_dept; DROP TABLE tab_emp; COMMIT; -- disconnect from the database CONNECT RESET; TERMINATE;