CODEWITHSUNDEEP
sqloracle-databaseindexingtriggersconstraints
James
James

Reputation: 377

Can I alter the constraints of one table by using the constraints of another table?

I had to drop a table and remake it using an archive. In the process, I lost the table's constraints--things like the primary key--triggers, indices, and more. I have, however, the same table on a different DB, which has all the appropriate constraints.

I have tried adding the constraints, triggers, and indices manually, but there are just too many.

I was wondering if I could do something like:

alter table t73
  modify col_n....col_n+1
  using (select constraints from t73@otherdb)

Upvotes: 1

Views: 70

Answers (2)

Moudiz
Moudiz

Reputation: 7377

this is below example how you can use dbms_metadata.get_ddl oracle package

create table EX_EMPLOYEe ( id number(5) null, name varchar2(100))
/
alter table ex_Employee add constraint PK_EX_EMPLOYEE primary key (id)
/
alter table ex_Employee add constraint FK_EX_EMPLOYEE foreign key (id)
references ex_Employee1 (id)
/
create table EX_EMPLOYEe1 ( id number(5) null, name varchar2(100))
/
alter table ex_Employee1 add constraint PK_EX_EMPLOYEE1 primary key (id)

alter table SYS_PARAM_KEY_LABEL
  add constraint FK1_SYS_PARAM_KEY_LABEL foreign key (KEY_GROUP_ID)
  references SYS_PARAM_KEY_GROUP (KEY_GROUP_ID);
/
CREATE INDEX IDX_EX_EMPLOYEe on ex_employee(name)
/
  Create or replace PROCEDURE P_EX_EMPLOYEe as
  begin
  select id from ex_employee where rownum=1;
  end;
/
CREATE OR REPLACE TRIGGER TRG_EX_EMPLOYEe AFTER DELETE ON EX_EMPLOYEe
FOR EACH ROW 
BEGIN
  DELETE FROM ex_employee1 WHERE id = :OLD.ID;
END;
/
select to_char( dbms_metadata.get_ddl('CONSTRAINT', c.constraint_name)) from user_constraints c where table_name='EX_EMPLOYEE' 
and c.constraint_type='P'
union
select to_char( dbms_metadata.get_ddl('REF_CONSTRAINT', c.constraint_name)) from user_constraints c where table_name='EX_EMPLOYEE' 
and c.constraint_type='R'
union
select to_char( dbms_metadata.get_ddl('INDEX', c.index_name)) from user_indexes c where table_name='EX_EMPLOYEE'
union
select to_char( dbms_metadata.get_ddl('PROCEDURE', d.name)) from user_dependencies d where d.referenced_name='EX_EMPLOYEE'
and d.type='PROCEDURE'
union
select to_char( dbms_metadata.get_ddl('TRIGGER', d.name)) from user_dependencies d where d.referenced_name='EX_EMPLOYEE'
and d.type='TRIGGER'

Upvotes: 1

Littlefoot
Littlefoot

Reputation: 142798

No, that won't work.

What you could do is to use some GUI (like TOAD or SQL Developer), find table t73, have a look at its Script which contains all commands (CREATE TABLE, CREATE INDEX, CREATE CONSTRAINT, ...) and copy/paste the ones you need and execute them in your current database.

That would be quick.

If you want to do it right (you know, pretending you know what you're doing, just like I do), then see DBMS_METADATA.GET_DDL and extract those commands from the database.

The final result should be the same.

Upvotes: 2

Related Questions