Default Constraint -- Constriants

  The DEFAULT constraint provides a default value to a column when the INSERT INTO statement does not provide a specific value.

Example:

For example, the following SQL creates a new table called CUSTOMERS and adds five columns. Here, SALARY column is set to 5000.00 by default, so in case INSERT INTO statement does not provide a value for this column, then by default this column would be set to 5000.00.

CREATE TABLE CUSTOMERS (

       ID   INT              NOT NULL,

       NAME VARCHAR (20)     NOT NULL,

       AGE INT              NOT NULL,

       ADDRESS CHAR (25),

       SALARY   DECIMAL (18, 2) DEFAULT 5000.00,       

       PRIMARY KEY (ID)

);

If CUSTOMERS table has already been created, then to add a DEFAULT constraint to SALARY column, you would write a statement similar to the following:

ALTER TABLE CUSTOMERS MODIFY SALARY DECIMAL (18, 2) DEFAULT 5000.00;

Drop Default Constraint:

To drop a DEFAULT constraint, use the following SQL:

ALTER TABLE CUSTOMERS ALTER COLUMN SALARY DROP DEFAULT;