tablas - modificar clave primaria sql server

Cambiar el nombre de las tablas de forma segura utilizando columnas de clave primaria en serie

Question

Sé que las tablas de PostgreSQL que usan una clave primaria SERIAL terminan con un índice implícito, una secuencia y una restricción creadas por PostgreSQL. La pregunta es cómo cambiar el nombre de estos objetos implícitos cuando se cambia el nombre de la tabla. A continuación se muestra mi intento de resolver esto con preguntas específicas al final.

Dada una tabla como:

CREATE TABLE foo (
    pkey SERIAL PRIMARY KEY,
    value INTEGER
);

Postgres salidas:

AVISO: CREAR TABLA creará la secuencia implícita "foo_pkey_seq" para la columna serial "foo.pkey"
AVISO: CREATE TABLE / PRIMARY KEY creará el índice implícito "foo_pkey" para la tabla "foo"
Consulta devuelta exitosamente sin resultado en 52 ms.

El panel SQL de pgAdmin III muestra la siguiente secuencia de comandos DDL para la tabla (ordenada):

CREATE TABLE foo (
  pkey serial NOT NULL,
  value integer,
  CONSTRAINT foo_pkey PRIMARY KEY (pkey )
);
ALTER TABLE foo OWNER TO postgres;

Ahora renombra la tabla:

ALTER table foo RENAME TO bar;

Consulta devuelta exitosamente sin resultado en 17 ms.

pgAdmin III:

CREATE TABLE bar (
  pkey integer NOT NULL DEFAULT nextval(''foo_pkey_seq''::regclass),
  value integer,
  CONSTRAINT foo_pkey PRIMARY KEY (pkey )
);
ALTER TABLE bar OWNER TO postgres;

Tenga en cuenta el siguiente valor DEFAULT nextval(''foo_pkey_seq''::regclass), esto significa que cambiar el nombre de la tabla no cambia el nombre de la secuencia de las claves principales, pero ahora tenemos este nextval() explícito nextval() .

Ahora renombra la secuencia:

Quiero mantener el nombre de la base de datos coherente, así que probé:

ALTER SEQUENCE foo_pkey_seq RENAME TO bar_pkey_seq;

Consulta devuelta exitosamente sin resultado en 17 ms.

pgAdmin III:

CREATE TABLE bar (
  pkey serial NOT NULL,
  value integer,
  CONSTRAINT foo_pkey PRIMARY KEY (pkey )
);
ALTER TABLE bar OWNER TO postgres;

El valor DEFAULT nextval(''foo_pkey_seq''::regclass), se ha ido.

Preguntas

1. ¿Por qué apareció y desapareció la declaración DEFAULT nextval(''foo_pkey_seq''::regclass) ?
2. ¿Hay alguna forma de cambiar el nombre de la tabla y cambiar el nombre de la secuencia de la clave primaria al mismo tiempo?
3. ¿Es seguro cambiar el nombre de la tabla y luego la secuencia mientras los clientes están conectados a la base de datos, hay algún problema de concurrencia?
4. ¿Cómo sabe postgres qué secuencia usar? ¿Hay un disparador de base de datos que se utiliza internamente? ¿Hay algo más para cambiar el nombre que no sea la tabla y la secuencia?
5. ¿Qué pasa con el índice implícito creado por una clave primaria? ¿Debería eso ser renombrado? Si es así, ¿cómo se puede hacer eso?
6. ¿Qué pasa con el nombre de restricción anterior? Todavía es foo_pkey . ¿Cómo se cambia el nombre de una restricción?

Answer 1

serial no es un tipo de datos real. El manual establece :

Los tipos de datos smallserial , serial y bigserial no son tipos verdaderos, sino simplemente una conveniencia de notación para crear columnas de identificadores únicos

El tipo de pseudodatos se resuelve haciendo todo esto:

• crear una secuencia llamada tablename_colname_seq

• cree la columna con el tipo integer (o int2 / int8 respectivamente para smallserial / bigserial )

• haga la columna NOT NULL DEFAULT nextval(''tablename_colname_seq'')

• haga que la columna sea propietaria de la secuencia, para que se descargue con ella automáticamente

El sistema no sabe si hizo todo esto a mano o mediante el tipo de serial . pgAdmin comprueba las funciones enumeradas y, si se cumplen todas, el script DDL de ingeniería inversa se simplifica con el tipo de serial correspondiente. Si una de las características no se cumple, esta simplificación no tiene lugar. Eso es algo que hace pgAdmin. Para las tablas de catálogo subyacentes es todo lo mismo. No hay ningún tipo de serial como tal.

No hay forma de renombrar automáticamente las secuencias propias. Tu puedes correr:

ALTER SEQUENCE ... RENAME TO ...

como hiciste El sistema en sí no se preocupa por el nombre . La columna DEFAULT almacena un OID ( ''foo_pkey_seq''::regclass ), puede cambiar el nombre de la secuencia sin romper eso: el OID se mantiene igual. Lo mismo ocurre con las claves externas y referencias similares dentro de la base de datos.

El índice implícito para la clave principal está vinculado al nombre de la restricción PK, que no cambiará si cambia el nombre de la tabla. En Postgres 9.2 o posterior puedes usar

ALTER TABLE ... RENAME CONSTRAINT ..

para rectificar eso, también.

También puede haber índices nombrados en referencia al nombre de la tabla. Procedimiento similar :

ALTER INDEX .. RENAME TO  ..

Puedes tener todo tipo de referencias informales al nombre de la tabla. El sistema no puede renombrar a la fuerza los objetos que se pueden nombrar como desee. Y no le importa.

Por supuesto, no desea invalidar el código SQL que hace referencia a esos nombres. Obviamente, no desea cambiar los nombres mientras la lógica de la aplicación los hace referencia. Normalmente, esto no sería un problema para los nombres de índices, secuencias o restricciones, ya que normalmente no se hace referencia a ellos por su nombre.

Postgres también adquiere un bloqueo en los objetos antes de cambiarles el nombre. Entonces, si hay transacciones abiertas concurrentes que tengan algún tipo de bloqueo en los objetos en cuestión, su operación RENAME se detiene hasta que esas transacciones se confirmen o restituyan.

Catálogos de sistemas y OIDs.

El esquema de la base de datos se almacena en las tablas del catálogo del sistema en el esquema del sistema pg_catalog . Todos los detalles en el manual aquí. Si no sabe exactamente lo que está haciendo, no debería estar jugando con esas tablas en absoluto . Un movimiento en falso y puedes romper tu base de datos. Utilice los comandos DDL que proporciona Postgres.

Para algunas de las tablas más importantes, Postgres proporciona OID y tipos de conversión para obtener el nombre del OID y viceversa rápidamente. Me gusta:

SELECT ''foo_pkey_seq''::regclass

Si el nombre del esquema está en la search_path y el nombre de la tabla es único, eso le da lo mismo que:

SELECT oid FROM pg_class WHERE relname = ''foo_pkey_seq'';

La clave principal de la mayoría de las tablas de catálogo es OID e internamente, la mayoría de las referencias utilizan OID.