sql - tablas - error no se pudo crear la clave ajena phpmyadmin

Postgres e índices de claves foráneas y claves primarias

Question

¿Postgres coloca automáticamente los índices en claves externas y claves principales? ¿Cómo puedo decir? ¿Hay un comando que devolverá todos los índices en una tabla?

Answer 1

Esta consulta mostrará una lista de los índices que faltan en las claves externas , fuente original .

-- check for FKs where there is no matching index
-- on the referencing side
-- or a bad index
WITH fk_actions ( code, action ) AS (
VALUES ( ''a'', ''error'' ),
( ''r'', ''restrict'' ),
( ''c'', ''cascade'' ),
( ''n'', ''set null'' ),
( ''d'', ''set default'' )
),
fk_list AS (
SELECT pg_constraint.oid as fkoid, conrelid, confrelid as parentid,
conname, relname, nspname,
fk_actions_update.action as update_action,
fk_actions_delete.action as delete_action,
conkey as key_cols
FROM pg_constraint
JOIN pg_class ON conrelid = pg_class.oid
JOIN pg_namespace ON pg_class.relnamespace = pg_namespace.oid
JOIN fk_actions AS fk_actions_update ON confupdtype = fk_actions_update.code
JOIN fk_actions AS fk_actions_delete ON confdeltype = fk_actions_delete.code
WHERE contype = ''f''
),
fk_attributes AS (
SELECT fkoid, conrelid, attname, attnum
FROM fk_list
JOIN pg_attribute
ON conrelid = attrelid
AND attnum = ANY( key_cols )
ORDER BY fkoid, attnum
),
fk_cols_list AS (
SELECT fkoid, array_agg(attname) as cols_list
FROM fk_attributes
GROUP BY fkoid
),
index_list AS (
SELECT indexrelid as indexid,
pg_class.relname as indexname,
indrelid,
indkey,
indpred is not null as has_predicate,
pg_get_indexdef(indexrelid) as indexdef
FROM pg_index
JOIN pg_class ON indexrelid = pg_class.oid
WHERE indisvalid
),
fk_index_match AS (
SELECT fk_list.*,
indexid,
indexname,
indkey::int[] as indexatts,
has_predicate,
indexdef,
array_length(key_cols, 1) as fk_colcount,
array_length(indkey,1) as index_colcount,
round(pg_relation_size(conrelid)/(1024^2)::numeric) as table_mb,
cols_list
FROM fk_list
JOIN fk_cols_list USING (fkoid)
LEFT OUTER JOIN index_list
ON conrelid = indrelid
AND (indkey::int2[])[0:(array_length(key_cols,1) -1)] @> key_cols
),
fk_perfect_match AS (
SELECT fkoid
FROM fk_index_match
WHERE (index_colcount - 1) <= fk_colcount
AND NOT has_predicate
AND indexdef LIKE ''%USING btree%''
),
fk_index_check AS (
SELECT ''no index'' as issue, *, 1 as issue_sort
FROM fk_index_match
WHERE indexid IS NULL
UNION ALL
SELECT ''questionable index'' as issue, *, 2
FROM fk_index_match
WHERE indexid IS NOT NULL
AND fkoid NOT IN (
SELECT fkoid
FROM fk_perfect_match)
),
parent_table_stats AS (
SELECT fkoid, tabstats.relname as parent_name,
(n_tup_ins + n_tup_upd + n_tup_del + n_tup_hot_upd) as parent_writes,
round(pg_relation_size(parentid)/(1024^2)::numeric) as parent_mb
FROM pg_stat_user_tables AS tabstats
JOIN fk_list
ON relid = parentid
),
fk_table_stats AS (
SELECT fkoid,
(n_tup_ins + n_tup_upd + n_tup_del + n_tup_hot_upd) as writes,
seq_scan as table_scans
FROM pg_stat_user_tables AS tabstats
JOIN fk_list
ON relid = conrelid
)
SELECT nspname as schema_name,
relname as table_name,
conname as fk_name,
issue,
table_mb,
writes,
table_scans,
parent_name,
parent_mb,
parent_writes,
cols_list,
indexdef
FROM fk_index_check
JOIN parent_table_stats USING (fkoid)
JOIN fk_table_stats USING (fkoid)
WHERE table_mb > 9
AND ( writes > 1000
OR parent_writes > 1000
OR parent_mb > 10 )
ORDER BY issue_sort, table_mb DESC, table_name, fk_name;

Answer 2

Me encanta cómo se explica esto en el artículo Características de rendimiento de EclipseLink 2.5.

Indización de llaves foráneas

La primera característica es la indexación automática de claves externas. La mayoría de las personas asume incorrectamente que las bases de datos indexan claves externas de forma predeterminada Bueno, ellos no. Las claves primarias se indexan automáticamente, pero las claves externas no. Esto significa que cualquier consulta basada en la clave externa estará realizando exploraciones de tabla completa. Esta es cualquier relación OneToMany , ManyToMany o ElementCollection , así como muchas relaciones OneToOne y la mayoría de las consultas sobre cualquier relación que involucre uniones o comparaciones de objetos . Esto puede ser un problema importante de rendimiento, y siempre debe indexar los campos de claves externas.

Answer 3

Para una PRIMARY KEY , se creará un índice con el siguiente mensaje:

NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "index" for table "table"

Para una FOREIGN KEY , la restricción no se creará si no hay un índice en la tabla de referencia.

No se requiere un índice en la tabla de referencia (aunque se desea), y por lo tanto no se creará implícitamente.

Answer 4

PostgreSQL crea automáticamente índices en claves primarias y restricciones únicas, pero no en el lado de referencia de las relaciones de clave externa.

Cuando Pg crea un índice implícito, emitirá un mensaje de nivel NOTICE que puede ver en psql y / o en los registros del sistema, para que pueda ver cuándo sucede. Los índices creados automáticamente también son visibles en la salida de /d para una tabla.

La documentación sobre índices únicos dice:

PostgreSQL crea automáticamente un índice para cada restricción única y restricción de clave principal para imponer la unicidad. Por lo tanto, no es necesario crear un índice explícitamente para columnas de clave primaria.

y la documentación sobre constraints dice:

Dado que el BORRADO de una fila de la tabla referenciada o una ACTUALIZACIÓN de una columna referenciada requerirá un escaneo de la tabla de referencia para las filas que coincidan con el valor anterior, a menudo es una buena idea indexar las columnas de referencia. Debido a que esto no siempre es necesario, y hay muchas opciones disponibles sobre cómo indexar, la declaración de una restricción de clave externa no crea automáticamente un índice en las columnas de referencia.

Por lo tanto, usted mismo tiene que crear índices en claves foráneas si los quiere.

Tenga en cuenta que si usa claves primarias-foráneas, como 2 FK''s como PK en una tabla de M a N, tendrá un índice en el PK y probablemente no necesite crear ningún índice adicional.

Si bien generalmente es una buena idea crear un índice en (o incluir) sus columnas de clave externa del lado de referencia, no es obligatorio. Cada índice que agrega ralentiza ligeramente las operaciones de DML, por lo que paga un costo de rendimiento en cada INSERT , UPDATE o DELETE . Si el índice se usa raramente, puede que no valga la pena tenerlo.

Answer 5

Sí, para claves primarias, no, para claves externas (más en los docs ).

/d <table_name>

en "psql" muestra una descripción de una tabla que incluye todos sus índices.

Answer 6

Si desea enumerar los índices de todas las tablas en su (s) esquema (s) de su programa, toda la información está disponible en el catálogo:

select
n.nspname as "Schema"
,t.relname as "Table"
,c.relname as "Index"
from
pg_catalog.pg_class c
join pg_catalog.pg_namespace n on n.oid = c.relnamespace
join pg_catalog.pg_index i on i.indexrelid = c.oid
join pg_catalog.pg_class t on i.indrelid = t.oid
where
c.relkind = ''i''
and n.nspname not in (''pg_catalog'', ''pg_toast'')
and pg_catalog.pg_table_is_visible(c.oid)
order by
n.nspname
,t.relname
,c.relname

Si desea profundizar más (como columnas y pedidos), debe buscar en pg_catalog.pg_index. Usar psql -E [dbname] es útil para averiguar cómo consultar el catálogo.