Example of when or then constraints - sql, PL-SQL Programming

Example of WHEN or THEN Constraints

A concrete example showing how SQL supports WHEN/THEN constraints

CREATE TABLE SAL_HISTORY (EmpNo CHAR (6),

Salary INTEGER NOT NULL,

From DATE

To DATE

PERIOD FOR During (From, To),

PRIMARY KEY (EmpNo, During WITHOUT OVERLAPS)

) ;

The PERIOD FOR specification states that the From and To values in each row denote a time interval (called a period because SQL uses the term "interval" for something else). The From values are treated as closed bounds, the To values as open bounds, so a given row in SAL_HISTORY indicates that an employee was paid a certain salary from the given From date up to but not including the given To date. The specification implies the column constraint NOT NULL NOT DEFERRABLE ENFORCED for each of columns From and To. During WITHOUT OVERLAPS, which, if required, must appear as the last element of the key, specifies that if the same EmpNo value appears in two distinct rows of SAL_HISTORY, then the From and To values in those rows must denote During periods that do not overlap (have no date in common).

Posted Date: 1/18/2013 11:34:42 PM | Location : United States







Related Discussions:- Example of when or then constraints - sql, Assignment Help, Ask Question on Example of when or then constraints - sql, Get Answer, Expert's Help, Example of when or then constraints - sql Discussions

Write discussion on Example of when or then constraints - sql
Your posts are moderated
Related Questions
Use the PLS_INTEGER Datatype When you require to declare an integer variable, use the datatype PLS_INTEGER that is the most efficient numeric type. That is as the PLS_INTEGER

Special cases of projection This section describes the identity projection, r {ALL BUT}, and the projection on no attributes, r { }, which yields TABLE_DUM when r is empty, ot

%TYPE Attribute The %TYPE attribute gives the datatype of a record, field, nested table, database column, or the variable. You can use the %TYPE attribute as the datatype speci

Understanding Nested Tables Within the database, the nested tables can be considered as one-column database tables. The Oracle stores the rows of a nested table in no specific o

Some Varray Examples In SQL Plus, assume that you define an object type Project, as described below: SQL> CREATE TYPE Project AS OBJECT ( 2 project_no NUMBER(2), 3 title VARCHA

Advantages of Wrapping   The PL/SQL Wrapper convert the PL/SQL source code into a transitional form of the object code. By hiding the application internals, the Wrapper secure

Functions   The function is a subprogram that calculates a value. The Functions and procedures are structured similar, except that the functions have a RETURN clause. You can

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

Selecting Objects: Suppose that you have run the SQL*Plus script below that creates object type Person and object table persons, and that you have settled the table: CREATE

Aggregate Operators SQL Supports all of the aggregate operators mentioned in the theory book and many more besides. The syntax, however, involves an unusual trick that SQL cal