DB2 view, sequence, constraints, foreign key exercise test

DB2 view, sequence, constraints, foreign key exercise test
Simple View:

Db2 => create view myview1 as select * from t1 where empno <80
The DB20000I SQL command is successfully completed.
Db2 => select * from myview1


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


Three records are selected.


Simple Sequence


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


Db2 => insert into tank1 values (nextval for myseq, 'asdf ')
The DB20000I SQL command is successfully completed.
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 ', 'cler ')),
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', '2017-02-10', 1788 )"
The DB20000I SQL command is successfully completed.
[Db2inst2 @ localhost ~] $ Db2 "insert into employee values (1, 'asdf ', 13, 'sales', '2017-02-10', 1788 )"
DB21034E this command is treated as SQL
Statement, because it is an invalid "command line processor. In SQL
During processing, it returns:
SQL0803N INSERT statement, UPDATE statement, or by DELETE
One or more values in the foreign key update caused by the statement are invalid because"
The primary key, unique constraint, or unique index of the table "DB2INST2. EMPLOYEE"
The index key cannot have duplicate values. SQLSTATE = 23505
[Db2inst2 @ localhost ~] $ Db2 "insert into employee values (2, 'asdf ', 101, 'sales', '2017-02-10', 1788 )"
DB21034E this command is treated as SQL
Statement, because it is an invalid "command line processor. In SQL
During processing, it returns:
SQL0545N because the row does not meet the check Constraints
"DB2INST2. EMPLOYEE. DEPT_CK1", therefore, the requested operation is not allowed.
SQLSTATE = 23513
[Db2inst2 @ localhost ~] $ Db2 "insert into employee values (2, 'asdf ', 99, 'sales', '2017-02-10', 1788 )"
The DB20000I SQL command is successfully completed.
[Db2inst2 @ localhost ~] $ Db2 "insert into employee values (3, 'asdf ', 99, 'kales', '2017-02-10 ', 1788 )"
DB21034E this command is treated as SQL
Statement, because it is an invalid "command line processor. In SQL
During processing, it returns:
SQL0545N because the row does not meet the check Constraints
"DB2INST2. EMPLOYEE. DEPT_CK2", therefore, the requested operation is not allowed.
SQLSTATE = 23513
[Db2inst2 @ localhost ~] $ Db2 "insert into employee values (3, 'asdf ', 99, 'sales', '2017-02-10', 1788 )"
The DB20000I SQL command is successfully completed.
[Db2inst2 @ localhost ~] $ Db2 "insert into employee values (4, 'asdf ', 98, 'mgr', '2017-02-10 ', 1788 )"
The DB20000I SQL command is successfully completed.
[Db2inst2 @ localhost ~] $ Db2 "insert into employee values (5, 'asdf ', 98, 'mgr', '2017-02-10 ', 40 )"
The DB20000I SQL command is successfully completed.
[Db2inst2 @ localhost ~] $ Db2 "insert into employee values (6, 'asdf ', 98, 'mgr', '2017-02-10 ', 40 )"
DB21034E this command is treated as SQL
Statement, because it is an invalid "command line processor. In SQL
During processing, it returns:
SQL0545N because the row does not meet the check Constraints
"DB2INST2. EMPLOYEE. YEARSAL_CK", so the requested operation is not allowed.
SQLSTATE = 23513
[Db2inst2 @ localhost ~] $ Db2 "insert into employee values (6, 'asdf ', 98, 'mgr', '2017-02-10 ', 1981 )"
The DB20000I SQL command is successfully completed.
[Db2inst2 @ localhost ~] $ Db2 "insert into employee values (6, 'asdf ', 98, 'mgr', '2017-02-10 ', 1981 )"



Test the primary and Foreign keys

1. View constraints:

SELECT * from syscat. TABCONST

2. Create a 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 sub-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, 'ken', 15000.00 ),
(102, 'radha', 10000.00 ),
(103, 'wali', 20000.00 ),
(104, 'rayanc', 15000.00)
;

5. Insert data into a sub-table

[Db2inst2 @ localhost ~] $ More inste3. SQL
Insert into employee. salesboys values (100, 'raju', 98998976 ),
(101, 'ken', 98911176 ),
(102, 'radha', 943245176 ),
(103, 'wali', 89857330 ),
(104, 'rayan', 89851130 );

6. Test the external creation constraints:


[Db2inst2 @ localhost ~] $ Db2 "insert into employee. salesboys values (103, 'rayanc', 89851130 )"
The DB20000I SQL command is successfully completed.
[Db2inst2 @ localhost ~] $ Db2 "insert into employee. salesboys values (109, 'rayanc', 89851130 )"
DB21034E this command is treated as SQL
Statement, because it is an invalid "command line processor. In SQL
During processing, it returns:
SQL0530N foreign key "maid. SALESBOYS. FK_BOY_ID"
The insert or update value of is not equal to any parent key value of the parent table. SQLSTATE = 23503
[Db2inst2 @ localhost ~] $


7. Delete external creation Test

[Db2inst2 @ localhost ~] $ Db2 "alter table employee. salesboys drop foreign key FK_BOY_ID"
The DB20000I SQL command is successfully completed.
[Db2inst2 @ localhost ~] $ Db2 "insert into employee. salesboys values (109, 'rayanc', 89851130 )"
The DB20000I SQL command is successfully completed.
[Db2inst2 @ localhost ~] $