HASAN EKEN INTEGRITY CONSTRAINTS

1. HASAN EKENINTEGRITY CONSTRAINTS

2. 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.

3. 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.

4. Types of integrity constraints • Entity integrity • Referential integrity(not included) • Domain integrity • User defined integrity

5. 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.

6. 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.

7. 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.

8. 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.

9. Examples • CREATE TABLE employee • ( id number(5) PRIMARY KEY, • name char(20), • age number(2), • location char(10) • );

10. 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)

11. 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

12. 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.

13. 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.

14. 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.

15. 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) • );

16. 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.

17. 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.

18. 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) • );

19. 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) • )

20. 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;

21. 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') • )

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

23. 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.

24. Example Default Constraint • CREATE TABLE Persons • ( • P_Idint NOT NULL, • LastNamevarchar(255) NOT NULL, • FirstNamevarchar(255), • Address varchar(255), • City varchar(255) DEFAULT 'Sandnes' • )

25. 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.

26. Example Unique • CREATE TABLE Persons • ( • P_Idint NOT NULL UNIQUE, • LastNamevarchar(255) NOT NULL, • FirstNamevarchar(255), • Address varchar(255), • City varchar(255) • )

27. The above example guarantees P_id is unique for all columns.

28. 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) • )

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

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