Constraints are a an extremely important attribute in a relational model. In fact, the relational design supports the well-defined concept of border on features or tables. Constraints space useful since they enable a designer come specify the semantics of data in the database. Constraints space the rules that pressure DBMSs to check that data satisfies the semantics.

You are watching: Referential and entity integrity are two names for the same thing.

Domain Integrity

Domain restricts the worths of attributes in the relation and is a constraint that the relational model. However, there room real-world semantics for data that cannot be specified if provided only with domain constraints. We need much more specific methods to state what data values room or are not permitted and which style is suitable for an attribute. Because that example, the Employee identifier (EID) must be unique or the employee Birthdate is in the variety . Such information is provided in reasonable statements dubbed integrity constraints.

There are several kinds of integrity constraints, described below.

Entity integrity

To for sure entity integrity, it is required that every table have a primary key. No the PK no one any part of it deserve to contain null values. This is due to the fact that null values for the primary crucial mean we cannot determine some rows. For example, in the EMPLOYEE table, Phone can not be a primary vital since some civilization may not have actually a telephone.

Referential integrity

Referential integrity calls for that a foreign key must have actually a equivalent primary vital or it have to be null. This constraint is specified between two tables (parent and child); that maintains the correspondence between rows in this tables. It method the recommendation from a heat in one table to another table should be valid.

Examples of referential truth constraint in the Customer/Order database the the Company:

Customer(CustID, CustName)Order(OrderID, CustID, OrderDate)

To ensure that there are no orphan records, we must enforce referential integrity. An orphan record is one who foreign key FK value is not uncovered in the corresponding entity – the entity where the PK is located. Recall the a usual join is between a PK and also FK.

The referential truth constraint says that the customer ID (CustID) in the stimulate table must complement a precious CustID in the client table. Many relational databases have declarative referential integrity. In various other words, as soon as the tables are produced the referential truth constraints are collection up.

Here is one more example indigenous a Course/Class database:

Course(CrsCode, DeptCode, Description)Class(CrsCode, Section, ClassTime)

The referential verity constraint says that CrsCode in the course table must complement a precious CrsCode in the food table. In this situation, it’s not sufficient that the CrsCode and Section in the class table consist of the PK, us must likewise enforce referential integrity.

When setting up referential integrity that is crucial that the PK and also FK have actually the same data varieties and come from the exact same domain, otherwise the relational database administration system (RDBMS) will not allow the join. RDBMS is a well-known database device that is based upon the relational model introduced by E. F. Codd the IBM’s mountain Jose research study Laboratory. Relational database solution are easier to use and also understand than various other database systems.

Referential integrity in Microsoft Access

In Microsoft (MS) Access, referential verity is collection up by authorized the PK in the customer table to the CustID in the stimulate table. See number 9.1 for a view of exactly how this is done on the edit Relationships display screen in ms Access.

*
Figure 9.1. Referential accessibility in multiple sclerosis Access, by A. Watt.

Referential integrity utilizing Transact-SQL (MS SQL Server)

When making use of Transact-SQL, the referential truth is collection when creating the stimulate table through the FK. Detailed below are the statements mirroring the FK in the order table referencing the PK in the client table.


CREATE TABLE Orders( OrderID INTEGER main KEY,CustID INTEGER references Customer(CustID),OrderDate DATETIME )

Foreign vital rules

Additional foreign key rules might be included when setting referential integrity, such together what to execute with the kid rows (in the assignment table) as soon as the record with the PK, component of the parental (Customer), is deleted or adjusted (updated). Because that example, the modify Relationships window in MS accessibility (see figure 9.1) mirrors two added options for FK rules: Cascade Update and also Cascade Delete. If these space not selected, the device will protect against the deletion or update of PK values in the parental table (Customer table) if a child record exists. The child record is any kind of record v a equivalent PK.

In part databases, secondary option exist when selecting the Delete option called collection to Null. In this is chosen, the PK row is deleted, however the FK in the kid table is set to NULL. Despite this creates an orphan row, that is acceptable.

Enterprise Constraints

Enterprise limit – sometimes referred to as semantic constraints – are additional rules specified by users or database administrators and can be based on multiple tables.

Here are some examples.

A class deserve to have a preferably of 30 students.A teacher deserve to teach a best of four classes every semester.An employee cannot take part in an ext than five projects.The value of one employee cannot exceed the salary of the employee’s manager.

Business Rules

Business rules are acquired from users as soon as gathering requirements. The requirements-gathering process is an extremely important, and its results need to be verified by the user before the database architecture is built. If the business rules space incorrect, the architecture will it is in incorrect, and also ultimately the application constructed will not role as supposed by the users.

Some instances of service rules are:

A teacher deserve to teach countless students.A course can have a preferably of 35 students.A course have the right to be taught many times, but by only one instructor.Not all teachers teach classes.

Cardinality and connectivity

Business rules are provided to identify cardinality and also connectivity. Cardinality describes the relationship in between two data tables by expressing the minimum and maximum variety of entity occurrences connected with one event of a related entity. In figure 9.2, you deserve to see that cardinality is stood for by the innermost markings top top the partnership symbol. In this figure, the cardinality is 0 (zero) on the right and also 1 (one) top top the left.

*
Figure 9.2. Place of connectivity and also cardinality top top a relationship symbol, by A. Watt.

The outermost symbol that the connection symbol, ~ above the various other hand, represents the connectivity between the two tables. Connectivity is the relationship in between two tables, e.g., one to one or one to many. The just time it is zero is once the FK can be null. When it concerns participation, there room three options to the relationship between these entities: either 0 (zero), 1 (one) or many. In figure 9.2, for example, the connectivity is 1 (one) top top the outer, left-hand side of this line and also many on the outer, right-hand side.

Figure 9.3. Mirrors the symbol that represents a one to many relationship.

*
Figure 9.3.

In figure 9.4, both inner (representing cardinality) and also outer (representing connectivity) markers room shown. The left next of this symbol is read together minimum 1 and maximum 1. On the appropriate side, that is check out as: minimum 1 and best many.

*
Figure 9.4.

Relationship Types

The line the connects two tables, in an ERD, shows the relationship kind between the tables: one of two people identifying or non-identifying. An identifying relationship will have actually a solid heat (where the PK consists of the FK). A non-identifying relationship is shown by a damaged line and does no contain the FK in the PK. Check out the section in thing 8 the discusses weak and solid relationships for more explanation.

*
Figure 9.5. Identifying and also non-identifying relationship, through A. Watt.

Optional relationships

In one optional relationship, the FK have the right to be null or the parental table go not need to have actually a equivalent child table occurrence. The symbol, shown in number 9.6, illustrates one kind with a zero and three prongs (indicating many) i beg your pardon is taken as zero OR many.

Figure 9.6.

For example, if girlfriend look at the stimulate table ~ above the right-hand next of figure 9.7, you’ll notice that a customer doesn’t need to ar an bespeak to it is in a customer. In various other words, the many side is optional.

*
Figure 9.7. Example intake of a zero to numerous optional connection symbol, by A. Watt.

The connection symbol in number 9.7 can likewise be check out as follows:

Left side: The bespeak entity have to contain a minimum the one related entity in the client table and a preferably of one related entity.Right side: A client can place a minimum of zero assignment or a best of numerous orders.

Figure 9.8 mirrors another kind of optional connection symbol through a zero and one, an interpretation zero OR one.  The one side is optional.

*
Figure 9.8.

Figure 9.9 gives an instance of how a zero to one symbol can be used.

*
Figure 9.9. Example intake of a zero to one optional connection symbol, by A. Watt.

Mandatory relationships

In a mandatory relationship, one entity occurrence requires a matching entity occurrence. The symbol for this relationship mirrors one and only one as presented in figure 9.10. The one next is mandatory.

*
Figure 9.10

See number 9.11 for an instance of exactly how the one and only one mandatory symbol is used.

*
Figure 9.11. Example of a one and only one mandatory partnership symbol, by A. Watt.

Figure 9.12 illustrates what a one to numerous relationship price looks like where the many side is mandatory.

*
Figure 9.12.

Refer to number 9.13 for an example of how the one to numerous symbol might be used.

*
Figure 9.13. Example of a one to numerous mandatory partnership symbol, through A. Watt.

So far we have actually seen the the innermost next of a partnership symbol (on the left-side that the symbol in figure 9.14) deserve to have a 0 (zero) cardinality and also a connectivity of numerous (shown top top the right-side that the symbol in number 9.14), or one (not shown).

Figure 9.14

However, the cannot have a connectivity the 0 (zero), as presented in figure 9.15. The connectivity have the right to only be 1.

*
Figure 9.15.

The connectivity symbols display maximums. Therefore if you think about it logically, if the connectivity symbol on the left side mirrors 0 (zero), climate there would be no connection between the tables.

The means to check out a partnership symbol, such together the one in number 9.16, is together follows.

The CustID in the stimulate table must additionally be uncovered in the customer table a minimum the 0 and a preferably of 1 times.The 0 way that the CustID in the bespeak table may be null.The left-most 1 (right prior to the 0 representing connectivity) claims that if there is a CustID in the bespeak table, it have the right to only it is in in the customer table once.When you check out the 0 symbol for cardinality, you deserve to assume 2 things: Tthe FK in the stimulate table allows nulls, andthe FK is not part of the PK because PKs should not contain null values.

See more: Persona 3 Lead Medal &Middot; Lord Yuan Shu, Lead Medal (Spoilers

*
Figure 9.16. The relationship between a client table and also an order table, by A. Watt.