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
Write a stored procedure that accepts the post code in which the customer resides as the input parameter. The procedure should then use an explicit cursor to display comprehensive

Implicit Cursor Attributes The Implicit cursor attributes returns the information about the execution of an INSERT, DELETE, UPDATE, or SELECT INTO statement. The cursor attribu

Enrolment was split - SQL Example shows how relvars IS_CALLED and IS_ENROLLED_ON can be derived from the original ENROLMENT relvar, using projection in the initial assignment

Example of Tables within a Table - SQL Example: Obtaining C_ER from COURSE and EXAM_MARK SELECT CourseId, CAST (TABLE (SELECT DISTINCT StudentId, Mark FROM EXAM_MARK AS EM

Parameter and Keyword Description: label_name: This is an undeclared identifier which optionally labels a loop. When used, the label_name should be enclosed by double ang

BETWEEN and NOT BETWEEN Operator in SQL Example: Restricting exam marks to between 0 and 100 CREATE ASSERTION Marks_between_0_and_100 CHECK (NOT EXISTS (SELECT * FROM

Using Operator VALUE: As you may expect, the operator VALUE returns the value of an object. The VALUE takes its argument a correlation variable.  For illustration, to return a

Important Distinctions The list of important distinctions are given below: Value versus variable Syntax versus semantics Variable versus variable reference

Passing Cursor Parameters You use the OPEN statement to pass the parameters to a cursor. Unless you want to accept the default values, each proper parameter in the cursor decl

Implicit Cursors The Oracle implicitly opens a cursor to process each SQL statement not related with an explicitly declared cursor. The PL/SQL lets you refer to the most recen