CODEWITHSUNDEEP
mysqlsqlmysql-workbench
Angular noob
Angular noob

Reputation: 437

Foreign key constraint has no effect when described inline

I have a simple schema: 

CREATE TABLE technologies (
  technologyName VARCHAR(50) NOT NULL PRIMARY KEY
);

CREATE TABLE technologySynonyms (
  synonymId              INT PRIMARY KEY AUTO_INCREMENT,
  sourceTechnologyName VARCHAR(50) UNIQUE,
  targetTechnologyName VARCHAR(50),
  FOREIGN KEY (targetTechnologyName) 
    REFERENCES technologies(technologyName)
);

I thought to simplify the technologySynonyms table definition further like so: 

CREATE TABLE technologySynonyms (
  synonymId              INT PRIMARY KEY AUTO_INCREMENT,
  sourceTechnologyName VARCHAR(50) UNIQUE,
  targetTechnologyName VARCHAR(50) REFERENCES technologies(technologyName)
);

I get no errors - the query executes fine and the table is created, but when I insert a record that validates the foreign key constraint: 

INSSERT INTO technologySynonyms (sourceTechnologyName, targetTechnologyName)
  VALUES ('JS', 'Value not present in technologies table!!');

the record is inserted.

This leaves me with a couple of questions:

  1. What is wrong with my SQL? Should this not work?
  2. Can I make MySQL Workbench more strict? I would have preferred it if the query was rejected immediately.

Upvotes: 0

Views: 78

Answers (1)

Mihai
Mihai

Reputation: 26804

This is standard behavior,read the Important section

Furthermore, MySQL parses but ignores “inline REFERENCES specifications” (as defined in the SQL standard) where the references are defined as part of the column specification. MySQL accepts REFERENCES clauses only when specified as part of a separate FOREIGN KEY specification

Upvotes: 2

Related Questions