Alterando muitas Foreign Keys [Resolvido]

e como listar as PK’s de tabelas num select ??? alguem sabe…

estou dando um select nas constraints e fazer a gramatica virar magica… mas nao sei como listar só PK’s de tabelas…

alguem sabe ???

Dependendo da alteração q vc precisa fazer, tem q ser manual mesmo.

No Oracle, para listar as PKs de suas tabelas , o SQL é esse:

select table_name,constraint_name from user_constraints where constraint_type='P'

Quer saber as colunas envolvidas e outros tipos de constraints?

Estude o dicionário de dados!

problema... quando faço o filtro:

constraint_type='P'

select constraint_name, table_name, r_constraint_name

from user_constraints 
where constraint_type like 'R'  ORDER BY TABLE_NAME

:cry: será q tem jeito !?

Bom, vc poderia ter uma "ajuda" com JDBC
SuaConnection.getMetaData().getExportedKeys(parametros)
devolve algumas informações…

ResultSet java.sql.DatabaseMetaData.getExportedKeys(String catalog, String schema, String table)
Retrieves a description of the foreign key columns that reference the given table’s primary key columns (the foreign keys exported by a table). They are ordered by FKTABLE_CAT, FKTABLE_SCHEM, FKTABLE_NAME, and KEY_SEQ.

Each foreign key column description has the following columns:

PKTABLE_CAT String => primary key table catalog (may be null)

PKTABLE_SCHEM String => primary key table schema (may be null)

PKTABLE_NAME String => primary key table name

PKCOLUMN_NAME String => primary key column name

FKTABLE_CAT String => foreign key table catalog (may be null) being exported (may be null)

FKTABLE_SCHEM String => foreign key table schema (may be null) being exported (may be null)

FKTABLE_NAME String => foreign key table name being exported

FKCOLUMN_NAME String => foreign key column name being exported

KEY_SEQ short => sequence number within foreign key

UPDATE_RULE short => What happens to foreign key when primary is updated:

importedNoAction - do not allow update of primary key if it has been imported

importedKeyCascade - change imported key to agree with primary key update

importedKeySetNull - change imported key to NULL if its primary key has been updated

importedKeySetDefault - change imported key to default values if its primary key has been updated

importedKeyRestrict - same as importedKeyNoAction (for ODBC 2.x compatibility)

DELETE_RULE short => What happens to the foreign key when primary is deleted.

importedKeyNoAction - do not allow delete of primary key if it has been imported

importedKeyCascade - delete rows that import a deleted key

importedKeySetNull - change imported key to NULL if its primary key has been deleted

importedKeyRestrict - same as importedKeyNoAction (for ODBC 2.x compatibility)

importedKeySetDefault - change imported key to default if its primary key has been deleted

FK_NAME String => foreign key name (may be null)

PK_NAME String => primary key name (may be null)

DEFERRABILITY short => can the evaluation of foreign key constraints be deferred until commit

importedKeyInitiallyDeferred - see SQL92 for definition

importedKeyInitiallyImmediate - see SQL92 for definition

importedKeyNotDeferrable - see SQL92 for definition

Parameters:

catalog a catalog name; must match the catalog name as it is stored in this database; "" retrieves those without a catalog; null means that the catalog name should not be used to narrow the search

schema a schema name; must match the schema name as it is stored in the database; "" retrieves those without a schema; null means that the schema name should not be used to narrow the search

table a table name; must match the table name as it is stored in this database

Returns:

a ResultSet object in which each row is a foreign key column description

Throws:

SQLException if a database access error occurs

See Also:

getImportedKeys</blockquote>

pelo metadata da conecction vc consegue uma lista das tabelas tb, então nao sei se teria como vc "automatizar" isso…

Se nao for isso que procura, desculpe…

select a.table_name,
       DECODE(A.POSITION, 1,  a.constraint_name ||' ('||C.TABLE_NAME||')'  , NULL) CONST,
       a.column_name
from user_cons_columns a, user_constraints b, USER_CONSTRAINTS C
where a.owner=b.owner
  and a.table_name=b.table_name
  and a.constraint_name=b.constraint_name
  AND B.R_CONSTRAINT_NAME = C.CONSTRAINT_NAME(+)
order by a.table_name,
             decode(b.constraint_type,'P','A',b.constraint_type),
             b.constraint_name;

Estude também um pouco de SQL!!!

pessoal...

os scripts levam ao mesmo lugar, preciso da PK na mesma linha da minha FK ... mas bem.. acho q estou me confundindo tb.. acho q nao é por aki... :cry:

alter table documento  
    drop constraint FK_DOCUMENTO_USUARIO;  
   
 alter table documento  
    add constraint FK_DOC_USU foreign key (USU_ID)  
     references USUARIO (USU_ID)

com certeza nao é simples...
é um script mesmo, para alterar o nome de toda FK q nao esteja na ordem gramatical do banco...
e essa FK depende do nome da PK da tabela mãe e da PK referenciada.... igual ao código....

:cry:

alguem pode me mostrar outro caminho ???

Era só dar uma garibada no meu SELECT (acho que SQL/dicionário de dados do Oracle não é o seu forte)!!!

select 'alter table ' || a.table_name || ' add constraint ' || a.constraint_name || ' foreign key (' || a.column_name || ') references ' || d.table_name || '(' || d.column_name || ')' SCRIPT
from user_cons_columns a, user_constraints b, USER_CONSTRAINTS C, user_cons_columns d
where b.constraint_type = 'R'
  and a.owner = b.owner
  and a.table_name = b.table_name
  and a.constraint_name = b.constraint_name
  and d.owner = c.owner
  and d.table_name = c.table_name
  and d.constraint_name = c.constraint_name
  AND B.R_CONSTRAINT_NAME = C.CONSTRAINT_NAME(+)

O “alter table drop FK” eu deixo como tarefa de casa…

oyama:

O "alter table drop FK" eu deixo como tarefa de casa... :)

é isso aí... e tarefa feita !! heheh

select  'alter table ' || a.table_name || 
        ' drop constraint ' || a.constraint_name || ';' EXCLUIR
        
        
from    user_cons_columns a,
        user_constraints b, 
        user_constraints c, 
        user_cons_columns d
 
where   b.constraint_type = 'R'
and 	b.delete_rule = 'CASCADE'  
and     a.owner = b.owner  
and     a.table_name = b.table_name  
and     a.constraint_name = b.constraint_name  
and     d.owner = c.owner  
and     d.table_name = c.table_name  
and     d.constraint_name = c.constraint_name  
and     b.r_constraint_name = c.constraint_name

Muito obrigado a todos que ajudaram!

é q ainda sou basicao no SQL heheh
valeu! :wink: