0

The table Friendship only uses REFERENCES without FOREIGN KEY keyword. Is it a mistake without coupling with Foreign Key keyword, or is it valid and represents another semantics? The Friendship table is supposed to hold IDs of any pair of two friends.

Create table Student (
id int NOT NULL AUTO_INCREMENT,
PRIMARY KEY (id)
);

Create table Book (
id int NOT NULL AUTO_INCREMENT,
student_id int NOT NULL,
PRIMARY KEY (id),
FOREIGN KEY (student_id)
  REFERENCES Student(id)
  ON DELETE CASCADE
);

Create table Friendship (
  id_from int NOT NULL REFERENCES Student(id),
  id_to int NOT NULL REFERENCES Student(id), 
  PRIMARY KEY (id_from, id_to) 
);

I created the 3 tables in MySql and inserted some data into them, and I am able to do searches like "find all friends of "John" correctly.

But I am not very sure whether REFERENCES can be used alone like this as a foreign key constraint.

Edited:

Follow the comment's suggestion, I modified the Friendship to the following:

Create table Friendship (
  id_from int NOT NULL,
  id_to int NOT NULL, 
  PRIMARY KEY (id_from, id_to)
  FOREIGN KEY (id_from) REFERENCES Student(id)
  FOREIGN KEY (id_to) REFERENCES Student(id)
);

But now I received a syntax error in creating this table:

Create table Friendship (
    ->   id_from int NOT NULL,
    ->   id_to int NOT NULL, 
    ->   PRIMARY KEY (id_from, id_to)
    ->   FOREIGN KEY (id_from) REFERENCES Student(id)
    ->   FOREIGN KEY (id_to) REFERENCES Student(id)
    -> );
ERROR 1064 (42000): 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 'FOREIGN KEY (id_from) REFERENCES Student(id)
  FOREIGN KEY (id_to) REFERENCES Student(' at line 5
user697911
  • 10,043
  • 25
  • 95
  • 169
  • Read before with question-answer http://stackoverflow.com/a/14230934/2253302 – alexander.polomodov Mar 04 '16 at 23:37
  • Does this mean that I have to add "FOREIGN KEY" to the Friendship table creation statement? If that is the case, why does the current code work well when I test them on MySql? – user697911 Mar 04 '16 at 23:42
  • Of course, you can search even without foreign keys or references. But there are some constraints, for example Update and Delete Anomalies – alexander.polomodov Mar 04 '16 at 23:45
  • If I add ""FOREIGN KEY" keyword to Friendship, then "id_from" and "id_to" would be both primary key and foreign key. Can a key be both at the same time? – user697911 Mar 05 '16 at 00:26
  • @alexander.polomodov, please see my updated version and the error message. – user697911 Mar 05 '16 at 00:46
  • You forgot commas between pk, and fk `Create table Friendship ( id_from int NOT NULL, id_to int NOT NULL, PRIMARY KEY (id_from, id_to), FOREIGN KEY (id_from) REFERENCES Student(id), FOREIGN KEY (id_to) REFERENCES Student(id) );` – alexander.polomodov Mar 05 '16 at 00:49
  • Let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/105412/discussion-between-user697911-and-alexander-polomodov). – user697911 Mar 05 '16 at 00:54

0 Answers0