Pues como yo lo veo, no necesitas tener los campos "login_alumno" y "clave_alumno" en la tabla alumno, solamente un campo que apunte a "id_usuario" de la tabla usuarios y otro campo que apunte a id_materia en la tabla "materia".
El error lo causa el hecho de que en las tablas "usuarios" y "materia" todas las claves son de tipo "PRIMARY" cuando solamente el id debe serlo, el resto de los campos que quieres que sean indice deben ser de tipo "INDEX".
Aunque más general veo problemas con el diseño general de todo esto. Por ejemplo, la tabla "alumno" parece ser una continuación de la tabla "usuarios" así que no necesitas esa tabla, podrías poner todos los campos de "alumnos" en la misma tabla de "usuarios". Excepto el campo "materia_alumno". Ese campo sugiere que es un grupo repetitivo. O sea, hay una relación de uno a muchos entre alumnos y materias (un alumno->muchas materias).
Para mi que lo que has querido hacer es lo siguiente:
Código MySQL :
CREATE TABLE IF NOT EXISTS `usuarios` (
`id_usuario` int(11) NOT NULL,
`login` varchar(20) DEFAULT NULL,
`clave` varchar(16) DEFAULT NULL,
`nivel` varchar(20) latin1 DEFAULT NULL,
`nombre` varchar(20) DEFAULT NULL,
`apellidos` varchar(40) DEFAULT NULL,
`direccion` varchar(30) DEFAULT NULL,
`telefono` varchar(20) DEFAULT NULL,
`email` varchar(40) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
-- Indice de la tabla `usuarios`
ALTER TABLE `usuarios`
ADD PRIMARY KEY (`id_usuario`), ADD KEY `login` (`login`);
-- AUTO_INCREMENT de la tabla `usuarios`
ALTER TABLE `usuarios`
MODIFY `id_usuario` int(11) NOT NULL AUTO_INCREMENT;
- -----------------------------------------
CREATE TABLE IF NOT EXISTS `materias` (
`id_materia` int(11) NOT NULL,
`nombre` varchar(20) DEFAULT NULL,
`duracion` date DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
-- Indices de la tabla `materias`
ALTER TABLE `materias`
ADD PRIMARY KEY (`id_materia`);
-- AUTO_INCREMENT de la tabla `materias`
ALTER TABLE `materias`
MODIFY `id_materia` int(11) NOT NULL AUTO_INCREMENT;
-- ---------------------------------
CREATE TABLE IF NOT EXISTS `materias_por_alumno` (
`id_usuario` int(11) NOT NULL,
`id_materia` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
-- Indices de la tabla `materias_por_alumno`
ALTER TABLE `materias_por_alumno`
ADD KEY `id_usuario` (`id_usuario`), ADD KEY `id_materia` (`id_materia`);
-- Filtros para la tabla `materias_por_alumno`
ALTER TABLE `materias_por_alumno`
ALTER TABLE `materias_por_alumno`
ADD CONSTRAINT `materias_por_alumno_ibfk_1` FOREIGN KEY (`id_usuario`) REFERENCES `usuarios` (`id_usuario`) ON DELETE CASCADE ON UPDATE CASCADE,
ADD CONSTRAINT `materias_por_alumno_ibfk_2` FOREIGN KEY (`id_materia`) REFERENCES `materias` (`id_materia`) ON DELETE CASCADE ON UPDATE CASCADE;
Los CONSTRAIT en la tabla "materias_por_alumno" hacen que cuando se borre una materia, se borren las relaciones entre esa materia y los alumnos (lo mismo si se borra un usuario).
Acá está el volcado de las tablas para que pruebes:
Código MySQL :
INSERT INTO `materias` (`id_materia`, `nombre`, `duracion`) VALUES
(1, 'eurgheiurghieruhg', '2015-09-30'),
(2, 'iuthiruthgiuhg', '2015-10-25'),
(3, 'eirughieur dbveuyrg', '2015-11-01');
INSERT INTO `materias_por_alumno` (`id_usuario`, `id_materia`) VALUES
(1, 1),
(1, 2),
(2, 2),
(2, 3);
INSERT INTO `usuarios` (`id_usuario`, `login`, `clave`, `nivel`, `nombre`, `apellidos`, `direccion`, `telefono`, `email`) VALUES
(1, 'lkjfwioowie', 'iquiquhiuq', 'iwuefiwueh', 'Juan', 'Perez', 'woiefwie owiefow efo987', '298329834782', '[email protected]'),
(2, 'eiurhieurhgiuer', 'wuyegfuwyegf', 'sjkeliuweiuwe', 'Maria', 'Lopez', 'wiueiuefiwue wiuehfiuwhefu', '82762763', '[email protected]');
Puedes hacer lo siguiente. Agrega una materia nueva. Asígnale esa nueva materia al alumino 2 (id_usuario=2) y luego borra la materia de la tabla materias. Verás que la relación desaparece de la tabla "materias_por_usuario" también.
Para saber cuáles materias tiene un alumno basta con leer la tabla "materias_por_alumno" y relacionarla con las otras tablas:
Código MySQL :
SELECT * FROM `materias_por_alumno`, `usuarios`, `materias`
WHERE `materias_por_alumno`.`id_usuario` = 1
AND `materias_por_alumno`.`id_materia` = `materias`.`id_materia`
AND `materias_por_alumno`.`id_usuario` = `usuarios`.`id_usuario`
.....espero que me puedan ayudar he andado buscando informacion pero nada, sin mas les comento:
, de verdad se agradece, mira te explico:
