Creating_10G_Table.htm
Objectives:
  • Wine Database : Wine Relational Table
  • Show Constraints
  • Dropping Constraints
Create table and primary key

drop table producer;
CREATE TABLE producer
( producer_id numeric(10) not null,
producer_name varchar2(50) not null,
contact_name varchar2(50),
CONSTRAINT producer_pk PRIMARY KEY (producer_id, producer_name)
);

View Constraints
select constraint_name, constraint_type
from user_constraints
where table_name ='PRODUCER'

Dropping primary key

ALTER TABLE producer drop CONSTRAINT producer_pk;
ENABLE OR DISABLE  keys:

drop table T1;
CREATE TABLE T1
( t_id numeric(10) not null,
t_name varchar2(50) not null,
t_address varchar2(50),
CONSTRAINT t1_pk PRIMARY KEY (t_id)
);

select constraint_name, constraint_type
from user_constraints
where table_name ='T1';

ALTER TABLE T1 disable CONSTRAINT t1_pk;

 
Foreign key and primary Key

drop table producer;
drop table products;
CREATE TABLE producer
( producer_id numeric(10) not null,
producer_name varchar2(50) not null,
contact_name varchar2(50),
CONSTRAINT producer_pk PRIMARY KEY (producer_id, producer_name)
);

CREATE TABLE products
( product_id numeric(10) not null,
producer_id numeric(10) not null,
producer_name varchar2(50) not null,
item_id numeric(10) not null,
CONSTRAINT products_fk
FOREIGN KEY (producer_id,producer_name)
REFERENCES producer(producer_id,producer_name)
);

Now if you try to drop primary key you will see an error

Dropping cascading constraints: the constraint-dependencies are the one that has to dropped first with the table. The following will show an warning that the table "producer" does not exist.

DROP TABLE producer CASCADE CONSTRAINTS;
drop table producer;
drop table products;
CREATE TABLE producer
( producer_id numeric(10) not null,
producer_name varchar2(50) not null,
contact_name varchar2(50),
CONSTRAINT producer_pk PRIMARY KEY (producer_id, producer_name)
);

CREATE TABLE products
( product_id numeric(10) not null,
producer_id numeric(10) not null,
producer_name varchar2(50) not null,
item_id numeric(10) not null,
CONSTRAINT products_fk
FOREIGN KEY (producer_id,producer_name)
REFERENCES producer(producer_id,producer_name)
);

Adding key's after creating a table

drop table T2;
CREATE TABLE T2
( t_id numeric(10) not null,
t_name varchar2(50) not null,
t_address varchar2(50)
);

alter table T2 add(CONSTRAINT t2_pk PRIMARY KEY (t_id));

select constraint_name, constraint_type
from user_constraints
where table_name ='T2';