Problems creating a foreign key

Question

Problems creating a foreign key

Navigation

#1 by (0 votes)
#2 by (0 votes)

0

Personal I need to create a relationship between the attendance table and the category table, but every time I try it it gives the error 1215 can not add foreign key. What am I doing wrong? Both tables are empty.

CREATE TABLE 'atendimentos' (
'id' int(11) NOT NULL AUTO_INCREMENT,
'cliente_id' int(11) NOT NULL,
'categoria_id' int(11) NOT NULL DEFAULT '1',
'contato' varchar(100) DEFAULT NULL,
'created_at' timestamp NULL DEFAULT NULL,
'updated_at' timestamp NULL DEFAULT NULL,
PRIMARY KEY ('id'), ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

and this is a category table:

CREATE TABLE 'categorias' (
'id' int(11) unsigned NOT NULL AUTO_INCREMENT,
'parent_id' int(11) unsigned DEFAULT NULL,
'slug' varchar(100) COLLATE utf8_unicode_ci NOT NULL,
'label' varchar(100) COLLATE utf8_unicode_ci NOT NULL,
'order' int(11) NOT NULL DEFAULT '0',
'icon' varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
'created_at' timestamp NULL DEFAULT NULL,
'updated_at' timestamp NULL DEFAULT NULL,
PRIMARY KEY ('id'),
UNIQUE KEY 'categorias_slug_unique' ('slug'),
KEY 'categorias_parent_id_foreign' ('parent_id')
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

mysql

asked by anonymous 26.10.2018 / 19:38

2 answers

Retrieve values from an array of the next line in the previous row HTML does not recognize jQuery (imported or internal)

score 0 · Answer 1

Add the code to create the foreign key. Remember the order:

First the CREATE TABLE of the category table, and then calls.

CREATE TABLE 'atendimentos' (
'id' int(11) NOT NULL AUTO_INCREMENT,
'cliente_id' int(11) NOT NULL,
'categoria_id' int(11) NOT NULL DEFAULT '1',
'contato' varchar(100) DEFAULT NULL,
'created_at' timestamp NULL DEFAULT NULL,
'updated_at' timestamp NULL DEFAULT NULL,
PRIMARY KEY ('id'), 
CONSTRAINT FK_Atendimento_Categoria FOREIGN KEY (categoria_id)
REFERENCES categorias(id)
ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

If the tables are already created and you want to add them later:

ALTER TABLE atendimentos
ADD CONSTRAINT FK_Atendimento_Categoria
FOREIGN KEY (id) REFERENCES categorias(id);

Edit: As reported and done as above, see if the problem is not in default. First do this:

ALTER TABLE atendimentos
ALTER COLUMN id DROP DEFAULT;

 ALTER TABLE atendimentos
    ADD CONSTRAINT FK_Atendimento_Categoria
    FOREIGN KEY (id) REFERENCES categorias(id);

score 0 · Answer 2

0

I found the error. The person who created the Category table put the ID with unsigned it will not. I took the unsigned and all right. Many thanks to all.

26.10.2018 / 20:41