SQL Syntax Error in FK Creation

Asked

Viewed 1,587 times

0

I’m looking to add a foreign key on the table CONSULTATION Only Mysql reports the following error:

1064 - You have an error in your SQL syntax; check the manual that Corresponds to your Mariadb server version for the right syntax to use near 'REFERENCES medico(crm) ENGINE=Innodb DEFAULT CHARSET=latin1' at line 9

The SQL code is as follows::

CREATE TABLE `medico` (
  `crm` float(12) NOT NULL,
  `cpf` varchar(15) DEFAULT NULL,
  `dataInscricao` varchar(20) DEFAULT NULL,
  `bairro` varchar(50) DEFAULT NULL,
  `cidade` varchar(50) DEFAULT NULL,
  `logradouro` varchar(90) DEFAULT NULL,
  `uf` varchar(2) DEFAULT NULL,
  `nome` varchar(50) DEFAULT NULL,
  `nomeUser` varchar(12) DEFAULT NULL,
  `senhaUser` varchar(12) DEFAULT NULL,
  `cnpj` varchar(15) NOT NULL,
  PRIMARY KEY (`crm`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

CREATE TABLE `consulta` (
  `codConsulta` int(11) NOT NULL,
  `data` varchar(10) DEFAULT NULL,
  `horario` varchar(10) DEFAULT NULL,
  `crm` float(12) NOT NULL,
  `cpf` varchar(15) NOT NULL,
  `codTipoCons` int(11) NOT NULL,
  PRIMARY KEY (`codConsulta`),
  FOREIGN KEY `crm` REFERENCES `medico`(`crm`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

What’s The Problem?

mysql sql

  • Was any of the answer helpful? Don’t forget to choose one and mark it so it can be used if someone has a similar question!

2 answers

2


Friend, try to create the table and then change the table by setting the desired FK.

Ex:

    CREATE TABLE `cidades` ( 
    `codcidade` INT NOT NULL ,
    `descricao` VARCHAR( 50 ) NOT NULL) 
    ENGINE = innodb;

    ALTER TABLE `clientes` ADD CONSTRAINT `fk_cidade` FOREIGN KEY ( `codcidade` ) REFERENCES `cidade` ( `codcidade` ) ;

Ref.: http://www.devmedia.com.br/criando-uma-chave-estrangeira-no-mysql/20299

  • While this link may answer the question, it is best to include the essential parts of the answer here and provide the link for reference. Replies per link only can be invalidated if the page with the link is changed

0

We’re missing a () in the creation of the foreign key. It would look like this:

FOREIGN KEY(`crm`) REFERENCES `medico`(`crm`)

Browser other questions tagged mysql sql

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