CODEWITHSUNDEEP
mysqlsqldatabase
Jimmy
Jimmy

Reputation: 12517

Linking tables in MYSQL - Do I need a foreign key

I have this schema, it's one of my first:

CREATE TABLE location (
id INT AUTO_INCREMENT PRIMARY KEY,
locality VARCHAR(20),
administrative_area_level_1 VARCHAR(20),
administrative_area_level_2 VARCHAR(20),
administrative_area_level_3 VARCHAR(20),
loc VARCHAR (17) NOT NULL,
rad VARCHAR (17),
updated TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
)

CREATE TABLE country {
id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(20),
iso VARCHAR(20),
loc VARCHAR (17) NOT NULL,
rad VARCHAR (17),
updated TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
}

First of all could you tell me how I can link my location table to a country table so that it is also required (i.e you can't have a location without a country).

Further to this, could anyone tell me why SQL fiddle might be giving me this error on my schema:

Schema Creation Failed: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'CREATE TABLE country' at line 12:

Upvotes: 1

Views: 203

Answers (4)

user2656474
user2656474

Reputation: 81

you should use '(' instead of '{' while creating the tables. now, how to join the two tables. there is no need of a foreign key to join the two tables at all. there is a field 'id' which is same in both of the tables and you can use this field to join the two tables. by using the following query:

SELECT l.administrative_area_level_1,c.name 
FROM country c,location l
WHERE l.id=c.id

the above query will show you the administrative_area_level_1 and name fields from location and country table respectively where the value of the id column will be same. but you should note that the id column in the location should represent the same record as that id field in the country. otherwise it will show you the wrong data. basically foreign key is needed to ensure that there will be no ambiguity in the table for example if there is an id column and corresponding to that id there is another column in other table. and if there exists an id in the second table and not in the first table there can be ambiguity there. so if we put id under the foreign key referencing the second table then if there is an id update in the first table then it would also be updated in the second table.so you can't create your own id in the second column. hope this help you in the foreign key concept.

Upvotes: 0

plain jane
plain jane

Reputation: 1007

Your code should be as follows:

 CREATE TABLE location (
   id INT AUTO_INCREMENT PRIMARY KEY,
   locality VARCHAR(20),
   administrative_area_level_1 VARCHAR(20),
   administrative_area_level_2 VARCHAR(20),
   administrative_area_level_3 VARCHAR(20),
   loc VARCHAR (17) NOT NULL,
   rad VARCHAR (17),
   updated TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
   );



  CREATE TABLE country (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(20),
    iso VARCHAR(20),
    loc VARCHAR (17) NOT NULL,
    rad VARCHAR (17),
    updated TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
  );

Remove the curly braces and add semicolon(;) after the statement

Upvotes: 0

Shemeer M Ali
Shemeer M Ali

Reputation: 1041

SQL fiddle issue can be solve with following query 

CREATE TABLE country (
id INT AUTO_INCREMENT PRIMARY KEY,
NAME VARCHAR(20),
iso VARCHAR(20),
loc VARCHAR (17) NOT NULL,
rad VARCHAR (17),
updated TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
)

Upvotes: 0

Alma Do
Alma Do

Reputation: 37365

Your second table creation is failed since you're using wrong brackets ({..} instead of (..))

As for foreign key - all explanation could be found in manual pages (here and here).

Upvotes: 1

Related Questions