ALTER TABLE Table and Column Constraint clauses:

Column_constraint: 

   [CONSTRAINT constraint ]  {PRIMARY KEY | UNIQUE } 
         [CLUSTERED | NONCLUSTERED ] 
           [WITH FILLFACTOR = fillfactor  
           [WITH ( index_option [ , ...n ] ) 
              [ON storage_option]

   [CONSTRAINT constraint ] 
      [FOREIGN KEY ] 
        REFERENCES [schema. ] referenced_table [ ( ref_column ) ] 
           [ON DELETE { NO ACTION | CASCADE | SET NULL | SET DEFAULT } ] 
              [ON UPDATE { NO ACTION | CASCADE | SET NULL | SET DEFAULT } ] 
                 [NOT FOR REPLICATION ] 

   [CONSTRAINT constraint ] 
      CHECK [ NOT FOR REPLICATION ] ( logical_expression ) 


table_constraint:

   [CONSTRAINT constraint] 
      {PRIMARY KEY | UNIQUE } 
         [CLUSTERED | NONCLUSTERED] 
            (column [ ASC | DESC ] [,...n] ) 
               [WITH FILLFACTOR = fillfactor   ]
               [WITH ( index_option [, ...n] ) ]
                  [ON storage_option ] 

   [CONSTRAINT constraint] 
      FOREIGN KEY 
         ( column [,...n] ) 
            REFERENCES referenced_table [ ( ref_column [ ,...n ] ) ] 
               [ON DELETE { NO ACTION | CASCADE | SET NULL | SET DEFAULT } ] 
                  [ON UPDATE { NO ACTION | CASCADE | SET NULL | SET DEFAULT } ] 
                     [NOT FOR REPLICATION ] 

   [ CONSTRAINT constraint ] 
        CHECK [ NOT FOR REPLICATION ] ( logical_expression ) 


storage_options:
    partition_scheme ( partition_column ) 
    filegroup 
    "default" 

index_options:
   PAD_INDEX = {ON | OFF} 
   FILLFACTOR = fillfactor 
   IGNORE_DUP_KEY = {ON | OFF} 
   STATISTICS_NORECOMPUTE = {ON | OFF} 
   ALLOW_ROW_LOCKS = {ON | OFF} 
   ALLOW_PAGE_LOCKS = {ON | OFF} 

Arguments:

ON <partition_scheme> - Table with partitions stored on one or more filegroups.
ON filegroup - Table is stored in the named filegroup.
ON "default" (or if ON is not specified at all) - The table is stored on the default filegroup.

DEFAULT - A value provided for the column when nothing is explicitly supplied during an insert.
IDENTITY - An identity column, one per table: tinyint, smallint, int, bigint, decimal(p,0), or numeric(p,0)

NOT FOR REPLICATION - Do not enforce constraints for the replication agent (IDENTITY, FOREIGN KEY and CHECK constraints.)
CONSTRAINT - Define a PRIMARY KEY, NOT NULL, UNIQUE, FOREIGN KEY, or CHECK constraint.

NULL / NOT NULL - Whether the column can accept null values.

CLUSTERED | NONCLUSTERED - The type of index is created for a PRIMARY KEY or UNIQUE constraint. PRIMARY KEY constraints default to CLUSTERED, and UNIQUE constraints default to NONCLUSTERED.

FOREIGN KEY REFERENCES - A constraint to provide referential integrity for the data, requires a UNIQUE INDEX on the referenced table.

max - Applies only to the varchar, nvarchar, and varbinary data types for storing 2^31-1 bytes of character / binary / Unicode data.

WITH CHECK / WITH NOCHECK - Is data in the table validated against the new FOREIGN KEY or CHECK constraint.

Examples

--Add a default constraint
ALTER TABLE MyTable 
ADD CONSTRAINT MyNewColumn_dflt
DEFAULT 50 FOR MyNewColumn ;
GO

-- add a date/time column defaulting to today
ALTER TABLE MyTable
ADD MyDateColumn smalldatetime NULL
CONSTRAINT MyDateColumn_dflt
DEFAULT GETDATE() WITH VALUES ;
GO

"At a good table we may go to school" - Thomas Fuller

Related commands:

CREATE TABLE
DROP TABLE

Equivalent Oracle command:

ALTER TABLE