Web System Manage constraints

Constraints control the data in table columns by preventing the input of data that logically contradicts a specific database.

Said differently, it is a way for you to disallow 'bad' data to be entered (stored) in your tables.  Users (ourselves included) make mistakes.  Constraints can be an effective means of keeping 'garbage' out of your Tables.  Common errors such as duplicate records (rows), transposing an employee's Hire Date and Birth Date or entering an hourly wage of 1.560 - when you meant 15.60 - can all be avoided by imposing Constraints.

• [Add] — opens a dialog to enter a new constraint. The type of constraint is selected in the drop-down menu on the right.
• [Edit] - opens a dialog for editing constraint attributes. The constraint editing dialog could also be opened by double-clicking a constraint in the list of constraints. You can only edit a newly created constraint.  If the changes are already saved in the database, you cannot edit but you can delete it and add a new constraint with the desired parameters. This quirk is dictated by the PostgreSQL platform.
• [Drop] —Deletes (drops) a constraint.

Younicycle, like PostgreSQL, has four types of constraints:

1 - PRIMARY KEY — This constraint prevents the input of either duplicate or null data. A PRIMARY KEY indicates that a column or group of columns can be used as a unique identifier for rows in the table. Younicycle controls the PRIMARY KEY and does not allow users to edit or delete this constraint.  The Younicycle Server Package does allow access to this key, but only a qualified PostgreSQL developer should edit any Primary key as it is an integral part of the system.

2 - UNIQUE — see below

Ensures that the data contained in a column(s) is unique with respect to all of the rows in the table. Unlike PRIMARY KEY, UNIQUE allows duplicate rows that contain null values.  Consider selecting NOT NULL for any UNIQUE column.  It is not necessary to create an Index for a UNIQUE constraint - as it is done automatically by PostgreSQL. Younicycle creates a portion of the name automatically.  The remainder of the name you do - once again, respecting the 'all alpha, lower case, no spaces with _ being allowed.

3 - CHECK — Specifies an additional constraint on the value of a column.  For example, wage_rate < 100,000 guarantees that each employee will have a salary less than 100,000.

4 - FOREIGN KEY — Specifies that the values in a column(s) must match the values appearing in some row of another table. Ensures  that values in the column(s) of the referencing table (local field) will only be the values from the referenced table.  Implied is that the values in the referenced column must be UNIQUE, so you would set that constraint 1st - then add the FOREIGN KEY constraint on the referencing column. When done, it will be listed as an available [Referenced field] see below.

What happens if a value in your referenced Column changes?  Your choices include: ON DELETE NO ACTION.  Produce an error indicating that the deletion or update would create a foreign key constraint violation. If the constraint is deferred, this error will be produced at constraint check time if there still exist any referencing rows. This is the default action. ON DELETE RESTRICT:  Produce an error indicating that the deletion or update would create a foreign key constraint violation. This is the same as NO ACTION except that the check is not deferrable. ON DELETE CASCADE:  Produce an error indicating that the deletion or update would create a foreign key constraint violation. This is the same as NO ACTION except that the check is not deferrable. ON DELETE SET NULL:  Set the referencing column(s) to null. ON DELETE SET DEFAULT:  Set the referencing column(s) to their default values. The same options are available ON UPDATE.

Note that foreign key constraints cannot be defined between temporary tables and permanent tables.

A value inserted into the referencing column(s) is MATCHED against the values of the referenced table and referenced columns using the given match type. There are three match types: FULL, PARTIAL, and SIMPLE, which is also the default. MATCH FULL will not allow one column of a multicolumn foreign key to be null unless all foreign key columns are null. MATCH SIMPLE allows some foreign key columns to be null while other parts of the foreign key are not null. MATCH PARTIAL is not yet implemented.