Category Archives: RDBMS

Database Integrity Constraints


Database integrity constraints are a set of rules, defined by database administrator or application developer to maintain the assurance of accuracy and consistency of data. It ensures that the data insertion, updating, and other processes have to be performed in such a way that data integrity is not violated.

Types of Integrity Constraint

  1. Domain Constraints
  2. Primary Integrity Constraints
  3. Referential Integrity Constraints
  4. Key Constraints

Domain Constraints

Domain constraints can be determined as the valid set of values for an attribute. The data type of database includes integer, character, string, currency, date and time, etc. The value of the attribute must be available in the corresponding database..

Primary Integrity Constraints

The primary/entity integrity says that every table must have a primary key and column or columns chosen for the primary key should be unique or not null. The Primary key is used to identify individual row or record from the entity. If the primary key has a NULL value, then we can’t identify those rows or records.

Referential Integrity Constraints

A referential integrity constraint concerns the concept of a foreign key.  It states that any foreign-key value can only be in one of two states as the primary key of the relative table or null value. Null represents no relation between table.

Key Constraints

Keys constraints are the entity set rule that is used to identify a record within its entity uniquely. An entity can have multiple keys, but out of which one key will be the primary key as given above in Primary Integrity Constraints.

Types of Data Integrity

The following types of data integrity rules applied by developers or data administrators on  tables :

  • Nulls:  Define a rule for the column to allows or disallows Null value in inserts or updates of rows. When referenced type column data is updated or deleted, all associated columns data is set to NULL.
  • Unique Column Values: Define a rule on a column or set of columns that allow only insert or update of a row with a unique value for the column or set of columns.
  • Primary Key Values: Define a rule on a column or set of columns so that each row or record in the table can be uniquely identified by the values in the column or set of columns.
  • Referential Integrity: Define a rule on a column or set of columns in a table that allows the insert or update of a record only if the value for the column or set of columns (foreign key value) matches a value in a column of a related table (the referenced value/ primary key value).
  • Restrict: A referential integrity rule that restricts the update or deletion of referenced data.
  • Set to Default: When referenced column data is updated or deleted, all associated dependent column data is set to a default value.
  • Cascade: When referenced column data is updated, all associated dependent column data is correspondingly updated; when a referenced row/record is deleted, all associated dependent rows/records are deleted.
  • Database triggers: Define rules to invoke triggers when particular column or table perform operations insert, update or delete and called procedures. Triggers used in the following situations:
    • Update or delete dependent data when above referential integrity rules not able to handle. For Ex:  update and delete SET NULL, update and delete SET DEFAULT or update CASCADE.
    • Call the procedure from different nodes (distributed database) when child and parent tables are on different nodes.
    • Apply business rules apart from the above data integrity constraints.

     

References

https://docs.oracle.com/cd/A57673_01/DOC/server/doc/SCN73/ch7.htm

Advertisements

JDBC : Stored Procedure Example


A SQL stored procedure (SP) is a collection SQL statements , SQL command logic, which is compiled and stored on the database and execute on the server. Stored procedures can also be cached and reused. The main purpose of stored procedures to hide direct SQL queries from the code and improve performance of database operations such as select, update, and delete data.

See Also :

Types of parameters of Stored Procedure:

  • IN: A parameter whose value is unknown when the SQL statement is created. You bind values to IN parameters with the setXXX() methods.
  • OUT: A parameter whose value is supplied by the SQL statement it returns. You retrieve values from the OUT parameters with the getXXX() methods.\
  • INOUT: A parameter that provides both input and output values. You *bind variables with the setXXX() methods and retrieve values with the getXXX() methods.

Example :

In this example will focus on how to execute procedures by JDBC instead of writing of procedure.

Table and Procedure :  Here is small procedure to getStudentName based on student id.


Create Table
CREATE TABLE `facingissuesonitdb`.`student` (
`id` INT NOT NULL AUTO_INCREMENT,
`name` VARCHAR(25) NULL,
PRIMARY KEY (`id`),
UNIQUE INDEX `name_UNIQUE` (`name` ASC));

Insert Records  
INSERT INTO `facingissuesonitdb`.`student` (`id`, `name`) VALUES ('1', 'Saurabh Gupta');
INSERT INTO `facingissuesonitdb`.`student` (`id`, `name`) VALUES ('2', 'Rajesh Gupta');
INSERT INTO `facingissuesonitdb`.`student` (`id`, `name`) VALUES ('3', 'Ramesh Gupta');

Create Stored procedure
USE `facingissuesonitdb`;
DROP procedure IF EXISTS `getStudentName`;

DELIMITER $$
USE `facingissuesonitdb`$$
CREATE DEFINER=`root`@`localhost` PROCEDURE `getStudentName`(in student_id integer, out first_name varchar(25))
BEGIN
select name into  first_name from student where id=student_id;
END$$

DELIMITER ;

Java code to execute stored procedure by JDBC.

package procedure_functions;

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.SQLException;

import connection.MyConnection;

public class StoredProcedureTest {

	private static final String PROCEDURE_QUERY = "{call getStudentName(?,?)}";

	public static void main(String[] args) {
	 try {
             //to get connection code check blog for Connection according to your need.
	     Connection conn = MyConnection.getConnectionMain("MySql");
	     CallableStatement csmt = conn.prepareCall(PROCEDURE_QUERY);
	     csmt.setInt(1, 2);
	     // Because second parameter is OUT so register it
	    csmt.registerOutParameter(2, java.sql.Types.VARCHAR);
	    csmt.execute();
	     String firstName = csmt.getString(2);// for retrieving out parameter
											     // after execution
	     System.out.println(firstName);
	    } catch (SQLException ex) {
			ex.printStackTrace();
	   }
	}
}

Output


MySql Connection created successfully !!
MySql Connection Creation end !!
Rajesh Gupta

More on JDBC

Follow below links to know more on JDBC and solving JDBC issues :