CREATE TABLE a (
a_id NUMBER(10),
city VARCHAR2(255),
CONSTRAINT pk_a PRIMARY KEY ( a_id )
);
CREATE TABLE b (
b_id NUMBER(10),
a_id NUMBER(10),
city VARCHAR2(255),
CONSTRAINT pk_b PRIMARY KEY ( b_id ),
CONSTRAINT fk_b_a FOREIGN KEY ( a_id )
REFERENCES a ( a_id )
);
INSERT INTO a VALUES(1,'Mumbai');
INSERT INTO a VALUES(2,'Pune');
INSERT INTO b VALUES(1,1,'Mumbai');
INSERT INTO b VALUES(2,2,'Pune');
COMMIT;
I need to generate a create table
DDL scripts in order of parent-child. Suppose, I have two tables A
and B
then the script should give me DDL for table A
and then for B
.
From the below query I can get the parent-child tables but how to generate DDL out of it.
SELECT p.table_name PARENT_TABLE, c.table_name CHILD_TABLE
FROM dba_constraints p, dba_constraints c
WHERE (p.constraint_type = 'P' OR p.constraint_type = 'U')
AND c.constraint_type = 'R'
AND p.constraint_name = c.r_constraint_name
AND p.table_name = UPPER('A')
and p.owner='TAM';
And same for INSERT
DML scripts. I need to have the DML scripts in the order of parent-child. I am stuck in generating the scripts.