Example of add constraint in sql, PL-SQL Programming

Assignment Help:

Example of ADD CONSTRAINT in SQL

Example: Alternative formulation for MAX_ENROLMENTS

ALTER TABLE IS_ENROLLED_ON

ADD CONSTRAINT MAX_ENROLMENTS

CHECK ((SELECT COUNT (*) FROM IS_ENROLLED_ON) <= 20000);

Explanation

  • ALTER TABLE IS_ENROLLED_ON announces that an alteration to the definition of base table IS_ENROLLED_ON is being specified.
  • ADD CONSTRAINT MAX_ENROLMENTS states that the alteration in question is the addition of something SQL calls a table constraint, and its name is MAX_ENROLMENTS. A table constraint is a condition that is required to be satisfied by every row appearing in the base table for which that constraint is defined. Thus, in general, it is an open expression of the kind that can appear as the condition of a WHERE clause. In this example the condition is in fact a closed expression-it contains no reference to a column of IS_ENROLLED_ON-and thus if it is satisfied by one row of that table, then it is satisfied by all of them.
  • A table constraint is one that must be satisfied by every row of the applicable table, it is always satisfied when the applicable table is empty-there is no row for which the constraint fails. In the case at hand, this is not a problem, because obviously, when IS_ENROLLED_ON is empty, then its cardinality-zero-does not exceed 20,000. However, suppose a constraint was required to the effect that IS_ENROLLED_ON must never be empty. That could be achieved by changing <= 20000 to > 0 , but the same change to Example would be ineffectual: when IS_ENROLLED_ON is empty, it contains no row that fails to satisfy the constraint. That is why SQL is incomplete with respect to database integrity when support for CREATE ASSERTION is absent.

Related Discussions:- Example of add constraint in sql

Cursor variables, Cursor Variables Similar to a cursor, cursor variable...

Cursor Variables Similar to a cursor, cursor variable points to the current row in the result set of a multi-row query. But, dissimilar a cursor, a cursor variable can be opene

Collection methods in pl sql, Collection Methods:   The collection me...

Collection Methods:   The collection method is a built-in function or procedure which operates on the collections and is called using the dot notation. The methods like the C

Role of abstraction in pl/sql, Role of Abstraction in pl/sql: The abst...

Role of Abstraction in pl/sql: The abstraction is a high-level description or model of a real-world entity. The Abstractions keep our daily lives convenient. They help us ca

Check constraints in sql, CHECK Constraints in SQL A CHECK constraint ...

CHECK Constraints in SQL A CHECK constraint is a table constraint defined using the key word CHECK, as already illustrated in several examples in this chapter. In particular,

Group and having query, Using a join on 2 tables, select all columns and 10...

Using a join on 2 tables, select all columns and 10 rows from the 2 tables without the use of a Cartesian product. Query: SELECT * FROM EMPLOYEE1 E JOIN STAFF S ON E.EMP_

What is a record, What Is a Record  ? A record is a group of related...

What Is a Record  ? A record is a group of related data items that stored in the fields, each with its own name and datatype. Assume that you have different data about an em

Expressions - syntax, Expressions   An expression is a randomly comple...

Expressions   An expression is a randomly complex combination of the constants, variables, literals, operators, & function calls. The simplest expression is the single variabl

Using rename in combination with join - sql, Using RENAME in combination wi...

Using RENAME in combination with JOIN - SQL Example gives pairs of ids of students having the same name, by joining two renamings of IS_CALLED. Example gives an equivalent ex

Long and long raw in pl/sql, LONG and LONG RAW You use the LONG datatyp...

LONG and LONG RAW You use the LONG datatype to store the variable-length character strings. The LONG datatype is such as the VARCHAR2 datatype, except that the maximum length o

Sql is a database language, SQL Is a Database Language: The commands g...

SQL Is a Database Language: The commands given to a DBMS by an application are written in the database language of the DBMS. The term data sublanguage is sometimes used instea

Write Your Message!

Captcha
Free Assignment Quote

Assured A++ Grade

Get guaranteed satisfaction & time on delivery in every assignment order you paid with us! We ensure premium quality solution document along with free turntin report!

All rights reserved! Copyrights ©2019-2020 ExpertsMind IT Educational Pvt Ltd