Constraint

Para cambiar las restricciones y la clave primaria de una tabla debemos usar ALTER TABLE.

Crear una clave primaria (primary key):

ALTER TABLE T_PEDIDOS ADD CONSTRAINT PK_PEDIDOS
      PRIMARY KEY (numpedido,lineapedido);

Crear una clave externa, para integridad referencial (foreign key):

ALTER TABLE T_PEDIDOS ADD CONSTRAINT FK_PEDIDOS_CLIENTES
      FOREIGN KEY (codcliente) REFERENCES T_CLIENTES (codcliente));

Crear un control de valores (check constraint):

ALTER TABLE T_PEDIDOS ADD CONSTRAINT CK_ESTADO
      CHECK (estado IN (1,2,3));

Crear una restricción UNIQUE:

ALTER TABLE T_PEDIDOS ADD CONSTRAINT UK_ESTADO
      UNIQUE (correosid);

Normalmente una restricción de este tipo se implementa mediante un indice unico (ver CREATE INDEX).

Borrar una restricción:

ALTER TABLE T_PEDIDOS DROP CONSTRAINT CON1_PEDIDOS;

Deshabilita una restricción:

ALTER TABLE T_PEDIDOS DISABLE CONSTRAINT CON1_PEDIDOS;

habilita una restricción:

ALTER TABLE T_PEDIDOS ENABLE CONSTRAINT CON1_PEDIDOS;

la sintaxis ALTER TABLE para restricciones es:

   ALTER TABLE [esquema.]tabla
      constraint_clause,...
          [ENABLE enable_clause | DISABLE disable_clause]
              [{ENABLE|DISABLE} TABLE LOCK]
                 [{ENABLE|DISABLE} ALL TRIGGERS];

donde constraint_clause puede ser alguna de las siguientes entradas:

   ADD out_of_line_constraint(s)
   ADD out_of_line_referential_constraint
   DROP PRIMARY KEY [CASCADE] [{KEEP|DROP} INDEX]
   DROP UNIQUE (column,...) [{KEEP|DROP} INDEX]
   DROP CONSTRAINT constraint [CASCADE]
   MODIFY CONSTRAINT constraint constrnt_state
   MODIFY PRIMARY KEY constrnt_state
   MODIFY UNIQUE (column,...) constrnt_state
   RENAME CONSTRAINT constraint TO new_name

donde a su vez constrnt_state puede ser:

 
    [[NOT] DEFERRABLE] [INITIALLY {IMMEDIATE|DEFERRED}]
       [RELY | NORELY] [USING INDEX using_index_clause]
          [ENABLE|DISABLE] [VALIDATE|NOVALIDATE]
              [EXCEPTIONS INTO [schema.]table]

Borrar una restricción:

   ALTER TABLE T_PEDIDOS DROP CONSTRAINT CON1_PEDIDOS;

INFO IMPORTANTE:

Oracle "Check" Constraint: This constraint validates incoming columns at row insert time. For example, rather than having an application verify that all occurrences of region are North, South, East, or West, an Oracle check constraint can be added to the table definition to ensure the validity of the region column. 
Not Null Constraint: This Oracle constraint is used to specify that a column may never contain a NULL value. This is enforced at SQL insert and update time. 
Primary Key Constraint: This Oracle constraint is used to identify the primary key for a table. This operation requires that the primary columns are unique, and this Oracle constraint will create a unique index on the target primary key. 
References Constraint: This is the foreign key constraint as implemented by Oracle. A references constraint is only applied at SQL insert and delete times.  At SQL delete time, the references Oracle constraint can be used to ensure that an employee is not deleted, if rows still exist in the DEPENDENT table. 
Unique Constraint: This Oracle constraint is used to ensure that all column values within a table never contain a duplicate entry.
Oracle constraint views:
DBA
ALL
USER
dba_cons_columns
all_cons_columns
user_cons_columns
dba_constraints
all_constraints
user_constraints
dba_indexes
all_indexes
user_indexes
dba_ind_partitions
all_ind_partitions
user_ind_partitions
dba_ind_subpartitions
all_ind_subpartitions
user_ind_subpartitions
 
Oracle Constraint Standards
Oracle Primary key constraints will follow this naming convention:
PK_nnnnn 
Where nnnn = The table name that the index is built on.

 
UK_nnnnn_nn
Where nnnn = The table name that the index is built on.
                nn =  A number that makes the constraint unique.

 
FK_pppp_cccc_nn
Where pppp = The parent table name
            cccc = The child parent table name
                nn = A number that makes the constraint unique