DEFAULT and NOT NULL

 < Day Day Up > 

DEFAULT and NOT NULL

Choosing a default value for a column saves you the effort of manually entering (or coding) a value if none is entered by a user, as well as sending this data from the client application to the database server. If you find that a table has fields that are usually filled with the same values, consider prepopulating them by using the DEFAULT constraint.

Be certain that you specify the NOT NULL constraint when creating the table if your application demands that a column is never left empty.

For example, suppose that you need a table to hold point-in-time snapshots of the airline's efforts to find a missing piece of luggage. One of the columns in the table will track the type of update that's being entered (for example, data entered by the airline, a phone call from the customer). If most of the entries in this table are generated by other programs (that is, entered by the airline), you could simply set the default for the column to be "Automatic update: High-Hat."

 CREATE TABLE missing_luggage_tracking (     missing_luggage_tracking_id INT AUTO_INCREMENT PRIMARY KEY,     missing_luggage_id INT,     entry_type VARCHAR(30) NOT NULL DEFAULT 'Automatic update: High-Hat', ... ...     FOREIGN KEY (missing_luggage_id) REFERENCES missing_luggage(missing_luggage_id) ON DELETE CASCADE ) ENGINE = INNODB; 

MySQL's Table Editor will show the constraints you've placed on the entry_type column, as shown in Figure 5.3.

Figure 5.3. Using the MySQL Table Editor to set a default value.


Your INSERT statement now does not need to even mention this column unless you have a different value to insert. If there is also a human interface working against this table, that application could provide its own values in SQL (for example, 'Customer phone call'), which would override the default:

 INSERT INTO missing_luggage_tracking (missing_luggage_id) VALUES (29320); INSERT INTO missing_luggage_tracking (missing_luggage_id, entry_type) VALUES (29320, 'Customer phone call'); 

     < Day Day Up > 


    MySQL Database Design and Tuning
    MySQL Database Design and Tuning
    ISBN: 0672327651
    EAN: 2147483647
    Year: 2005
    Pages: 131

    flylib.com © 2008-2017.
    If you may any questions please contact us: flylib@qtcs.net