The DEFAULT constraint is used to insert a default value into a column. The default value will be added to all new records, if no other value is specified.

CREATE TABLE Emp3 (

   EmpNo NUMBER(4) NOT NULL,

   DeptNo NUMBER(7,2) NOT NULL,

   Ename VARCHAR2(9) NOT NULL,

   Hire_Date DATE DEFAULT GETDATE ()

   );

The DEFAULT constraints may be applied to update an existing table, as shown:

ALTER TABLE Emp3 MODIFY Hire_Date DEFAULT GETDATE();

Or to drop a DEFAULT constraint from existing table:

ALTER TABLE Emp3 ALTER COLUMN Hire_Date DROP DEFAULT;

Examples

Tables being created specifying various constraints:

CREATE TABLE Dept (DeptNo NUMBER(2) CONSTRAINT Dept_DeptNo_pk PRIMARY KEY,

   Dname VARCHAR2(14),

   Loc VARCHAR2(13),

   CONSTRAINT Dept_Dname_uk UNIQUE(Dname)

   );

CREATE TABLE EMP3 (EmpNo NUMBER(4) CONSTRAINT Emp_EmpNo_pk PRIMARY KEY,

   Ename VARCHAR2(10) NOT NULL,

   Job VARCHAR2(9),

   Mgr NUMBER(4),

   HireDate DATE DEFAULT SYSDATE,

   Sal NUMBER(7, 2),

   Comm NUMBER(7, 2),

   DeptNo NUMBER(7, 2) NOT NULL,

   CONSTRAINT  Emp_DeptNo_ck  CHECK (DeptNo BETWEEN 1 AND 50), CONSTRAINT

   Emp_DeptNo_fk FOREIGN KEY (DeptNo) REFERENCES Dept(DeptNo)

   );