DB2 views, sequences, constraints, foreign key practice tests_PHP tutorial

DB2 view, sequence, constraint, foreign key practice test

Simple view:

db2 => create view myview1 as select * from t1 where empno DB20000I SQL command completed successfully.
db2 => select * from myview1


EMPNO NAME SEQNO
----------- ---------- ---- -------
10 wan qi 1
30 xu xin 3
50 bbbb 6


3 records have been selected.


Simple sequence


db2 => create sequence myseq start with 1 increment by 1 cache 5
DB20000I SQL command completed successfully.


db2 => insert into tank1 values(nextval for myseq,'asdf')
DB20000I SQL command completed successfully.
db2 => select * from tank1


ID NAME
----------- ----------
2 asdf
3 asdf
4 asdf
5 asdf
6 asdf
7 asdf
8 asdf



Simple constraint experiment

create table employee
(
ID integer not null constraint ID_pk primary key,
NAME varchar(9),
DEPT smallint constraint dept_ck1
check (dept between 10 and 100),
JOB char(5) constraint dept_ck2
check (JOB IN('Sales','Mgr','Clerk')),
HIREDATE date,
SALARY decimal(7,2),
constraint yearsal_ck
check (year(HIREDATE) > 1986 OR SALARY > 40000 )


)


[db2inst2@localhost ~]$ db2 "insert into employee values (1,'asdf',11, 'Sales','1788-02-10',40100)"
DB20000I SQL command completed successfully.
[db2inst2@localhost ~]$ db2 "insert into employee values(1,'asdf',13, 'Sales','1788-02-10',40100)"
DB21034E This command is treated as SQL
statement to process because it is an invalid "Command Line Processor" command. During SQL
processing, it returns:
SQL0803N One or more values ​​in the INSERT statement, UPDATE statement, or foreign key update caused by the DELETE
statement are invalid because identified by "1"
The primary key, unique constraint, or unique index restricts the index keys of table "DB2INST2.EMPLOYEE"
to not have duplicate values. SQLSTATE=23505
[db2inst2@localhost ~]$ db2 "insert into employee values(2,'asdf',101, 'Sales','1788-02-10',40100)"
DB21034E This command was Treated as a SQL
statement because it is an invalid "Command Line Processor" command. During SQL
processing it returns:
SQL0545N The requested operation is not allowed because the row does not satisfy the check constraint
"DB2INST2.EMPLOYEE.DEPT_CK1".
SQLSTATE=23513
[db2inst2@localhost ~]$ db2 "insert into employee values(2,'asdf',99, 'Sales','1788-02-10',40100)"
DB20000I SQL command completed successfully.
[db2inst2@localhost ~]$ db2 "insert into employee values(3,'asdf',99, 'Kales','1788-02-10',40100)"
DB21034E This command is treated as SQL
statement to process because it is an invalid "Command Line Processor" command. During SQL
processing it returns:
SQL0545N The requested operation is not allowed because the row does not satisfy the check constraint
"DB2INST2.EMPLOYEE.DEPT_CK2".
SQLSTATE=23513
[db2inst2@localhost ~]$ db2 "insert into employee values(3,'asdf',99, 'Sales','1788-02-10',40100)"
DB20000I SQL command completed successfully.
[db2inst2@localhost ~]$ db2 "insert into employee values(4,'asdf',98, 'Mgr','1788-02-10',40100)"
DB20000I SQL command completed successfully.
[db2inst2@localhost ~]$ db2 "insert into employee values(5,'asdf',98, 'Mgr','1987-02-10',40)"
DB20000I SQL command completed successfully.
[db2inst2@localhost ~]$ db2 "insert into employee values(6,'asdf',98, 'Mgr','1984-02-10',40)"
DB21034E This command is treated as SQL
statement to process because it is an invalid "Command Line Processor" command. During SQL
processing it returns:
SQL0545N The requested operation is not allowed because the row does not satisfy the check constraint
"DB2INST2.EMPLOYEE.YEARSAL_CK".
SQLSTATE=23513
[db2inst2@localhost ~]$ db2 "insert into employee values(6,'asdf',98, 'Mgr','1981-02-10',41000)"
DB20000I SQL command completed successfully.
[db2inst2@localhost ~]$ db2 "insert into employee values(6,'asdf',98, 'Mgr','1981-02-10',41000)"



Simple primary and foreign key test

1. View constraints:

SELECT * FROM SYSCAT.TABCONST

2. Create a new parent table:

create table shopper.salesboys(sid int not null, name
varchar(40) not null, salary double not null, constraint
pk_boy_id primary key (sid));


3. Create a new child Table:

create table employee.salesboys(
sid int,
name varchar(30) not null,
phone int not null,
constraint fk_boy_id
foreign key( sid)
references shopper.salesboys (sid)
on delete restrict //Restrict deletion
);

4. Insert data into the parent table,

[db2inst2@ localhost ~]$ more inste2.sql
insert into shopper.salesboys values(100,'raju',20000.00),
(101,'kiran',15000.00),
(102,'radha', 10000.00),
(103,'wali',20000.00),
(104,'rayan',15000.00)
;

5. Insert data into sub-table

[db2inst2@localhost ~]$ more inste3.sql
insert into employee.salesboys values(100,'raju',98998976),
(101,'kiran',98911176),
(102,'radha',943245176),
(103,'wali',89857330),
(104,'rayan',89851130) ;

6. Test external constraints:


[db2inst2@localhost ~]$ db2 "insert into employee.salesboys values(103,'rayan',89851130)"
DB20000I SQL command completed successfully.
[db2inst2@localhost ~]$ db2 "insert into employee.salesboys values(109,'rayan',89851130)"
DB21034E This command is processed as a SQL
statement because it is invalid "Command Line Processor" command. During SQL
processing, it returns:
SQL0530N FOREIGN KEY "EMPLOYEE.SALESBOYS.FK_BOY_ID" The inserted or updated value of
is not equal to any parent key value of the parent table. SQLSTATE=23503
[db2inst2@localhost ~]$


7. Delete external test

[db2inst2@localhost ~]$ db2 "ALTER TABLE EMPLOYEE.SALESBOYS DROP FOREIGN KEY FK_BOY_ID"
DB20000I SQL command completed successfully.
[db2inst2@localhost ~]$ db2 "insert into employee.salesboys values(109,'rayan',89851130)"
DB20000I SQL command completed successfully.
[db2inst2@localhost ~]$