A number of additional constraints, or modifiers, can be applied to a field to increase the consistency of the data that will be entered into it and to mark it as “special” in some way. These modifiers can either appear as part of the field definition, if they apply only to that specific field (for example, a default value for a field), or after all the field definitions, if they relate to multiple fields (for example, a multicolumn primary key).
• To specify whether the field is allowed to be empty or if it must necessarily be filled with data, place the NULL and NOT NULL modifiers after each field definition.
• To specify a default value for a field, use the DEFAULT modifier. This default value is used if no value is specified for that field when inserting a record. In the absence of a DEFAULT modifier for NOT NULL fields, MySQL automatically inserts a nonthreatening default value into the field.
• To have MySQL automatically generate a number for a field (by incrementing the previous value by 1), use the AUTO_INCREMENT modifier. This is particularly useful to generate row numbers for each record in the table. However, the AUTO_INCREMENT modifier can only be applied to numeric fields that are both NOT NULL and belong to the PRIMARY KEY. A table may only contain one AUTO_INCREMENT field.
• To specify the character set for fields containing string values, use the CHARACTER SET modifier.
• To index a field, use the INDEX modifier. When a field is indexed in this manner, MySQL no longer needs to scan each row of the table for a match when performing queries; instead, it can simply look up the index. Indexing is recommended for fields that frequently appear in the WHERE, ORDER BY, and GROUP BY clauses of SELECT queries and for fields used to join tables together.
• A variant of the INDEX modifier is the UNIQUE modifier, which is a special type of index used to ensure that values entered into a field must be either unique or NULL.
• To specify a primary key for the table, use the PRIMARY KEY modifier. The PRIMARY KEY constraint can best be thought of as a combination of the NOT NULL and UNIQUE constraints because it requires values in the specified field to be neither NULL nor repeated in any other row. It thus serves as a unique identifier for each record in the table, and it should be selected only after careful thought has been given to the inter-relationships between tables.
• To specify a foreign key for a table, use the FOREIGN KEY modifier. The FOREIGN
KEY modifier links a field in one table to a field (usually a primary key) in another table, setting up a base for relationships. However, foreign keys are only supported in MySQL’s InnoDB storage engine; the FOREIGN KEY modifier is simply ignored in all other engines.
Source of Information : MCGraw Hill - SQL the Complete Reference 3rd Edition
• To specify whether the field is allowed to be empty or if it must necessarily be filled with data, place the NULL and NOT NULL modifiers after each field definition.
• To specify a default value for a field, use the DEFAULT modifier. This default value is used if no value is specified for that field when inserting a record. In the absence of a DEFAULT modifier for NOT NULL fields, MySQL automatically inserts a nonthreatening default value into the field.
• To have MySQL automatically generate a number for a field (by incrementing the previous value by 1), use the AUTO_INCREMENT modifier. This is particularly useful to generate row numbers for each record in the table. However, the AUTO_INCREMENT modifier can only be applied to numeric fields that are both NOT NULL and belong to the PRIMARY KEY. A table may only contain one AUTO_INCREMENT field.
• To specify the character set for fields containing string values, use the CHARACTER SET modifier.
• To index a field, use the INDEX modifier. When a field is indexed in this manner, MySQL no longer needs to scan each row of the table for a match when performing queries; instead, it can simply look up the index. Indexing is recommended for fields that frequently appear in the WHERE, ORDER BY, and GROUP BY clauses of SELECT queries and for fields used to join tables together.
• A variant of the INDEX modifier is the UNIQUE modifier, which is a special type of index used to ensure that values entered into a field must be either unique or NULL.
• To specify a primary key for the table, use the PRIMARY KEY modifier. The PRIMARY KEY constraint can best be thought of as a combination of the NOT NULL and UNIQUE constraints because it requires values in the specified field to be neither NULL nor repeated in any other row. It thus serves as a unique identifier for each record in the table, and it should be selected only after careful thought has been given to the inter-relationships between tables.
• To specify a foreign key for a table, use the FOREIGN KEY modifier. The FOREIGN
KEY modifier links a field in one table to a field (usually a primary key) in another table, setting up a base for relationships. However, foreign keys are only supported in MySQL’s InnoDB storage engine; the FOREIGN KEY modifier is simply ignored in all other engines.
Source of Information : MCGraw Hill - SQL the Complete Reference 3rd Edition
|
|
0 comments
Post a Comment