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

  • Oracle
    • WebLogic Reset (212) Tue04,11:01am

      WebLogic起動するたびにユーザ名とパスワードを聞いてきて煩わしかったので回避したメモ。     securityフォルダを作成     $ mkdir $DOMAIN_HOME/servers/AdminServer/security     boot.propertiesを作成    boot.properties     username=weblogic    password=password     ※平文でOK     nohup & でWeblogicを起動     $ nohup sh $DOMAIN_HOME/startWebLogic.sh &     ※nohup.outに吐かれるのがいやだったら適当にリダイレクトさせる     boot.propertiesが暗号化されてることを確認     $ ca…

      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 “not 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 “not 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 “id” 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 “primary key” and a “foreign key” constraint to declare relationships between multiple tables.

Syntax:

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

Example: To create ‘salesboys’ table with “sid” 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 “shopper.salesboys” . For this table, the primary key is “sid”. Now you are creating a new table that has sales boy’s personal details with different schema named “employee” and table named “salesboys”. In this case, “sid” 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 “shopper.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 “employee.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 “shopper.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