Primary key constraint Foreign key constraint Check constraint NOT NULL Constraint OT NULL constraints prevent null values from being entered into a unique constraint Unique constraints ensure that the values in a set of columns are unique constraint in a dEPaRtmENt table might be that the pical ue and not null for all rows in the table For ex department number is unique and not null Invalid Record 005 Figure 10 Unique Constraints Prevent Duplicate Data The database manager enforces the constraint during insert and pdate operations, ensuring data integrity primary key constraint Each table can have one primary key. A primary key is a column or combination of columns that has the same properties as a unique constraint. You can use a primary key and foreign key constraints to define relationships between tables Because the primary key is used to identify a row in a table, it should be unique and have very few additions or deletions. A table cannot have more than one primary key, but it can have multiple unique keys. Primary keys are optional, and can be defined when a table is created or altered. They are also beneficial, because they order the data when data is exported or reorganized In the following tables, DEPTNO and EMPNO are the primary keys for the Department and EMPLOYEE tables 16 Administration Guide: Planning
v Primary key constraint v Foreign key constraint v Check constraint NOT NULL constraint NOT NULL constraints prevent null values from being entered into a column. unique constraint Unique constraints ensure that the values in a set of columns are unique and not null for all rows in the table. For example, a typical unique constraint in a DEPARTMENT table might be that the department number is unique and not null. The database manager enforces the constraint during insert and update operations, ensuring data integrity. primary key constraint Each table can have one primary key. A primary key is a column or combination of columns that has the same properties as a unique constraint. You can use a primary key and foreign key constraints to define relationships between tables. Because the primary key is used to identify a row in a table, it should be unique and have very few additions or deletions. A table cannot have more than one primary key, but it can have multiple unique keys. Primary keys are optional, and can be defined when a table is created or altered. They are also beneficial, because they order the data when data is exported or reorganized. In the following tables, DEPTNO and EMPNO are the primary keys for the DEPARTMENT and EMPLOYEE tables. Invalid Record 001 002 003 004 005 Dept. No. 003 Figure 10. Unique Constraints Prevent Duplicate Data 16 Administration Guide: Planning
Table 1 DEPARTMENT Table DEPTNO (Primary Key) DEPTNAMe MGRNO A00 Spiffy Computer Service 000010 Planning 000020 Information Center 000030 D11 Manufacturing Systems 000060 Table 2. EMPLOYEE Table EMPNO FIRSTNAME LASTNAME WORKDEPT PHONENO (Primary Key) Haas A00 00030 Kwan 000060 Irving Stern 6423 00120 Sean 2167 00140 Nicholls 1793 00170 Masatoshi Yoshimi D11 2890 foreign key constraint Foreign key constraints(also known as referential integrity constraints)enable you to define required relationships between and within tables For example, a typical foreign key constraint might state that every employee in the EMPLOYEE table must be a member of an existin department, as defined in the DEPARTMENT table To establish this relationship you would define the department number in the EMPLOYee table as the foreign key, and the department number in the DEPARTMENT table as the primary key Chapter 1. Basic relational database concepts 17
Table 1. DEPARTMENT Table DEPTNO (Primary Key) DEPTNAME MGRNO A00 Spiffy Computer Service Division 000010 B01 Planning 000020 C01 Information Center 000030 D11 Manufacturing Systems 000060 Table 2. EMPLOYEE Table EMPNO (Primary Key) FIRSTNAME LASTNAME WORKDEPT (Foreign Key) PHONENO 000010 Christine Haas A00 3978 000030 Sally Kwan C01 4738 000060 Irving Stern D11 6423 000120 Sean O’Connell A00 2167 000140 Heather Nicholls C01 1793 000170 Masatoshi Yoshimura D11 2890 foreign key constraint Foreign key constraints (also known as referential integrity constraints) enable you to define required relationships between and within tables. For example, a typical foreign key constraint might state that every employee in the EMPLOYEE table must be a member of an existing department, as defined in the DEPARTMENT table. To establish this relationship, you would define the department number in the EMPLOYEE table as the foreign key, and the department number in the DEPARTMENT table as the primary key. Chapter 1. Basic relational database concepts 17
Employee Table Foreign Key Employee Name John Doe Barb smith Invalid Record Fred vickers 027Janedoe Department Table Department Name Sales 002 Training Communications 015 Program Development Primary Key Figure 11. Foreign and Primary Key Constraints Define Relationships and Protect Data check constraint A check constraint is a database rule that specifies the values allowed in one or more columns of every row of a table For example, in an EMPLOYEE table, you can define the Type of Job column to be"Sales","Manager",or"Clerk". With this constraint, any record with a different value in the Type of Job column is not valid, and would be rejected, enforcing rules about the type of data allowed in the table You can also use triggers in your database. Triggers are more complex and potentially more powerful than constraints. They define a set of actions that are executed in conjunction with, or triggered by, an INSERT, UPDATE, or dELEtE clause on a specified base table. You can use triggers to support general forms of integrity or business rules. For example, a trigger can check a customers credit limit before an order is accepted, or be used in a banking 18 Administration Guide: Planning
check constraint A check constraint is a database rule that specifies the values allowed in one or more columns of every row of a table. For example, in an EMPLOYEE table, you can define the Type of Job column to be ″Sales″, ″Manager″, or ″Clerk″. With this constraint, any record with a different value in the Type of Job column is not valid, and would be rejected, enforcing rules about the type of data allowed in the table. You can also use triggers in your database. Triggers are more complex and potentially more powerful than constraints. They define a set of actions that are executed in conjunction with, or triggered by, an INSERT, UPDATE, or DELETE clause on a specified base table. You can use triggers to support general forms of integrity or business rules. For example, a trigger can check a customer’s credit limit before an order is accepted, or be used in a banking Dept. No. Dept. No. Employee Name Department Name John Doe Jane Doe Program Development Invalid Record Sales Barb Smith Training Fred Vickers Communications 015 Department Table Employee Table Foreign Key Primary Key 001 001 002 002 003 003 027 Figure 11. Foreign and Primary Key Constraints Define Relationships and Protect Data 18 Administration Guide: Planning
application to raise an alert if a withdrawal from an account did not fit a customers standard withdrawal patterns Related concepts: Constraints"on page 80 Triggers" on page 85 Developing a Backup and Recovery Strategy a database can become unusable because of hardware or software failure, or both. You may, at one time or another, encounter storage problems, power interruptions, and application failures, and different failure scenarios require different recovery actions. Protect your data against the possibility of loss b\y having a well rehearsed recovery strategy in place. Some of the questions that you should answer when developing your recovery strategy are: Will the database be recoverable? How much time can be spent recovering the database How much time will pass between backup operations? How much storage space can be allocated for backup copies and archived logs? Will table space level backups be sufficient, or will full database backups be necessary? A database recovery strategy should ensure that all information is available when it is required for database recovery. It should include a regular schedule for taking database backups and, in the case of partitioned database systems, include backups when the system is scaled (when database partition servers or nodes are added or dropped). Your overall strategy should also include procedures for recovering command scripts, applications, user-defined functions (UDFs), stored procedure code in operating system libraries, and load copies Different recovery methods are discussed in the sections that follow, and you will discover which recovery method is best suited to your business environment The concept of a database backup is the same as any other data backup: taking a copy of the data and then storing it on a different medium in case of failure or damage to the original. The simplest case of a backup involves shutting down the database to ensure that no further transactions occur, and then simply backing it up. You can then rebuild the database if it becomes damaged or corrupted in some way. The rebuilding of the database is called recovery. Version recovery is the restoration of a previous version of the database, using an image that was reated during a backup operation. Rollforward recovery is the reapplication of transactions recorded in the database log files after a database or a table space backup image has been restored Chapter 1. Basic relational database concepts 19
application to raise an alert if a withdrawal from an account did not fit a customer’s standard withdrawal patterns. Related concepts: v “Constraints” on page 80 v “Triggers” on page 85 Developing a Backup and Recovery Strategy A database can become unusable because of hardware or software failure, or both. You may, at one time or another, encounter storage problems, power interruptions, and application failures, and different failure scenarios require different recovery actions. Protect your data against the possibility of loss by having a well rehearsed recovery strategy in place. Some of the questions that you should answer when developing your recovery strategy are: Will the database be recoverable? How much time can be spent recovering the database? How much time will pass between backup operations? How much storage space can be allocated for backup copies and archived logs? Will table space level backups be sufficient, or will full database backups be necessary? A database recovery strategy should ensure that all information is available when it is required for database recovery. It should include a regular schedule for taking database backups and, in the case of partitioned database systems, include backups when the system is scaled (when database partition servers or nodes are added or dropped). Your overall strategy should also include procedures for recovering command scripts, applications, user-defined functions (UDFs), stored procedure code in operating system libraries, and load copies. Different recovery methods are discussed in the sections that follow, and you will discover which recovery method is best suited to your business environment. The concept of a database backup is the same as any other data backup: taking a copy of the data and then storing it on a different medium in case of failure or damage to the original. The simplest case of a backup involves shutting down the database to ensure that no further transactions occur, and then simply backing it up. You can then rebuild the database if it becomes damaged or corrupted in some way. The rebuilding of the database is called recovery. Version recovery is the restoration of a previous version of the database, using an image that was created during a backup operation. Rollforward recovery is the reapplication of transactions recorded in the database log files after a database or a table space backup image has been restored. Chapter 1. Basic relational database concepts 19
Crash recovery is the automatic recovery of the database if a failure occurs before all of the changes that are part of one or more units of work (transactions)are completed and committed. This is done by rolling back incomplete transactions and completing committed transactions that were still in memory when the crash occurred a database is created(Figure 12). These log files are important if you needt e Recovery log files and the recovery history file are created automatically wl recover data that is lost or damaged. You cannot directly modify a recovery log file or the recovery history file; however, you can delete entries from the recovery history file using the PRUNE HISTORY command. You can also use he rec_his-retentn database configuration parameter to specify the number of days that the recovery history file will be retained Database Object/Concept Equivalent Physical object Log Files Instance(s) Recovery History Database(s) History File Figure 12. Recovery Log Files and the Recovery History File 20 Administration Guide: Planning
Crash recovery is the automatic recovery of the database if a failure occurs before all of the changes that are part of one or more units of work (transactions) are completed and committed. This is done by rolling back incomplete transactions and completing committed transactions that were still in memory when the crash occurred. Recovery log files and the recovery history file are created automatically when a database is created (Figure 12). These log files are important if you need to recover data that is lost or damaged. You cannot directly modify a recovery log file or the recovery history file; however, you can delete entries from the recovery history file using the PRUNE HISTORY command. You can also use the rec_his_retentn database configuration parameter to specify the number of days that the recovery history file will be retained. Instance(s) System Database Object/Concept Equivalent Physical Object Database(s) Table Space Change History File Recovery History File Log Files Figure 12. Recovery Log Files and the Recovery History File 20 Administration Guide: Planning