obdalib_parsers_constraint_syntax

Table of Contents Data Constraints Check Constraint Unique Constraint Foreign Key Constraint Primary Key Constraint Creating a Data Constraint

Data Constraints

There are four types of data constraints supported by the API:

1. Check constraint,
2. Unique constraint,
3. Foreign key constraint,
4. Primary key constraint.

Check Constraint

The syntax follows this rule:

table_name 'CHECK' ($var op {$var|CONSTANTS}) {, ($var op {$var|CONSTANTS}) ...}

A check constraint uses a keyword "check" (case insensitive) that applies to a table_name exists in the data source. The constraint works by defining one or more conditional statements that consist of a variable, an operator and a range.

• A variable is a property in the table schema.
• An operator: "<", ">", "=", "<=", ">=".
• A range can be another variable or a constant. The API supports three kinds of constant, i.e., a string constant (enclosed by quotes), a numerical constant and a boolean constant (true or false).

For example:

Employee CHECK ($position = "Staff"), ($age < 30)

Unique Constraint

The syntax follows this rule:

table_name 'UNIQUE' ($var {, $var ...})

A unique constraint uses a keyword "unique" (case insensitive) that applies to a table_name exists in the data source. The constraint defines which one or more variables have a non-identical value (thus, unique).

For example:

Employee UNIQUE ($id, $tax_code)

The example means that the employees have their own ID and tax code number, and no one has the same value for both properties.

Foreign Key Constraint

The syntax follows this rule:

t1=table_name ($var {, $var ...}) 'REFERENCES' t2=table_name {($var {, $var ...})}

The foreign key constraint uses a keyword "references" (case insensitive) that applies to two tables, t1=table_name and t2=table_name. The constraint defines the link between the two tables which is explicitly stated by the variables. Note that, in the second table, it is an optional to put the variable if both tables use the same property name.

For example:

Employee ($id) REFERENCES Department ($employee_id)

Employee ($tax_code) REFERENCES Payroll

The first examples shows that the table Employee and Department is linked together by the employee ID number that this property is known as the id and employee_id, respectively. The second example assumes both tables use the same property name called tax_code to create the link.

Primary Key Constraint

The syntax follows this rule:

table_name 'PRIMARY KEY' ($var {, $var ...})

The primary key constraint uses a keyword "primary key" (case insensitive) that applies to a table_name. The constraint defines the primary key that is used in the table.

For example:

Employee PRIMARY KEY ($id)

Creating a Data Constraint