Problems creating a foreign key

Asked

Viewed 66 times

0

Staff need to create a relationship between table attendance and table category, but every time I try it gives error 1215 cannot 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 table of categories:

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

  • Is João Voce trying to relate a FK to his own table? (KEY fk_cliente (cliente_id)))

  • @Claytontosatti no. I’m trying to relate care.categoria_id to categories.id.

  • already tried not to put DEFAULT "1" to categoria_id?

  • do you want to create the relationship already in create table? Or edit and add later?

  • The tables are already created so it has to be in the after. But the tables still have no contents.

2 answers

0

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 tables are already created and want to add later:

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

Edit: As you reported and did as above, make sure the problem is not in the 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);

  • That’s exactly what I’m doing, only it gives the error. SQL]ALTER TABLE attendances ADD CONSTRAINT Fk_attendance_category FOREIGN KEY(id) REFERENCES categories(id); [Err] 1215 - Cannot add Foreign key Cont straint

  • See my edit showing how to reomver the default.

0

I found the bug. The person who created the Category table put the ID with unsigned there won’t even. I took the unsigned and all right. Thank you all very much.

Browser other questions tagged mysql

You are not signed in. Login or sign up in order to post.