MySQL - Clave foranea en la misma tabla

   
Vista:

Clave foranea en la misma tabla

Publicado por Mendak47 (4 intervenciones) el 30/03/2017 22:10:28
Me gustaria saber si es posible hacer clave foranea de una clave de la misma tabla, os dejo el codigo:
1
2
3
4
5
6
7
8
9
10
11
create table depart (dept_no int,
	primary key(dept_no));
create table emple (emp_no int,
	dir int,
	dept_no int,
	foreign key (dept_no) references depart (dept_no)
	on delete cascade on update cascade,
	primary key(emp_no));
 
alter table emple add key (dir);
alter table emple add foreign key (dir) references emple (dept_no);


Pero me da el siguiente error:

1
ERROR 1025 (HY000): Error on rename of './empleados/#sql-321_4' to './empleados/emple' (errno: 150 - Foreign key constraint is incorrectly formed)
Valora esta pregunta
Me gusta: Está pregunta es útil y esta claraNo me gusta: Está pregunta no esta clara o no es útil
0
Responder

Clave foranea en la misma tabla

Publicado por SuperIndio (71 intervenciones) el 31/03/2017 00:08:05
Estas seguro que es alli el error?... a mi no me dio ese error

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 85
Server version: 10.0.24-MariaDB mariadb.org binary distribution
 
Copyright (c) 2000, 2016, Oracle, MariaDB Corporation Ab and others.
 
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
 
MariaDB [dblaweb]> create table depart (dept_no int,
    ->  primary key(dept_no));
Query OK, 0 rows affected (0.24 sec)
 
MariaDB [dblaweb]> create table emple (emp_no int,
    ->  dir int,
    ->  dept_no int,
    ->  foreign key (dept_no) references depart (dept_no)
    ->  on delete cascade on update cascade,
    ->  primary key(emp_no));
Query OK, 0 rows affected (0.27 sec)
 
MariaDB [dblaweb]> alter table emple add key (dir);
Query OK, 0 rows affected (0.16 sec)
Records: 0  Duplicates: 0  Warnings: 0
 
MariaDB [dblaweb]> alter table emple add foreign key (dir) references emple (dept_no);
Query OK, 0 rows affected (0.73 sec)
Records: 0  Duplicates: 0  Warnings: 0
 
MariaDB [dblaweb]>

eso si si te fijas no me dio info sobre los campos afectados
Valora esta respuesta
Me gusta: Está respuesta es útil y esta claraNo me gusta: Está respuesta no esta clara o no es útil
0
Comentar

Clave foranea en la misma tabla

Publicado por Mendak47 (4 intervenciones) el 01/04/2017 21:09:49
Prueba con la tabla completa, a ver si te sigue dejando porfavor.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
create database empleados character set utf8 collate utf8_general_ci;
use empleados;
create table depart (dept_no int,
	nombre varchar(50),
	loc varchar(50),
	primary key(dept_no));
create table emple (emp_no int,
	apellido varchar(50),
	oficio varchar(50),
	dir int,
	fecha_alt date,
	salario dec(10,0),
	comision dec(10,0),
	dept_no int,
	foreign key (dept_no) references depart (dept_no)
	on delete cascade on update cascade,
	primary key(emp_no));
 
insert into depart value ("10","CONTABILIDAD","SERVILLA"),
	("20","INVESTIGACION","MADRID"),
	("30","VENTAS","BARCELONA"),
	("40","PRODUCCION","BILBAO");
insert into emple value ("7369","SANCHEZ","EMPLEADO","7902","1980/12/17","10400",NULL,"20"),
	("7499","ARROYO","EMPLEADO","7698","1980/02/20","2080000","39000","30"),
	("7521","SALA","VENDEDOR","7698","1981/02/22","162500","162500","30"),
	("7566","JIMENEZ","DIRECTOR","7839","1981/04/02","386750",NULL,"20"),
	("7654","MARTIN","VENDEDOR","7698","1981/09/29","182000",NULL,"30"),
	("7698","NEGRO","DIRECTOR","7839","1981/05/01","370500",NULL,"20"),
	("7788","GIL","ANALISTA","7566","1981/11/09","3900000",NULL,"20"),
	("7839","REY","PRESIDENTE",NULL,"1981/11/17","6500000",NULL,"10"),
	("7844","TOVAR","VENDEDOR","7698","1981/09/08","1950000","0","30"),
	("7876","ALONSO","EMPLEADO","7788","1981/09/23","143000",NULL,"20"),
	("7900","JIMENO","EMPLEADO","7698","1981/12/03","12350000",NULL,"30"),
	("7902","FERNANDEZ","ANALISTA","7566","1981/12/03","390000",NULL,"20"),
	("7934","MUÑOZ","EMPLEADO","7782","1982/01/23","169000",NULL,"10");
 
alter table emple add key (dir);
alter table emple add foreign key (dir) references emple (emp_no) on delete cascade on update cascade;
alter table depart add tlf int;

Gracias
Valora esta respuesta
Me gusta: Está respuesta es útil y esta claraNo me gusta: Está respuesta no esta clara o no es útil
0
Comentar