drop - foreign key mysql ejemplo

¿Puede una clave externa actuar como clave principal?

Question

Actualmente estoy diseñando una estructura de base de datos para el proyecto de nuestro equipo. Actualmente tengo esta misma pregunta en mente: ¿es posible tener una clave externa como clave principal en otra tabla?

Estas son algunas de las tablas del diseño de la base de datos de nuestro sistema:

user_accounts
students
guidance_counselors

Lo que quería que sucediera es que la tabla user_accounts debería contener los ID (supuestamente la credencial de inicio de sesión del sistema) y las contraseñas de los usuarios de los estudiantes y de los consejeros. En resumen, las claves principales de los students y la tabla guidance_counselors también son la clave externa de la tabla user_accounts . Pero no estoy seguro de si está permitido.

Otra pregunta es: también existe una tabla student_rec , que requiere un student_number (que es el user_id en la tabla user_accounts ) y un guidance_counsellor_id (que también es el user_id en user_accounts ) para cada uno de sus registros. Si los ID de un estudiante y un consejero user_accounts table provienen de la user_accounts table , ¿cómo diseñaría la tabla student_rec ? Y para referencia futura, ¿cómo lo escribo manualmente como código SQL?

Esto me ha estado molestando y no puedo encontrar ninguna respuesta específica o segura a mis preguntas.

Answer 1

Por supuesto. Esta es una técnica común conocida como tablas supertyping . Como en su ejemplo, la idea es que una tabla contiene un superconjunto de entidades y tiene atributos comunes que describen una entidad general, y otras tablas contienen subconjuntos de esas entidades con atributos específicos. No se diferencia de una simple jerarquía de clases en el diseño orientado a objetos.

Para su segunda pregunta, una tabla puede tener dos columnas que son claves foráneas separadas para la misma otra tabla. Cuando la base de datos crea la consulta, se une a esa otra tabla dos veces. Para ilustrar en una consulta SQL (no estoy seguro acerca de la sintaxis de MySQL, no la he usado en mucho tiempo, así que esta es la sintaxis de MS SQL específicamente), le daría a esa tabla dos alias distintos al seleccionar los datos. Algo como esto:

SELECT
    student_accounts.name AS student_name,
    counselor_accounts.name AS counselor_name
FROM
    student_rec
    INNER JOIN user_accounts AS student_accounts
      ON student_rec.student_number = student_accounts.user_id
    INNER JOIN user_accounts AS counselor_accounts
      ON student_rec.guidance_counselor_id = counselor_accounts.user_id

Esto esencialmente toma la tabla student_rec y la combina con la tabla user_accounts dos veces, una en cada columna, y asigna dos alias diferentes al combinarlos para diferenciarlos.

Answer 2

Sí, no debería haber ningún problema. Las claves externas y las claves principales son ortogonales entre sí, está bien que una columna o un conjunto de columnas sea la clave principal de esa tabla (que requiere que sean únicas) y también que se asocie con una clave principal / restricción única en otra mesa.