mysql - SQL: Crear una tabla de relaciones con 2 auto_increment diferentes

relational-database auto-increment

Question

Tengo 2 tablas, ambas con sus propias ID de incremento automático, que son, por supuesto, las claves principales.

Cuando quiero crear una tercera tabla para establecer la relación entre estas 2 tablas, siempre tengo un error.

El primero es que solo puede tener 1 columna de incremento automático, el segundo ocurre cuando elimino la declaración auto_increment de esos 2, por lo tanto, sql no me permite convertirlos en claves foráneas, debido a la falla de coincidencia de tipos.

¿Hay alguna manera de que pueda crear una tabla relacional sin perder las características de incremento automático?

Otra posible solución (pero no preferida) puede ser que haya otra clave primaria en la primera tabla, que es el nombre de usuario del usuario, no con una declaración de incremento automático, por supuesto. ¿Es inevitable?

Gracias por adelantado.

Answer 1

Conceptos

Has entendido mal algunos conceptos básicos, y las dificultades resultan de eso. Primero debemos abordar los conceptos, no el problema tal como lo percibe, y en consecuencia, su problema desaparecerá.

ID de incremento automático, que son, por supuesto, las claves principales.

No, ellos no son. Ese es un error común. Y los problemas están garantizados.

Un campo de ID no puede ser una Clave principal en inglés o sentidos técnicos o relacionales.

• Claro, en SQL, puede declarar cualquier campo como una PRIMARY KEY , pero eso no lo transforma mágicamente en una Clave primaria en los sentidos inglés, técnico o relacional. Puedes nombrar a un chihuahua "Rottweiller", pero eso no lo transforma en un Rottweiller, sigue siendo un chihuahua. Al igual que cualquier lenguaje, SQL simplemente ejecuta los comandos que le da, no entiende que PRIMARY KEY significa algo Relacional, simplemente golpea un índice único en la columna (o campo).

• El problema es que, dado que ha declarado que la ID es una PRIMARY KEY , la considera una Clave primaria y puede esperar que tenga algunas de las cualidades de una Clave primaria. Excepto por la unicidad del valor de ID, no proporciona ningún beneficio. No tiene ninguna de las cualidades de una Clave primaria, ni ningún tipo de Clave relacional para el caso. No es una clave en los sentidos inglés, técnico o relacional. Al declarar que una no clave es una clave, solo se confundirá a sí mismo y descubrirá que hay algo terriblemente mal solo cuando el usuario se queja de duplicados en la tabla.

Las tablas relacionales deben tener unicidad de fila

Una PRIMARY KEY en un campo ID no proporciona unicidad de fila . Por lo tanto, no es una tabla relacional que contenga filas, y si no es eso, entonces es un archivo que contiene registros. No tiene nada de la integridad, ni el poder (en esta etapa, solo sabrá el poder de unión) o la velocidad que tiene una tabla en una base de datos relacional.

Ejecute este código (MS SQL 2008) y pruébelo usted mismo. No lea simplemente esto y lo entienda, y luego continúe leyendo el resto de esta Respuesta, este código debe ejecutarse antes de seguir leyendo . Tiene valor curativo.

    CREATE TABLE dumb_file (
        id         INT      NOT NULL  IDENTITY  PRIMARY KEY,
        name_first CHAR(30) NOT NULL,
        name_last  CHAR(30) NOT NULL
        )

    INSERT dumb_file VALUES ( "Mickey", "Mouse" )  -- succeeds
    INSERT dumb_file VALUES ( "Mickey", "Mouse" )  -- succeeds, but not intended
    INSERT dumb_file VALUES ( "Mickey", "Mouse" )  -- succeeds, but not intended

    SELECT * FROM dumb_file

Tenga en cuenta que tiene filas duplicadas. Las tablas relacionales son necesarias para tener filas únicas. Prueba adicional de que no tiene una tabla relacional, o ninguna de las cualidades de una.

Tenga en cuenta que en su informe, lo único que es único es el campo ID , que a ningún usuario le importa, que ningún usuario ve, porque no son datos, es una tontería adicional que un "maestro" muy estúpido le dijo que pusiera cada archivo Tiene unicidad de registro pero no unicidad de fila .

En términos de los datos (los datos reales menos las adiciones extrañas), los datos name_last y name_first pueden existir sin el campo ID . Una persona tiene un nombre y apellido sin una identificación estampada en la frente.

La segunda cosa que estás usando que te confunde es el AUTOINCREMENT. Si está implementando un sistema de archivo de registros sin capacidad Relacional, seguro, es útil, no tiene que codificar el incremento al insertar registros. Pero si está implementando una base de datos relacional, no sirve para nada, porque nunca la usará. Hay muchas características en SQL que la mayoría de la gente nunca usa.

Acción correctiva

Entonces, ¿cómo se actualiza, eleva, ese archivo_dumb que está lleno de filas duplicadas a una tabla relacional, para obtener algunas de las cualidades y beneficios de una tabla relacional? Hay tres pasos para esto.

1. Necesitas entender Keys

   • Y dado que hemos progresado desde los archivos ISAM de la década de 1970 hasta el Modelo relacional , debe comprender las claves relacionales . Es decir, si desea obtener los beneficios (integridad, potencia, velocidad) de una base de datos relacional.

   El Dr. EF Cood, en su RM , declaró que:

   una clave se compone de los datos

   y

   las filas en una tabla deben ser únicas

   Su "clave" no se compone de los datos. Es un parásito adicional que no es de datos, causado por su infección con la enfermedad de su "maestro". Reconózcalo como tal y permítase la plena capacidad mental que Dios le dio (tenga en cuenta que no le pido que piense en términos aislados, fragmentados o abstractos, todos los elementos en una base de datos deben estar integrados entre sí). Cree una clave real a partir de los datos, y solo a partir de los datos. En este caso, solo hay una clave posible: (name_last, name_first).

2. Pruebe este código , declare una restricción única en los datos:

        CREATE TABLE dumb_table (
           id         INT      NOT NULL  IDENTITY  PRIMARY KEY,
           name_first CHAR(30) NOT NULL,
           name_last  CHAR(30) NOT NULL
   
           CONSTRAINT UK 
               UNIQUE ( name_last, name_first )
           )
   
       INSERT dumb_table VALUES ( "Mickey", "Mouse" )  -- succeeds
       INSERT dumb_table VALUES ( "Mickey", "Mouse" )  -- fails, as intended
       INSERT dumb_table VALUES ( "Minnie", "Mouse" )  -- succeeds
   
       SELECT * FROM dumb_table

   Ahora tenemos la singularidad de la fila . Esa es la secuencia que le sucede a la mayoría de las personas: crean un archivo que permite engaños; no tienen idea de por qué aparecen los engañados en los menús desplegables; el usuario grita; ajustan el archivo y agregan un índice para evitar engaños; van a la próxima corrección de errores. (Pueden hacerlo correctamente o no, esa es una historia diferente).

3. El segundo nivel. Para personas pensantes que piensan más allá de los arreglos. Dado que ahora tenemos la singularidad de la fila, ¿cuál es el propósito del campo ID en nombre del cielo, por qué lo tenemos? Oh, porque el chihuahua se llama Rotty y tenemos miedo de tocarlo.

   La declaración de que es una PRIMARY KEY es falsa, pero permanece, causando confusión y falsas expectativas. La única clave genuina que existe es (name_last, name_fist), y es una clave alternativa en este momento.

   Por lo tanto, el campo ID es totalmente superfluo; y también lo es el índice que lo soporta; y también lo es el estúpido AUTOINCREMENT ; y también lo es la declaración falsa de que es una PRIMARY KEY ; y cualquier expectativa que pueda tener al respecto es falsa.

   Por lo tanto, elimine el campo de ID superfluo. Prueba este código :

       CREATE TABLE honest_table (
           name_first CHAR(30) NOT NULL,
           name_last  CHAR(30) NOT NULL
   
           CONSTRAINT PK 
           PRIMARY KEY ( name_last, name_first )
           )
   
       INSERT honest_table VALUES ( "Mickey", "Mouse" )  -- succeeds
       INSERT honest_table VALUES ( "Mickey", "Mouse" )  -- fails, as intended
       INSERT honest_table VALUES ( "Minnie", "Mouse" )  -- succeeds
   
       SELECT * FROM honest_table

   Funciona bien, funciona según lo previsto, sin los campos e índices extraños.

   Por favor recuerde esto, y hágalo bien, cada vez.

Falsos maestros

En estos tiempos finales, como se aconseja, tendremos muchos de ellos. Tenga en cuenta bien, los "maestros" que propagan las columnas de ID , en virtud de la evidencia detallada en esta publicación, simplemente no entienden el Modelo Relacional o las Bases de Datos Relacionales. Especialmente aquellos que escriben libros al respecto.

Como se evidencia, están atrapados en la tecnología ISAM anterior a 1970. Eso es todo lo que entienden, y eso es todo lo que pueden enseñar. Utilizan un contenedor de base de datos SQL, para facilitar el acceso, la recuperación, la copia de seguridad, etc., pero el contenido es puro Sistema de archivo de registros sin integridad relacional, potencia o velocidad. AFAIC, es un fraude grave.

Además de los campos de ID , por supuesto, hay varios elementos que son conceptos clave relacionados o no, que en conjunto, hacen que llegue a una conclusión tan grave. Esos otros artículos están más allá del alcance de esta publicación.

Un par particular de idiotas está montando un asalto en la Primera Forma Normal. Pertenecen al manicomio.

Responder

Ahora para el resto de tu pregunta.

¿Hay alguna manera de que pueda crear una tabla relacional sin perder las características de incremento automático?

Esa es una oración contradictoria. Confío en que entenderá por mi explicación, las tablas relacionales no tienen necesidad de "características" de AUTOINCREMENT ; si el archivo tiene AUTOINCREMENT , no es una tabla relacional.

AUTOINCREMENT es bueno solo para una cosa: si, y solo si, desea crear una hoja de cálculo Excel en el contenedor de la base de datos SQL, repleta de campos llamados A, B, y C, en la parte superior, y registrar números en el lado izquierdo. En términos de base de datos, ese es el resultado de un SELECT, una vista plana de los datos, que no es la fuente de datos, que está organizada (Normalizada).

Otra posible solución (pero no preferida) puede ser que haya otra clave primaria en la primera tabla, que es el nombre de usuario del usuario, no con una declaración de incremento automático, por supuesto. ¿Es inevitable?

En el trabajo técnico, no nos interesan las preferencias, porque eso es subjetivo y cambia todo el tiempo. Nos preocupamos por la corrección técnica, porque eso es objetivo y no cambia.

Si, es inevitable. Porque es solo cuestión de tiempo; cantidad de errores; número de "no se puede hacer"; cantidad de gritos de los usuarios, hasta que enfrente los hechos, supere sus declaraciones falsas y se dé cuenta de que:

• la única forma de garantizar que las filas de usuarios sean únicas, que los nombres de usuario sean únicos, es declarando una restricción UNIQUE

• y deshacerse de user_id o id en el archivo de usuario

• que promueve user_name a PRIMARY KEY

Sí, porque todo su problema con la tercera tabla, no por casualidad, se elimina.

Esa tercera tabla es una tabla asociativa . La única clave requerida (clave primaria) es una combinación de las dos claves primarias principales. Eso garantiza la unicidad de las filas , que se identifican por sus claves, no por sus IDs.

Le advierto sobre eso porque los mismos "maestros" que le enseñaron el error de implementar campos ID , enseñan el error de implementar campos ID en la Tabla asociativa, donde, al igual que con una tabla ordinaria, es superfluo, no sirve para nada , introduce duplicados y causa confusión. Y es doblemente superfluo porque las dos claves que proporcionan ya están allí, mirándonos a la cara.

Como no entienden el RM o los términos relacionales, llaman a las tablas asociativas tablas de "enlace" o "mapa". Si tienen un campo de ID , de hecho son archivos.

Tablas de búsqueda

ID campos de ID son particularmente estúpidos para las tablas de búsqueda o referencia. La mayoría de ellos tienen códigos reconocibles, no hay necesidad de enumerar la lista de códigos en ellos, porque los códigos son (deberían ser) únicos.

Además, tener los códigos en las tablas secundarias como FK es una buena cosa: el código es mucho más significativo y, a menudo, guarda una unión innecesaria:

    SELECT ...
        FROM child_table           -- not the lookup table
        WHERE gender_code = "M"    -- FK in the child, PK in the lookup

en vez de:

    SELECT ...
        FROM child_table
        WHERE gender_id = 6        -- meaningless to the maintainer

o peor:

    SELECT ...
        FROM child_table C         -- that you are trying to determine
        JOIN lookup_table L
            ON C.gender_id = L.gender_id
        WHERE L.gender_code = "M"  -- meaningful, known

Tenga en cuenta que esto es algo que uno no puede evitar: necesita unicidad en el código de búsqueda y unicidad en la descripción. Ese es el único método para evitar duplicados en cada una de las dos columnas:

    CREATE TABLE gender (
        gender_code  CHAR(2)  NOT NULL,
        name         CHAR(30) NOT NULL

        CONSTRAINT PK 
            PRIMARY KEY ( gender_code )

        CONSTRAINT AK 
            UNIQUE ( name )
        )

Ejemplo completo

De los detalles en su pregunta, sospecho que tiene problemas de sintaxis SQL y definición de FK, por lo que le daré la solución completa que necesita como ejemplo (ya que no ha proporcionado definiciones de archivo):

    CREATE TABLE user (                 -- Typical Identifying Table
        user_name  CHAR(16) NOT NULL,   -- Short PK
        name_first CHAR(30) NOT NULL,   -- Alt Key.1
        name_last  CHAR(30) NOT NULL,   -- Alt Key.2
        birth_date DATE     NOT NULL    -- Alt Key.3

        CONSTRAINT PK                   -- unique user_name
            PRIMARY KEY ( user_name )

        CONSTRAINT AK                   -- unique person identification
            PRIMARY KEY ( name_last, name_first, birth_date )
        )

    CREATE TABLE sport (                  -- Typical Lookup Table
        sport_code  CHAR(4)  NOT NULL,    -- PK Short code
        name        CHAR(30) NOT NULL     -- AK

        CONSTRAINT PK 
            PRIMARY KEY ( sport_code )

        CONSTRAINT AK 
            PRIMARY KEY ( name )
        )

    CREATE TABLE user_sport (           -- Typical Associative Table
        user_name  CHAR(16) NOT NULL,   -- PK.1, FK
        sport_code CHAR(4)  NOT NULL,   -- PK.2, FK
        start_date DATE     NOT NULL

        CONSTRAINT PK 
            PRIMARY KEY ( user_name, sport_code )

        CONSTRAINT user_plays_sport_fk
            FOREIGN KEY     ( user_name )
            REFERENCES user ( user_name )

        CONSTRAINT sport_occupies_user_fk
            FOREIGN KEY      ( sport_code )
            REFERENCES sport ( sport_code )
        )

Allí, la declaración de PRIMARY KEY es honesta, es una clave primaria; sin ID; sin AUTOINCREMENT; sin índices adicionales; sin filas duplicadas; sin expectativas erróneas; sin problemas consecuentes.

Modelo de datos

Aquí está el modelo de datos para ir con las definiciones.

• Ejemplo de modelo de datos deportivos del usuario

• Si no está acostumbrado a la notación, tenga en cuenta que cada pequeño tic, muesca y marca, las líneas continuas vs discontinuas, las esquinas cuadradas vs redondeadas, significa algo muy específico. Consulte la notación IDEF1X .

• Una imagen vale mas que mil palabras; en este caso, una imagen de queja estándar vale más que eso; uno malo no vale el papel en el que está dibujado.

• Por favor revise las frases verbales cuidadosamente, comprenden un conjunto de predicados. El resto de los predicados se puede determinar directamente desde el modelo. Si esto no está claro, por favor pregunte.