Table of Contents
DB2 view, sequence, constraint, foreign key practice test
Home Backend Development PHP Tutorial DB2 views, sequences, constraints, foreign key practice tests_PHP tutorial

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

Jul 12, 2016 am 09:04 AM
android

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

www.bkjia.comtruehttp: //www.bkjia.com/PHPjc/1075174.htmlTechArticleDB2 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...
Statement of this Website
The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn

Hot AI Tools

Undresser.AI Undress

Undresser.AI Undress

AI-powered app for creating realistic nude photos

AI Clothes Remover

AI Clothes Remover

Online AI tool for removing clothes from photos.

Undress AI Tool

Undress AI Tool

Undress images for free

Clothoff.io

Clothoff.io

AI clothes remover

AI Hentai Generator

AI Hentai Generator

Generate AI Hentai for free.

Hot Article

R.E.P.O. Energy Crystals Explained and What They Do (Yellow Crystal)
2 weeks ago By 尊渡假赌尊渡假赌尊渡假赌
Repo: How To Revive Teammates
4 weeks ago By 尊渡假赌尊渡假赌尊渡假赌
Hello Kitty Island Adventure: How To Get Giant Seeds
3 weeks ago By 尊渡假赌尊渡假赌尊渡假赌

Hot Tools

Notepad++7.3.1

Notepad++7.3.1

Easy-to-use and free code editor

SublimeText3 Chinese version

SublimeText3 Chinese version

Chinese version, very easy to use

Zend Studio 13.0.1

Zend Studio 13.0.1

Powerful PHP integrated development environment

Dreamweaver CS6

Dreamweaver CS6

Visual web development tools

SublimeText3 Mac version

SublimeText3 Mac version

God-level code editing software (SublimeText3)

New report delivers damning assessment of rumoured Samsung Galaxy S25, Galaxy S25 Plus and Galaxy S25 Ultra camera upgrades New report delivers damning assessment of rumoured Samsung Galaxy S25, Galaxy S25 Plus and Galaxy S25 Ultra camera upgrades Sep 12, 2024 pm 12:23 PM

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 Galaxy S25 Ultra leaks in first render images with rumoured design changes revealed Samsung Galaxy S25 Ultra leaks in first render images with rumoured design changes revealed Sep 11, 2024 am 06:37 AM

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

IFA 2024 | TCL\'s NXTPAPER 14 won\'t match the Galaxy Tab S10 Ultra in performance, but it nearly matches it in size IFA 2024 | TCL\'s NXTPAPER 14 won\'t match the Galaxy Tab S10 Ultra in performance, but it nearly matches it in size Sep 07, 2024 am 06:35 AM

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

Vivo Y300 Pro packs 6,500 mAh battery in a slim 7.69 mm body Vivo Y300 Pro packs 6,500 mAh battery in a slim 7.69 mm body Sep 07, 2024 am 06:39 AM

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

New report delivers damning assessment of rumoured Samsung Galaxy S25, Galaxy S25 Plus and Galaxy S25 Ultra camera upgrades New report delivers damning assessment of rumoured Samsung Galaxy S25, Galaxy S25 Plus and Galaxy S25 Ultra camera upgrades Sep 12, 2024 pm 12:22 PM

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 Galaxy S24 FE billed to launch for less than expected in four colours and two memory options Samsung Galaxy S24 FE billed to launch for less than expected in four colours and two memory options Sep 12, 2024 pm 09:21 PM

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 Razr 50s shows itself as possible new budget foldable in early leak Motorola Razr 50s shows itself as possible new budget foldable in early leak Sep 07, 2024 am 09:35 AM

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

Xiaomi Redmi Note 14 Pro Plus arrives as first Qualcomm Snapdragon 7s Gen 3 smartphone with Light Hunter 800 camera Xiaomi Redmi Note 14 Pro Plus arrives as first Qualcomm Snapdragon 7s Gen 3 smartphone with Light Hunter 800 camera Sep 27, 2024 am 06:23 AM

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

See all articles