14
Thu, Nov
5 New Articles

Introduction to Database Constraints

Typography
  • Smaller Small Medium Big Bigger
  • Default Helvetica Segoe Georgia Times

This article is an excerpt from Chapter 5, "Database Constraints," of Database Design and SQL for DB2 (MC Press, 2013).

 

Constraints, sometimes referred to as integrity constraints or integrity rules, are restrictions based on business rules and other business policies and procedures that ensure data in a database are acceptable and accurate.

 

A DBMS can provide features that can be used to specify integrity constraints. The features usually include range checks (e.g., "between 1 and 50") or allowable values (e.g., "equal to A or B or C"). More-complex constraints may be specifiable as well; examples include relationships between columns within the same table (e.g., an employee's hire date must be greater than his or her birth date) or inter-row relationships (a customer number value in an order table must exist as a customer number value in exactly one customer table). The DBMS, which handles all updating of the database, checks these constraints when database rows are inserted, updated, or deleted. If a constraint is not met, the DBMS blocks the operation and signals an error.

 

Three integrity rule categories allow the specification of important constraints that a relational DBMS can enforce automatically whenever a database update occurs:

  • Data integrity
  • Entity integrity
  • Referential integrity

 

These rules protect not only the specific values in columns but also the identity and interrelationships of rows.

Data Integrity

Data integrity defines the possible values of a column. In a database system, data integrity is defined by

  • Data type and length
  • Null value acceptance
  • Allowable values
  • Default value

 

For example, if an age column in an EMPLOYEES table is defined as an integer, the value of every instance of that column must always be numeric and an integer. If this column is defined so that it must always be positive, a negative value is forbidden. The value of the column being mandatory indicates that the column can be NULL.

 

All these characteristics form the data integrity of this column. This type of data integrity warrants the following:

  • The identity and purpose of the column are clear, and all the tables in which the column appears are properly identified.
  • Column definitions are consistent throughout the database.
  • The values of the column are consistent and valid.
  • The types of modifications, comparisons, and operators that can be applied to the values in the column are clearly identified.

 

Each column in the model should be assigned attributes that include the following:

  • Data Type—Basic data types are integer, decimal, and character. Most databases support variants of these types plus special data types for date and time.
  • Length—The length is the number of digits or characters in the value, such as a value of 5 digits or 40 characters.
  • Date FormatThe date format specifies the format for date values, such as dd/mm/yy, mm/dd/yyyy, or yy/mm/dd.
  • Range—The range specifies the lower and upper boundaries of the values that the column may legally have.
  • Constraints—Constraints are special restrictions on allowable values. For example, the retire_date for an employee must always be greater than the hire_date for that employee.
  • Null Support—This attribute indicates whether the column can have null values.
  • Default Value (if any)—The default value is the value that a column will contain if no value is entered.

Entity Integrity

The second category of integrity essential to the relational database model is entity integrity. This is a fairly straightforward concept: Every row in a table represents an entity (i.e., an instance of an entity type) that must exist in the real world; therefore, every row must be uniquely identifiable. It follows that no completely duplicate rows (all column values identical) can exist in a table; otherwise, the unique existence of entities is not represented in the database.

 

From this property of uniqueness is derived the principle that there exists in every table some set of columns (possibly the entire table's columns) whose values are never duplicated entirely in any two rows in the table. If the set of columns includes no superfluous columns, or ones not needed to guarantee uniqueness, the set of columns can serve as the table's primary key. More than one possible set of columns may meet the criteria for a primary key; each of these is referred to as a candidate key, and one is picked arbitrarily as the primary key.

 

The primary key is a set of columns whose values are unique for all rows in a table. Because of this fact, the primary key forms the only means of addressing a specific row in the relational database model. A consequence of the requirement for unique primary key values is that none of the values in a row's primary key columns can be null; that is, none can be missing or unknown. None of the primary key columns can be null because otherwise this row's primary key value could not be guaranteed to be unequal to some other row's primary key value. In the relational model, null is essentially a placeholder that means the value of this column is unknown. There is no way to know that the null value is not also present in the same primary key column of some other row. Thus, if employee_id is the column serving as the primary key in the EMPLOYEES table and there exists a row with employee_id = NULL, there is no guarantee that another row might have a null value for employee_id. A similar argument holds for primary keys made up of more than one column, known as composite primary keys, which require all column values to guarantee uniqueness and hence cannot have null for any of the columns in the primary key.

Referential Integrity

The third, and final, category of integrity fundamental to the relational database model is referential integrity. Referential integrity is a database concept that ensures that relationships between tables remain reliable. That is, when a table contains a foreign key associated with another table's primary key, the concept of referential integrity states that a row containing the foreign key may not be added to the table unless a matching value exists in the primary key column of the parent table. In addition, referential integrity includes the concepts of cascading updates and deletes, which ensure that changes made to the table with the foreign key are reflected in the primary table.

Constraint Types

In a DBMS, database constraints provide a way to guarantee that rows in a table have valid primary or unique key values, that rows in a dependent table have valid foreign key values that reference rows in a parent table, and that individual column values are valid. All constraints are enforced when rows are inserted or updated in the table. This chapter discusses five constraints, identified in Table 5-1.


 

Constraint

Description

PRIMARY KEY

Identifies which   column is the unique identifier or primary key for each row in the table. The   values in the primary key column must be unique for every row in the table.   Since the primary key must be unique, it cannot be NULL.

FOREIGN KEY

Is what makes the   relational database work. For every one-to-many (parent-child) relationship   in the database, a foreign key constraint is added to the child (many) table.   The foreign key in the child (many) table links to the parent (one) table.   Thus, if a row is added to the child table, the value entered in the foreign   key column must already exist as a primary key in the parent table.

UNIQUE

Identifies a column   as containing unique values for the UNIQUE column in each   row in the table. The UNIQUE   constraint differs from a primary key in that it allows NULL   values.

CHECK

Enforces a business   rule on a column. Before a value can be entered into a CHECK   column, the condition (business rule) specified in the CHECK   constraint must be true.

NOT NULL

Ensures that a   column identified as NOT   NULL   will not contain a NULL   value.

TABLE 5-1: Constraint types

 

 

Jim Cooper has spent his entire career on IBM systems, including the System/34, System/36, AS/400, and now System i. He worked as a software developer for many years before moving to the education sector. Jim has been Coordinator and Professor in the Computer Studies department at Lambton College in Sarnia, Ontario, Canada for more than 25 years. He has taught a variety of subjects, with his primary focus on IBM i application development skills.

From 1995 through 2001, Jim served as Director of the IBM Roundtable College Conference, an IBM international conference held annually for colleges teaching IBM i technologies. Jim has also authored two textbooks on RPG and COBOL programming with John Wiley & Sons.

BLOG COMMENTS POWERED BY DISQUS

LATEST COMMENTS

Support MC Press Online

$

Book Reviews

Resource Center

  • SB Profound WC 5536 Have you been wondering about Node.js? Our free Node.js Webinar Series takes you from total beginner to creating a fully-functional IBM i Node.js business application. You can find Part 1 here. In Part 2 of our free Node.js Webinar Series, Brian May teaches you the different tooling options available for writing code, debugging, and using Git for version control. Brian will briefly discuss the different tools available, and demonstrate his preferred setup for Node development on IBM i or any platform. Attend this webinar to learn:

  • SB Profound WP 5539More than ever, there is a demand for IT to deliver innovation. Your IBM i has been an essential part of your business operations for years. However, your organization may struggle to maintain the current system and implement new projects. The thousands of customers we've worked with and surveyed state that expectations regarding the digital footprint and vision of the company are not aligned with the current IT environment.

  • SB HelpSystems ROBOT Generic IBM announced the E1080 servers using the latest Power10 processor in September 2021. The most powerful processor from IBM to date, Power10 is designed to handle the demands of doing business in today’s high-tech atmosphere, including running cloud applications, supporting big data, and managing AI workloads. But what does Power10 mean for your data center? In this recorded webinar, IBMers Dan Sundt and Dylan Boday join IBM Power Champion Tom Huntington for a discussion on why Power10 technology is the right strategic investment if you run IBM i, AIX, or Linux. In this action-packed hour, Tom will share trends from the IBM i and AIX user communities while Dan and Dylan dive into the tech specs for key hardware, including:

  • Magic MarkTRY the one package that solves all your document design and printing challenges on all your platforms. Produce bar code labels, electronic forms, ad hoc reports, and RFID tags – without programming! MarkMagic is the only document design and print solution that combines report writing, WYSIWYG label and forms design, and conditional printing in one integrated product. Make sure your data survives when catastrophe hits. Request your trial now!  Request Now.

  • SB HelpSystems ROBOT GenericForms of ransomware has been around for over 30 years, and with more and more organizations suffering attacks each year, it continues to endure. What has made ransomware such a durable threat and what is the best way to combat it? In order to prevent ransomware, organizations must first understand how it works.

  • SB HelpSystems ROBOT GenericIT security is a top priority for businesses around the world, but most IBM i pros don’t know where to begin—and most cybersecurity experts don’t know IBM i. In this session, Robin Tatam explores the business impact of lax IBM i security, the top vulnerabilities putting IBM i at risk, and the steps you can take to protect your organization. If you’re looking to avoid unexpected downtime or corrupted data, you don’t want to miss this session.

  • SB HelpSystems ROBOT GenericCan you trust all of your users all of the time? A typical end user receives 16 malicious emails each month, but only 17 percent of these phishing campaigns are reported to IT. Once an attack is underway, most organizations won’t discover the breach until six months later. A staggering amount of damage can occur in that time. Despite these risks, 93 percent of organizations are leaving their IBM i systems vulnerable to cybercrime. In this on-demand webinar, IBM i security experts Robin Tatam and Sandi Moore will reveal:

  • FORTRA Disaster protection is vital to every business. Yet, it often consists of patched together procedures that are prone to error. From automatic backups to data encryption to media management, Robot automates the routine (yet often complex) tasks of iSeries backup and recovery, saving you time and money and making the process safer and more reliable. Automate your backups with the Robot Backup and Recovery Solution. Key features include:

  • FORTRAManaging messages on your IBM i can be more than a full-time job if you have to do it manually. Messages need a response and resources must be monitored—often over multiple systems and across platforms. How can you be sure you won’t miss important system events? Automate your message center with the Robot Message Management Solution. Key features include:

  • FORTRAThe thought of printing, distributing, and storing iSeries reports manually may reduce you to tears. Paper and labor costs associated with report generation can spiral out of control. Mountains of paper threaten to swamp your files. Robot automates report bursting, distribution, bundling, and archiving, and offers secure, selective online report viewing. Manage your reports with the Robot Report Management Solution. Key features include:

  • FORTRAFor over 30 years, Robot has been a leader in systems management for IBM i. With batch job creation and scheduling at its core, the Robot Job Scheduling Solution reduces the opportunity for human error and helps you maintain service levels, automating even the biggest, most complex runbooks. Manage your job schedule with the Robot Job Scheduling Solution. Key features include:

  • LANSA Business users want new applications now. Market and regulatory pressures require faster application updates and delivery into production. Your IBM i developers may be approaching retirement, and you see no sure way to fill their positions with experienced developers. In addition, you may be caught between maintaining your existing applications and the uncertainty of moving to something new.

  • LANSAWhen it comes to creating your business applications, there are hundreds of coding platforms and programming languages to choose from. These options range from very complex traditional programming languages to Low-Code platforms where sometimes no traditional coding experience is needed. Download our whitepaper, The Power of Writing Code in a Low-Code Solution, and:

  • LANSASupply Chain is becoming increasingly complex and unpredictable. From raw materials for manufacturing to food supply chains, the journey from source to production to delivery to consumers is marred with inefficiencies, manual processes, shortages, recalls, counterfeits, and scandals. In this webinar, we discuss how:

  • The MC Resource Centers bring you the widest selection of white papers, trial software, and on-demand webcasts for you to choose from. >> Review the list of White Papers, Trial Software or On-Demand Webcast at the MC Press Resource Center. >> Add the items to yru Cart and complet he checkout process and submit

  • Profound Logic Have you been wondering about Node.js? Our free Node.js Webinar Series takes you from total beginner to creating a fully-functional IBM i Node.js business application.

  • SB Profound WC 5536Join us for this hour-long webcast that will explore:

  • Fortra IT managers hoping to find new IBM i talent are discovering that the pool of experienced RPG programmers and operators or administrators with intimate knowledge of the operating system and the applications that run on it is small. This begs the question: How will you manage the platform that supports such a big part of your business? This guide offers strategies and software suggestions to help you plan IT staffing and resources and smooth the transition after your AS/400 talent retires. Read on to learn: