Attach a handler to an event for the elements in jQuery

As of jQuery 1.7, the .on() method is the preferred method for attaching event handlers to a document. For earlier versions, the .bind() method is used for attaching an event handler directly to elements.  

Handlers are attached to the currently selected elements in the jQuery object, so those elements must exist at the point the call to .bind() occurs.

• .bind( eventType [, eventData], handler(eventObject) )

  eventTypeA string containing one or more DOM event types, such as "click" or "submit," or custom event names.
  eventDataA map of data that will be passed to the event handler.
  handler(eventObject)A function to execute each time the event is triggered.

• version added: 1.4.3.bind( eventType [, eventData], preventBubble )

  eventTypeA string containing one or more DOM event types, such as "click" or "submit," or custom event names.
  eventDataA map of data that will be passed to the event handler.
  preventBubbleSetting the third argument to false will attach a function that prevents the default action from occurring and stops the event from bubbling. The default is true.

• version added: 1.4.bind( events )

  eventsA map of one or more DOM event types and functions to execute for them.
  
  
  The .on() method attaches event handlers to the currently selected set of elements in the jQuery object.

  • .on( events [, selector] [, data], handler(eventObject) )

    eventsOne or more space-separated event types and optional namespaces, such as "click" or "keydown.myPlugin".
    selectorA selector string to filter the descendants of the selected elements that trigger the event. If the selector is null or omitted, the event is always triggered when it reaches the selected element.
    dataData to be passed to the handler in event.data when an event is triggered.
    handler(eventObject)A function to execute when the event is triggered. The valuefalse is also allowed as a shorthand for a function that simply does return false.

  • version added: 1.7.on( events-map [, selector] [, data] )

    events-mapA map in which the string keys represent one or more space-separated event types and optional namespaces, and the values represent a handler function to be called for the event(s).
    selectorA selector string to filter the descendants of the selected elements that will call the handler. If the selector is null or omitted, the handler is always called when it reaches the selected element.
    dataData to be passed to the handler in event.data when an event occurs.
    
    

Year Data Type in MySQL

The YEAR type is a one-byte type used for representing years. It can be declared as YEAR(2) or YEAR(4) to specify a display width of two or four characters. The default is four characters if no width is given.

For four-digit format, MySQL displays YEAR values in YYYY format, with a range of 1901 to 2155, or 0000. For two-digit format, MySQL displays only the last two (least significant) digits; for example, 70 (1970 or 2070) or 69(2069).

You can specify input YEAR values in a variety of formats:

• As a four-digit string in the range '1901' to '2155'.
• As a four-digit number in the range 1901 to 2155.
• As a two-digit string in the range '00' to '99'. Values in the ranges '00' to '69' and '70' to '99' are converted to YEAR values in the ranges 2000 to 2069 and 1970 to 1999.
• As a two-digit number in the range 1 to 99. Values in the ranges 1 to 69 and 70 to 99 are converted to YEARvalues in the ranges 2001 to 2069 and 1970 to 1999. Note that the range for two-digit numbers is slightly different from the range for two-digit strings, because you cannot specify zero directly as a number and have it be interpreted as 2000. You must specify it as a string '0' or '00' or it is interpreted as 0000.
• As the result of a function that returns a value that is acceptable in a YEAR context, such as NOW().

Illegal YEAR values are converted to 0000.

MySQL Data Type Default Values When Creating Tables

The DEFAULT value clause in a data type specification indicates a default value for a column. With one exception, the default value must be a constant; it cannot be a function or an expression. This means, for example, that you cannot set the default for a date column to be the value of a function such as NOW() or CURRENT_DATE. The exception is that you can specify CURRENT_TIMESTAMP as the default for a TIMESTAMP column.

 

As of MySQL 5.0.2, if a column definition includes no explicit DEFAULT value, MySQL determines the default value as follows:

If the column can take NULL as a value, the column is defined with an explicit DEFAULT NULL clause. This is the same as before 5.0.2.

If the column cannot take NULL as the value, MySQL defines the column with no explicit DEFAULT clause. Exception: If the column is defined as part of a PRIMARY KEY but not explicitly as NOT NULL, MySQL creates it as aNOT NULL column (because PRIMARY KEY columns must be NOT NULL), but also assigns it a DEFAULT clause using the implicit default value. To prevent this, include an explicit NOT NULL in the definition of any PRIMARY KEYcolumn.

For data entry for a NOT NULL column that has no explicit DEFAULT clause, if an INSERT or REPLACE statement includes no value for the column, or an UPDATE statement sets the column to NULL, MySQL handles the column according to the SQL mode in effect at the time:

• If strict SQL mode is not enabled, MySQL sets the column to the implicit default value for the column data type.
• If strict mode is enabled, an error occurs for transactional tables and the statement is rolled back. For nontransactional tables, an error occurs, but if this happens for the second or subsequent row of a multiple-row statement, the preceding rows will have been inserted.

Suppose that a table t is defined as follows:

CREATE TABLE t (i INT NOT NULL);

In this case, i has no explicit default, so in strict mode each of the following statements produce an error and no row is inserted. When not using strict mode, only the third statement produces an error; the implicit default is inserted for the first two statements, but the third fails because DEFAULT(i) cannot produce a value:

INSERT INTO t VALUES();
INSERT INTO t VALUES(DEFAULT);
INSERT INTO t VALUES(DEFAULT(i));

See Section 5.1.6, “Server SQL Modes”.

For a given table, you can use the SHOW CREATE TABLE statement to see which columns have an explicitDEFAULT clause.

SERIAL DEFAULT VALUE in the definition of an integer column is an alias for NOT NULL AUTO_INCREMENT UNIQUE.

Foreign Keys in MySQL - InnoDB supports foreign key constraints

Here is a simple example that relates parent and child tables through a single-column foreign key:

CREATE TABLE parent (id INT NOT NULL,
                     PRIMARY KEY (id)
) ENGINE=INNODB;

CREATE TABLE child (id INT, parent_id INT,
                    INDEX par_ind (parent_id),
                    FOREIGN KEY (parent_id) REFERENCES parent(id)
                      ON DELETE CASCADE
) ENGINE=INNODB;

A more complex example in which a product_order table has foreign keys for two other tables. One foreign key references a two-column index in the product table. The other references a single-column index in the customertable:

CREATE TABLE product (category INT NOT NULL, id INT NOT NULL,
                      price DECIMAL,
                      PRIMARY KEY(category, id)) ENGINE=INNODB;
 

CREATE TABLE customer (id INT NOT NULL,
                       PRIMARY KEY (id)) ENGINE=INNODB;
 

CREATE TABLE product_order (no INT NOT NULL AUTO_INCREMENT,
                            product_category INT NOT NULL,
                            product_id INT NOT NULL,
                            customer_id INT NOT NULL,
                            PRIMARY KEY(no),
                            INDEX (product_category, product_id),
                            FOREIGN KEY (product_category, product_id)
                              REFERENCES product(category, id)
                              ON UPDATE CASCADE ON DELETE RESTRICT,
                            INDEX (customer_id),
                            FOREIGN KEY (customer_id)
                              REFERENCES customer(id)) ENGINE=INNODB;

InnoDB enables you to add a new foreign key constraint to a table by using ALTER TABLE:

ALTER TABLE tbl_name
    ADD [CONSTRAINT [symbol]] FOREIGN KEY
    [index_name] (index_col_name, ...)
    REFERENCES tbl_name (index_col_name,...)
    [ON DELETE reference_option]
    [ON UPDATE reference_option]

The foreign key can be self referential (referring to the same table). When you add a foreign key constraint to a table using ALTER TABLE, remember to create the required indexes first.

 

Foreign Keys and ALTER TABLE

InnoDB supports the use of ALTER TABLE to drop foreign keys:

ALTER TABLE tbl_name DROP FOREIGN KEY fk_symbol;

 

Identify Foreign Key Creation Failure

If MySQL reports an error number 1005 from a CREATE TABLE statement, and the error message refers to error 150, table creation failed because a foreign key constraint was not correctly formed.