MySQL Column/field modifiers

  fieldName itsDataType itsModifiersIfAny


NOT NULL   Null is not allowed; a value is required. 
    Inserting Null into Not Null column is error (except Timestamp and Auto_increment columns)
    Can't Update a Not Null column to Null.
    By default, Null is allowed.
    Default value of a Not Null column is zero.
  name VARCHAR(12) NOT NULL
  weight Integer Not Null



PRIMARY KEY     this column(s) is uniquely identifying value for row.
  Automatically Not Null too. No [part of a] primary key may be Null.
  Can't Insert or Update to duplicate in this column, preserving
   "entity integrity": primary key value is unique, thus each entity is unique.
  An "index" is created internally for this table, allowing faster
   searching based on the index field.  
  Rows are displayed in primary key order by default.

  pk INT PRIMARY KEY              if only one column is primary key.
  ID Int Primary Key
  SSN Char(9) Primary Key         don't use the dashes
  Name Varchar(22) Primary Key    if know no duplicate Names.

  pk...,   ..., PRIMARY KEY(pk)       Alternate: after pk declared.
  PRIMARY KEY(pk1,pk2)  if more than one column is the primary key 
   "composite primary key": each combo is unique
    Primary Key(city,state)
    Primary Key(book,chapter,verse)    Bible
    Primary Key(classCode,StudentID)   Enrollment table    

 +-----------------+-------------+------+-----+---------+-------+ 
 | Field           | Type        | Null | Key | Default | Extra |
 +-----------------+-------------+------+-----+---------+-------+
 | city            | varchar(32) | NO   | PRI | NULL    |       |
 | state           | char(2)     | NO   | PRI | NULL    |       |


AUTO_INCREMENT    generate sequence of successive unique integers.
  On one integer column only, which must be indexed (e.g. Primary Key) 
   and Not Null (automatically is Not Null if Primary Key, but not if other indexes).
  Probably should be unsigned too (negative value might mess it up??).
  When Insert Null or 0 (deprecated?), generates the next unique integer.
  Typically used to create artificial primary key.
  Reset if Delete all rows or Truncate.
  LAST_INSERT_ID() function returns most recently generated sequence number.
  Independent sequences if Auto_increment on part of a composite key.

  id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT

 +----------+------------------+------+-----+---------+----------------+
 | Field    | Type             | Null | Key | Default | Extra          |
 +----------+------------------+------+-----+---------+----------------+
 | id       | int(10) unsigned | NO   | PRI | NULL    | auto_increment |




FOREIGN KEY    this column(s) is the link to another table's primary key.
  Table is related to another by sharing a common attribute.
 in "child" table ct:
 ct_fk itsTypeAndMods,  FOREIGN KEY(ct_fk) REFERENCES pt(pt_pk) 
  #pt_pk is primary key of "parent" table pt

 Desc ct:
 +-------+------------+------+-----+---------+-------+
 | Field | Type       | Null | Key | Default | Extra |
 +-------+------------+------+-----+---------+-------+
 | ct_fk | itsType    | YES  | MUL | NULL    |       |
 
 A foreign key is an index: "MUL"

 Create Table event (
   name      Varchar(20) Not Null, ...
   FOREIGN KEY(name) REFERENCES pet(name)  )

 fk and pk can be same or different name but must be same datatype.
  "Controlled redundancy" (duplication) of this column in both tables.
  Cannot Insert/Update an fk value in child that is not in parent, 
   preserving "referential integrity".  i.e. a child must have a parent
   that exists; cannot have orphans. 
  (OK to have pk values in parent that are not in any fk of child, 
   i.e. a parent can have no children.)  
  == Foreign key may be Null but may not be a value that is not in parent's primary key column.  
  == Every non-null foreign key value must reference an existing primary key value.
  == Impossible to have an invalid value.

  Parent row can have many children:  1-to-many relation.  One PK value in parent can exist
   many times (rows) as FK value in child.  
  Parent can have many children (or none). A child has exactly one parent.

  Foreign keys are the basis of the "join" operation (pulling info from
   multiple tables by following the links.)

  Parent table must be created before child (so reference is understood).
  Parent table must be populated before child (so referential
   integrity can be checked when inserting child table rows).

  Also, must handle cases when pk value in parent Updated or Deleted
  (can 1. disallow the update/delete, 
    or 2. cascade changed value to child tables or set to Null, 
    or 3. delete child rows).??
  Added to Foreign Key modifier in child:
  ON DELETE {RESTRICT | CASCADE | SET NULL}
  ON UPDATE {RESTRICT | CASCADE | SET NULL}
 MySQL defaults: On Delete Restrict     (cannot delete a row in parent
                                  if any child has the key being deleted).
                 On Update Restrict     (cannot change pk in parent 
                                  if any child has that value in its fk).

  MySQL: tables must be InnoDB engine type (default in Windows if
   Configuration Wizard was used to install, else MyISAM is default engine.)

  #explicitly specify engine:
  CREATE TABLE t (columns_definitions) ENGINE=InnoDB;  
  ALTER TABLE t Engine=InnoDB;   #change type after creation =MyISAM
  SHOW CREATE TABLE t;   #lists engine type and foreign key constraints
 



DEFAULT constant_value     default value if none specified at Insert.
   The implicit default value is Zero.  
   If Null not allowed (i.e. NOT NULL) then zero is default value 
    (exceptions: Enum Not Null default is 1st member (!), 
                 first Timestamp column's default is current date/time,
                 Auto_increment column's default is next sequence number).   
   Default value can be used as flag instead of Null to indicate absence of
    value and to avoid use of Nulls.
   Should defaults be built-in or part of application?
   Can not specify Default value for Text and Blob columns.
  name VARCHAR(12) DEFAULT 'John Q. Public'
  num  INT  DEFAULT -1
  bonus ENUM('n','y') DEFAULT 'y'