CONSTRAINS IN ORACLE APPS

Examples in constrain

 CHECK
========

For example:

CREATE TABLE suppliers
(     supplier_id     numeric(4),    
    supplier_name     varchar2(50),    
    CONSTRAINT check_supplier_name
    CHECK (supplier_name = upper(supplier_name))
);


CREATE TABLE suppliers
(     supplier_id     numeric(4),    
    supplier_name     varchar2(50),    
    CONSTRAINT check_supplier_id
    CHECK (supplier_id BETWEEN 100 and 9999)
);

 ALTER COMMAND:-
=================

    ALTER TABLE suppliers
    add CONSTRAINT check_supplier_name
       CHECK (supplier_name IN ('IBM', 'Microsoft', 'NVIDIA'));


  Drop a Check Constraint
===========================

ALTER TABLE table_name
drop CONSTRAINT constraint_name;


ALTER TABLE suppliers
drop CONSTRAINT check_supplier_id;


  UNIQUE
===========
CREATE TABLE supplier
(     supplier_id     numeric(10)     not null,
    supplier_name     varchar2(50)     not null,
    contact_name     varchar2(50),    
    CONSTRAINT supplier_unique UNIQUE (supplier_id)
);


CREATE TABLE supplier
(     supplier_id     numeric(10)     not null,
    supplier_name     varchar2(50)     not null,
    contact_name     varchar2(50),    
    CONSTRAINT supplier_unique UNIQUE (supplier_id, supplier_name)
);


 ALTER COMMAND:-
=================

ALTER TABLE supplier
add CONSTRAINT supplier_unique UNIQUE (supplier_id);


 Drop a UNIQUE Constraint
===========================

ALTER TABLE supplier
drop CONSTRAINT supplier_unique;]


 Primary Keys
===============

CREATE TABLE supplier
(     supplier_id     numeric(10)     not null,
    supplier_name     varchar2(50)     not null,
    contact_name     varchar2(50),    
    CONSTRAINT supplier_pk PRIMARY KEY (supplier_id)
);


CREATE TABLE supplier
(     supplier_id     numeric(10)     not null,
    supplier_name     varchar2(50)     not null,
    contact_name     varchar2(50),    
    CONSTRAINT supplier_pk PRIMARY KEY (supplier_id, supplier_name)
);



 ALTER COMMAND:-
=================

ALTER TABLE supplier
add CONSTRAINT supplier_pk PRIMARY KEY (supplier_id, supplier_name);

  Drop a UNIQUE Constraint
============================

ALTER TABLE supplier
drop CONSTRAINT supplier_pk;


   Disable a Primary Key
============================

ALTER TABLE supplier
disable CONSTRAINT supplier_pk;

ALTER TABLE supplier
enable CONSTRAINT supplier_pk;



    Foreign Keys
=====================

CREATE TABLE supplier
(     supplier_id     numeric(10)     not null,
    supplier_name     varchar2(50)     not null,
    contact_name     varchar2(50),    
    CONSTRAINT supplier_pk PRIMARY KEY (supplier_id)
);

   LINK IN Primary Key and Foreign Keys
=================================================


CREATE TABLE products
    (     product_id     numeric(10)     not null,
        supplier_id     numeric(10)     not null,
        CONSTRAINT fk_supplier
          FOREIGN KEY (supplier_id)
          REFERENCES supplier(supplier_id)
    );
  
==========================================================

CREATE TABLE supplier
(     supplier_id     numeric(10)     not null,
    supplier_name     varchar2(50)     not null,
    contact_name     varchar2(50),    
    CONSTRAINT supplier_pk PRIMARY KEY (supplier_id, supplier_name)
);



   LINK IN Primary Key and Foreign Keys
=================================================

CREATE TABLE products
(     product_id     numeric(10)     not null,
    supplier_id     numeric(10)     not null,
    supplier_name     varchar2(50)     not null,
    CONSTRAINT fk_supplier_comp
      FOREIGN KEY (supplier_id, supplier_name)
      REFERENCES supplier(supplier_id, supplier_name)
);

Using an ALTER TABLE statement
===================================
ALTER TABLE products
add CONSTRAINT fk_supplier
  FOREIGN KEY (supplier_id)
  REFERENCES supplier(supplier_id);
 
 
 
ALTER TABLE products
add CONSTRAINT fk_supplier
  FOREIGN KEY (supplier_id, supplier_name)
  REFERENCES supplier(supplier_id, supplier_name);