Reading Notes-Derby Reference Manual (3)

V. Create Index

Derby supports up to 16 index keys.

The index name cannot exceed 128 characters.

In the same index, each column can appear only once. Different indexes do not have this restriction.

The unique index checks data integrity.

The table and the corresponding index should be in the same schema.

Derby creates an index for each column in ascending order by default.

When a column has a uniqueness, primary key, or foreign key constraint, the system automatically creates an index for it (also known as a recessive index), that is, a column with a uniqueness or primary key constraint, you cannot create an index on it. If you add uniqueness or primary key constraints to a column with a unique index, the column will have two indexes. You can use the constraint name to find the corresponding implicit index:
Select conglomeratename from SYS. sysconglomerates,
SYS. sysconstraints where
SYS. sysconglomerates. tableid = sysconstraints. tableid
And constraintname = constraint_name

The size of the index key column must be smaller than or equal to half the size of the index page. If an index is created for a column with existing data greater than previously described, the index creation fails. After an index is created, if the size of the associated key column exceeds the page size, the index insertion may fail. (The size of the key columns in an index must be equal to or smaller than half the page size. if the length of the key columns in an existing row in a table is larger than half the page size of the index, creating an index on those key columns for the table will fail. this error only occurs when creating an index if an existing row in the table fails the criteria. after an index is created, inserts may fail if the size of their associated key exceeds the criteria .)

After an index is created for a table, the prepared statements based on the table will become invalid and must be re-compiled. The opened cursor can continue to be used and will not be affected (Prepared statements that involve select, insert, update, update where current, delete, and delete where current on the table referenced by the create index statement are invalidated when the index is created. open cursors on the table are not affected .).

6. Create role

Only the database creator has the permission to create a role and must set the data attribute Derby. database. sqlauthorization to true.

Derby tries to avoid conflicts between database user names and role names. When a user name is the same as the role name, the user cannot connect to the database.

The role name cannot start with sys or sys and cannot be the word public or public.

VII. Create Schema

Only the database creator has the permission to create a schema, and the data attribute Derby. database. sqlauthorization must be set to true.

Unlike the SQL standard, Derby only allows the user name as the authorization parameter, and the role name is not allowed.

All schemas starting with sys/sys belong to the system-level schema.

8. Create Synonym

You can create a semantic language for tables and views in the same schema or different schemas, or even create another semantic language for other semantic languages (but you cannot create an infinite loop semantic language: synonym A-> synonym B; synonym C-> synonym A; synonym B-> synonym C ;).

You cannot create a synonym for a non-existing table or view.

Definitions share a namespace with tables and views. Therefore, definitions, tables, and views in the same schema cannot have the same name.

It is not allowed to create a synonym in the system schema and temporary table.

9. Create Table

You can change the storage attributes, such as the page size of a table, by using syscs_util.syscs_set_database_property.

Create Table... as... you can use the column name and data type in the query result set or use only the data type as the template to create a new table. If the column name of the new table is not specified, a new table is created based on the column in The result set and the data type. The column name is specified, create a new table using the Data Type of the result set as the template (note that the number of columns must be equal ). Currently, with no data must be specified, that is, the actual data of the qury result set is not imported. Derby may be added to with data in the future, so that you can import data while generating a new table.

10. Create trigger

There is no limit on the number of triggers. You can even create multiple triggers for the same event in the same table.

Triggers are divided into pre-event and post-event triggers: Before an event occurs, the statement and constraints are executed before the trigger action; after the event is executed, the trigger action is performed after the statement and constraints are executed.

Currently, triggers capture the following three types of events: insert, update, and delete.

Referencing old and new values: the referencing
Clause

Using triggered-SQL-statements need to refer to data that
Is currently being changed by the database event that caused them to fire.
The triggered-SQL-statement might need to refer to the new (post-change or
"After") values.

Derby
Provides
You with a number of ways to refer to data that is currently being changed
By the database event that caused the trigger to fire. Changed data can be
Referred to in the triggered-SQL-statement usingTransition Variables
OrTransition
Tables
. The referencing clause allows you to provide a correlation name
Or alias for these transition variables by specifying old/newCorrelation-name
.

For
Example, if you add the following clause to the trigger definition:

REFERENCING OLD AS DELETEDROW

You
Can then refer to this correlation name in the triggered-SQL-statement:

DELETE FROM HotelAvailability WHERE hotel_id = DELETEDROW.hotel_id

The old and new transition variables map to Java. SQL. resultset
With
A single row.

Note:
Only row triggers (see statement versus row triggers
)
Can use the transition variables. Insert row triggers cannot reference
Old row. Delete row triggers cannot reference a new row.

For
Statement triggers, transitionTables
Serve as a table identifier
The triggered-SQL-statement or the trigger qualification. The referencing
Clause allows you to provide a correlation name or alias for these transition
Tables by specifying old_table/new_table as correlation-name

For example:

REFERENCING OLD_TABLE AS DeletedHotels

Allows
You to use that new identifier ( Deletedhotels
) In the triggered-SQL-statement:

DELETE FROM HotelAvailability WHERE hotel_id IN
    (SELECT hotel_id FROM DeletedHotels)

The old and
New transition tables map to Java. SQL. resultset
With cardinality
Equivalent to the number of rows affected by the triggering event.

Note:
Only
Statement triggers (see statement versus row triggers
)
Can use the transition tables. Insert statement triggers cannot reference
An old table. Delete statement triggers cannot reference a new table.

The
Referencing clause can designate only one new correlation or identifier and
Only one old correlation or identifier. Row triggers cannot designate an identifier
For a transition table and statement triggers cannot designate a correlation
For transition variables.

Statement versus row triggersyou
Have the option to specify whether a trigger is Statement trigger
Or Row trigger
.
If it is not specified in the create trigger statement via for each clause, then the trigger is
A Statement trigger
By default.

• Statement triggers

  A statement trigger fires once per triggering
  Event and regardless of whether any rows are modified by the insert, update,
  Or delete event.

• Row triggers

  A row trigger fires once for each row affected
  By the triggering event. If no rows are affected, the trigger does not fire.

Note:
An update that sets a column value to the value that it originally
Contained (for example, update t set C = C) causes a row trigger to fire,
Even though the value of the column is the same as it was prior to the triggering
Event.

Triggered-SQL-statementthe action
Defined by the trigger is called the triggered-SQL-statement (in syntax
Above,
See the last line). It has the following limitations:

• It must not contain any dynamic parameters (?).
• It must not create, alter, or drop the table upon which the trigger is
  Defined.
• It must not add an index to or remove an index from the table on which
  The trigger is defined.
• It must not add a trigger to or drop a trigger from the table upon which
  The trigger is defined.
• It must not commit or roll back the current transaction or change
  Isolation level.
• Before triggers cannot have insert, update or delete statements as their
  Action.
• Before triggers cannot call procedures that modify SQL data as their action.
• The new variable of a before trigger cannot reference A generated column.

The triggered-SQL-statement can reference database objects other
Than the table upon which the trigger is declared. If any of these database
Objects is dropped, the trigger is invalidated. If the trigger cannot be successfully
Recompiled upon the next execution, the invocation throws an exception and
The statement that caused it to fire will be rolled back.

For more information
On triggered-SQL-statements, seeDerby developer's Guide


.

Order of executionwhen a database event occurs
That fires a trigger, Derby
Performs
Actions in this order:

• It firesNo cascade before
  Triggers.
• It performs constraint checking (primary key, unique key, foreign key,
  Check ).
• It performs the insert, update, or delete.
• It firesAfter
  Triggers.

When multiple triggers are defined for the same database event
For the same table for the same trigger time (before or after), triggers are
Fired in the order in which they were created.

-- Statements and triggers:

CREATE TRIGGER t1 NO CASCADE BEFORE UPDATE ON x
  FOR EACH ROW MODE DB2SQL
  values app.notifyEmail('Jerry', 'Table x is about to be updated'); 

CREATE TRIGGER FLIGHTSDELETE
  AFTER DELETE ON FLIGHTS
  REFERENCING OLD_TABLE AS DELETEDFLIGHTS
  FOR EACH STATEMENT 
  DELETE FROM FLIGHTAVAILABILITY WHERE FLIGHT_ID IN
  (SELECT FLIGHT_ID FROM DELETEDFLIGHTS);

CREATE TRIGGER FLIGHTSDELETE3
  AFTER DELETE ON FLIGHTS
  REFERENCING OLD AS OLD
  FOR EACH ROW 
  DELETE FROM FLIGHTAVAILABILITY WHERE FLIGHT_ID = OLD.FLIGHT_ID;

Note:
You can find more examples in Derby developer's Guide


. Trigger Recursion

The maximum trigger Recursion
Depth is 16.