Saminnet-Search Article Wiki Forum Piwigo SNS Cloud vtiger Sugar
DB2 Constraints

  • Data-Articles
    • Baja iwf (106) Sun09,20:21pm

      sesuai post saya yang terakhirツdisini, rumah dua pohon sedang dalam pembangunan tahap 2 dengan material utama menggunakan baja. baja adalah material konstruksi yang paling padat apabila dibandingkan dengan beton dan kayu karena memiliki berat satuan yang cukup besar. karena baja bisa difabrikasi di pabrik, pemasangan di site bisa lebih cepat dibandingkan dengan beton yang mesti dicetak dan ditun…

      Read More...

DB2 Constraints

This chapter describes various constraints in the database.

Introduction

To enforce database integrity, a set of rules is defined, called constraints. The constraints either permit or prohibit the values in the columns.

In a Real time database activities, the data should be added with certain restrictions. For example, in a sales database, sales-id or transaction-id should be unique. The constraints types are:

  • http://www.tutorialspoint.com/images/icon-bullet.png) 0px 4px no-repeat;">NOT NULL
  • http://www.tutorialspoint.com/images/icon-bullet.png) 0px 4px no-repeat;">Unique
  • http://www.tutorialspoint.com/images/icon-bullet.png) 0px 4px no-repeat;">Primary key
  • http://www.tutorialspoint.com/images/icon-bullet.png) 0px 4px no-repeat;">Foreign Key
  • http://www.tutorialspoint.com/images/icon-bullet.png) 0px 4px no-repeat;">Check
  • http://www.tutorialspoint.com/images/icon-bullet.png) 0px 4px no-repeat;">Informational

Constraints are only associated with tables. They are applied to only particular tables. They are defined and applied to the table at the time of table creation.

Explanation of each constraint:

NOT NULL

It is a rule to prohibit null values from one or more columns within the table.

Syntax:

db2 create table <table_name>(col_name col_type notnull,..)  

Example: [To create a sales table, with four columns (id, itemname, qty, price) in this adding 窶從ot null窶 constraints to all columns to avoid forming any null cell in the table.]

db2 create table shopper.sales(id bigint notnull, itemname 
varchar(40)notnull, qty intnotnull,price doublenotnull)   

Inserting NOT NULL values into table

You can insert values in the table as shown below:

Example:ツ[ERRORoneous Query]

db2 insert into shopper.sales(id,itemname,qty) 
values(1,'raagi',12)

Output:ツ[Correct query]

DB21034E  The command was processed as an SQL statement because 
it was not a 

valid CommandLineProcessor command.  During SQL processing 
it returned: 

SQL0407N  Assignment of a NULL value to a NOT NULL column 
"TBSPACEID=5, 

TABLEID=4, COLNO=3"isnot allowed.  SQLSTATE=23502 
 

Example:ツ[Correct query]

db2 insert into shopper.sales(id,itemname,qty,price) 
values(1,'raagi',12,120.00)  

db2 insert into shopper.sales(id,itemname,qty,price) 
values(1,'raagi',12,120.00)

Output:

DB20000I The SQL command completed successfully.

Unique constraints

Using these constraints, you can set values of columns uniquely. For this, the unique constraints are declared with 窶從ot null窶 constraint at the time of creating table.

Syntax:

db2 create table <tab_name>(<col><col_type>notnull unique,...)

Example:

db2 create table shopper.sales1(id bigint notnull unique, 
itemname varchar(40)notnull, qty intnotnull,price 
doublenotnull)  

Inserting the values into table

Example:ツTo insert four different rows with unique ids as 1, 2, 3 and 4.

db2 insert into shopper.sales1(id, itemname, qty, price) 
values(1,'sweet',100,89)  

db2 insert into shopper.sales1(id, itemname, qty, price) 
values(2,'choco',50,60)  

db2 insert into shopper.sales1(id, itemname, qty, price) 
values(3,'butter',30,40)  

db2 insert into shopper.sales1(id, itemname, qty, price) 
values(4,'milk',1000,12)  

Example:ツTo insert a new row with 窶彿d窶 value 3

db2 insert into shopper.sales1(id, itemname, qty, price) 
values(3,'cheese',60,80)   

Output: when you try to insert a new row with existed id value it will show this result:

DB21034E  The command was processed as an SQL statement 
because it was not a 

valid CommandLineProcessor command.  During 
SQL processing it returned: 

SQL0803N  Oneor more values in the INSERT statement, 
UPDATE statement,or foreign key update caused by a
DELETE statement are not valid because the primary key, 
unique constraint or unique index identified by"1" constrains 
table "SHOPPER.SALES1"from having duplicate values for the 
index key. SQLSTATE=23505    

Primary key

Similar to the unique constraints, you can use a 窶徘rimary key窶 and a 窶彷oreign key窶 constraint to declare relationships between multiple tables.

Syntax:

db2 create table <tab_name>(<col_type>,.., primary
key ())

Example: To create 窶salesboys窶 table with 窶徭id窶 as a primary key

db2 create table shopper.salesboys(sid intnotnull, name 
varchar(40)notnull, salary doublenotnull, constraint 
pk_boy_id primary key (sid))

Foreign key

A foreign key is a set of columns in a table which are required to match at least one primary key of a row in another table. It is a referential constraint or referential integrity constraint. It is a logical rule about values in multiple columns in one or more tables. It enables required relationship between the tables.

Earlier, you created a table named 窶徭hopper.salesboys窶 . For this table, the primary key is 窶徭id窶. Now you are creating a new table that has sales boy窶冱 personal details with different schema named 窶彳mployee窶 and table named 窶徭alesboys窶. In this case, 窶徭id窶 is the foreign key.

Syntax:

db2 create table <tab_name>(<col><col_type>,constraint 
<const_name> foreign key (<col_name>)  
                  reference <ref_table>(<ref_col>)  

Example: [To create a table named 窶salesboys窶 with foreign key column 窶sid窶兢

db2 create table employee.salesboys( 
            sid int,  
            name varchar(30)notnull,  
            phone intnotnull,  
            constraint fk_boy_id  
            foreign key (sid)  
            references shopper.salesboys (sid) 
			 on delete restrict 
                       )

Example: [Inserting values into primary key table 窶徭hopper.salesboys窶拆

db2 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)

Example: [Inserting values into foreign key table 窶彳mployee.salesboys窶 [without error]]

db2 insert into employee.salesboys values(100,'raju',98998976), 
(101,'kiran',98911176), 
(102,'radha',943245176), 
(103,'wali',89857330),  
(104,'rayan',89851130)

If you entered an unknown number, which is not stored in 窶徭hopper.salesboys窶 table, it will show you SQL error.

Example: [error execution]

db2 insert into employee.salesboys values(105,'rayan',89851130)

Output:

DB21034E  The command was processed as an SQL statement because it 
was not a valid CommandLineProcessor command.  During SQL 
processing it returned: SQL0530N  The insert or update value of 
the FOREIGN KEY "EMPLOYEE.SALESBOYS.FK_BOY_ID"isnot equal to any 
value of the parent key of the parent table.  SQLSTATE=23503  

Checking constraint

You need to use this constraint to add conditional restrictions for a specific column in a table.

Syntax:

db2 create table <tab_name>                                                     
 (<col_name><col_type> 
  primary key (<col_name>),                                                       
  constraint <cons_name> check (condition or condition)  
 )
 

Example: [To create emp1 table with constraints values]

db2 create table empl                                                     
 (id           smallint notnull,                                         
  name         varchar(9),                                                
  dept         smallint check (dept between 10and100), 
  job          char(5)  check (job in('sales','mgr','clerk')), 
  hiredate     date,                                                      
  salary       decimal(7,2),                                              
  comm         decimal(7,2),                                              
  primary key (id),                                                       
  constraint yearsal check (year(hiredate)>1986or salary >40500)  
 )
 

Inserting values

You can insert values into a table as shown below:

db2 insert into empl values (1,'lee',15,'mgr','1985-01-01', 
40000.00,1000.00)

Dropping the constraint

Let us see the syntaxes for dropping various constraints.

Dropping UNIQUE constraint

Syntax:

db2 alter table <tab_name> drop unique <const_name>

Dropping primary key

Syntax:

db2 alter table <tab_name> drop primary key 

Dropping check constraint

Syntax:

db2 alter table <tab_name> drop check <check_const_name>  

Dropping foreign key

Syntax:

db2 alter table <tab_name> drop foreigh key <foreign_key_name>  

Comments   

 
0 #1 Guest 2017-06-29 23:34
Pretty! This has been a really wonderful post.

Thanks for providing this information.

Also visit my blog; quan ao bao ho dong nai
Quote
 

Category Database

TweetTweet Share on LinkedInShare on LinkedIn Share on Google+Google+ Submit to RedditReddit Publish on WordPress WordPress Send emailSend email