hasan eken integrity constraints
Download
Skip this Video
Download Presentation
HASAN EKEN INTEGRITY CONSTRAINTS

Loading in 2 Seconds...

play fullscreen
1 / 30

HASAN EKEN INTEGRITY CONSTRAINTS - PowerPoint PPT Presentation


  • 88 Views
  • Uploaded on

HASAN EKEN INTEGRITY CONSTRAINTS. What is it?. Integrity constraints are used to ensure accuracy and consistency of data in a relational database. Ensuring changes do not result in loss of consistency. A DBMS should provide capabilities for defining and enforcing these constraints. . Cont`d.

loader
I am the owner, or an agent authorized to act on behalf of the owner, of the copyrighted work described.
capcha
Download Presentation

PowerPoint Slideshow about ' HASAN EKEN INTEGRITY CONSTRAINTS' - miya


An Image/Link below is provided (as is) to download presentation

Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.


- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -
Presentation Transcript
what is it
What is it?
  • Integrity constraints are used to ensure accuracy and consistency of data in a relational database.
  • Ensuring changes do not result in loss of consistency.
  • A DBMS should provide capabilities for defining and enforcing these constraints.
cont d
Cont`d
  • What we mean by enforcing constraints is new entered information may be wrong and DBMS should guarantee that wrong operation will not effect accuracy and consistency.
types of integrity constraints
Types of integrity constraints
  • Entity integrity
  • Referential integrity(not included)
  • Domain integrity
  • User defined integrity
entity integrity
ENTITY INTEGRITY
  • In the relational data model, entity integrity is one of the three inherent integrity rules.
  • Entity Integrity ensures that there are no duplicate records within the table and that the field that identifies each record within the table is unique and never null.
  • The existence of the Primary Key is the core of the entity integrity. If you define a primary key for each entity, they follow the entity integrity rule.
cont d1
Cont`d
  • Entity integrity specifies that the Primary Keys on every instance of an entity must be kept, must be unique and must have values other than NULL.
cont d2
Cont`d
  • Entity Integrity ensures two properties for primary keys:
  • The primary key for a row is unique; it does not match the primary key of any other row in the table.
  • The primary key is not null, no component of the primary key may be set to null.
cont d3
Cont`d
  • The uniqueness property ensures that the primary key of each row uniquely identifies it; there are no duplicates. The second property ensures that the primary key has meaning, has a value; no component of the key is missing.
  • Within relational databases using SQL, entity integrity is enforced by adding a primary key clause to a schema definition. The system enforces Entity Integrity by not allowing operations (INSERT, UPDATE) to produce an invalid primary key. Any operation that is likely to create a duplicate primary key or one containing nulls is rejected.
examples
Examples
  • CREATE TABLE employee
  • ( id number(5) PRIMARY KEY,
  • name char(20),
  • age number(2),
  • location char(10)
  • );
examples1
Examples
  • Insert into employee values(123,Mehmet Yilmaz,28,Istanbul)
  • Insert into employee values(124,Ali Er,24,Istanbul)
  • Insert into employee values(123,Ahmet Yildiz,24,Istanbul)
  • Insert into employee values(,,28,Ankara)
domain integrtiy
Domain Integrtiy
  • A domain defines the possible values of an attribute. Domain Integrity rules govern these values. In a database system, the domain integrity is defined by:
  • The datatype and the length
  • The NULL value acceptance
  • The allowable values, through techniques like constraints or rules
  • The default value
cont d4
Cont’d
  • For example, if you define the attribute of Age, of an Employee entity, is an integer, the value of every instance of that attribute must always be numeric and an integer.
  • If you also define that this attribute must always be positive, the a negative value is forbidden.
cont d5
Cont’d
  • This type of data integrity warrants the following: the identity and purpose of a field is clear and all of the tables in which it appears are properly identified.
  • Each attribute in the model should be assigned domain information which includes:
  • Data Type - Basic data types are integer, decimal, or character. Most data bases support variants of these plus special data types for date and time.
  • Length - This is the number of digits or characters in the value. For example, a value of 5 digits or 40 characters.
  • Null support - Indicates whether the attribute can have null values.
  • Default value (if any) - The value an attribute instance will have if a value is not entered.
domain integrity
Domain Integrity
  • A column definition can specify NOT NULL. This indicates that the table column must contain a value for each row. By implication a column definition that does not specify NOT NULL do not have to contain an actual value.
examples not null
Examples Not Null
  • CREATE TABLE employee
  • ( id number(5),
  • name char(20) CONSTRAINT nm_nn NOT NULL,
  • dept char(10),
  • age number(2),
  • salary number(10),
  • location char(10)
  • );
examples cont d
Examples Cont’d
  • In the above example we see that name field can’t be null , if we try to give null values ,dbms won`t accept it.
check constraint
Check Constraint
  • The CHECK constraint is used to limit the value range that can be placed in a column.
  • If you define a CHECK constraint on a single column it allows only certain values for this column.
examples check
Examples Check
  • CREATE TABLE employee
  • ( id number(5) PRIMARY KEY,
  • name char(20),
  • dept char(10),
  • age number(2),
  • gender char(1) CHECK (gender in (\'M\',\'F\')),
  • salary number(10),
  • location char(10)
  • );
another example
Another Example
  • CREATE TABLE Persons
  • (
  • P_Idint NOT NULL CHECK (P_Id>0),
  • LastNamevarchar(255) NOT NULL,
  • FirstNamevarchar(255),
  • Address varchar(255),
  • City varchar(255)
  • )
another example cont d
Another Example Cont’d
  • Example above indicates that person’s id is an integer and it can’t be null and it is bigger than 0;
example
Example
  • CREATE TABLE Persons
  • (
  • P_Idint NOT NULL,
  • LastNamevarchar(255) NOT NULL,
  • FirstNamevarchar(255),
  • Address varchar(255),
  • City varchar(255),
  • CONSTRAINT chk_Person CHECK (P_Id>0 AND City=\'Sandnes\')
  • )
slide22

The above example is to allow naming of a CHECK constraint, and for defining a CHECK constraint on multiple columns

default constraint
DEFAULT Constraint
  • The DEFAULT constraint is used to insert a default value into a column.
  • The default value will be added to all new records, if no other value is specified.
example default constraint
Example Default Constraint
  • CREATE TABLE Persons
  • (
  • P_Idint NOT NULL,
  • LastNamevarchar(255) NOT NULL,
  • FirstNamevarchar(255),
  • Address varchar(255),
  • City varchar(255) DEFAULT \'Sandnes\'
  • )
unique constraint
Unique Constraint
  • The UNIQUE constraint uniquely identifies each record in a database table.
  • The UNIQUE and PRIMARY KEY constraints both provide a guarantee for uniqueness for a column or set of columns.
  • A PRIMARY KEY constraint automatically has a UNIQUE constraint defined on it.
  • Note that you can have many UNIQUE constraints per table, but only one PRIMARY KEY constraint per table.
example unique
Example Unique
  • CREATE TABLE Persons
  • (
  • P_Idint NOT NULL UNIQUE,
  • LastNamevarchar(255) NOT NULL,
  • FirstNamevarchar(255),
  • Address varchar(255),
  • City varchar(255)
  • )
another example unique
Another Example Unique
  • CREATE TABLE Persons
  • (
  • P_Idint NOT NULL,
  • LastNamevarchar(255) NOT NULL,
  • FirstNamevarchar(255),
  • Addressvarchar(255),
  • Cityvarchar(255),
  • CONSTRAINT uc_PersonID UNIQUE (P_Id,LastName)
  • )
slide29

The above example is to to allow naming of a UNIQUE constraint, and for defining a UNIQUE constraint on multiple columns

alter table
Alter Table
  • By using “Alter Table” we can create constraints when the table is already created.
  • Example:
  • ALTER TABLE Persons
  • ADD UNIQUE (P_Id)
ad