


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 ~]$

Hot AI Tools

Undresser.AI Undress
AI-powered app for creating realistic nude photos

AI Clothes Remover
Online AI tool for removing clothes from photos.

Undress AI Tool
Undress images for free

Clothoff.io
AI clothes remover

AI Hentai Generator
Generate AI Hentai for free.

Hot Article

Hot Tools

Notepad++7.3.1
Easy-to-use and free code editor

SublimeText3 Chinese version
Chinese version, very easy to use

Zend Studio 13.0.1
Powerful PHP integrated development environment

Dreamweaver CS6
Visual web development tools

SublimeText3 Mac version
God-level code editing software (SublimeText3)

Hot Topics

In recent days, Ice Universe has been steadily revealing details about the Galaxy S25 Ultra, which is widely believed to be Samsung's next flagship smartphone. Among other things, the leaker claimed that Samsung only plans to bring one camera upgrade

OnLeaks has now partnered with Android Headlines to provide a first look at the Galaxy S25 Ultra, a few days after a failed attempt to generate upwards of $4,000 from his X (formerly Twitter) followers. For context, the render images embedded below h

Alongside announcing two new smartphones, TCL has also announced a new Android tablet called the NXTPAPER 14, and its massive screen size is one of its selling points. The NXTPAPER 14 features version 3.0 of TCL's signature brand of matte LCD panels

The Vivo Y300 Pro just got fully revealed, and it's one of the slimmest mid-range Android phones with a large battery. To be exact, the smartphone is only 7.69 mm thick but features a 6,500 mAh battery. This is the same capacity as the recently launc

In recent days, Ice Universe has been steadily revealing details about the Galaxy S25 Ultra, which is widely believed to be Samsung's next flagship smartphone. Among other things, the leaker claimed that Samsung only plans to bring one camera upgrade

Samsung has not offered any hints yet about when it will update its Fan Edition (FE) smartphone series. As it stands, the Galaxy S23 FE remains the company's most recent edition, having been presented at the start of October 2023. However, plenty of

Motorola has released countless devices this year, although only two of them are foldables. For context, while most of the world has received the pair as the Razr 50 and Razr 50 Ultra, Motorola offers them in North America as the Razr 2024 and Razr 2

The Redmi Note 14 Pro Plus is now official as a direct successor to last year'sRedmi Note 13 Pro Plus(curr. $375 on Amazon). As expected, the Redmi Note 14 Pro Plus heads up the Redmi Note 14 series alongside theRedmi Note 14and Redmi Note 14 Pro. Li
