CODEWITHSUNDEEP
mysqlsqldatabasedatabase-designforeign-keys
Sharia Hussain
Sharia Hussain

Reputation: 45

SQL - Foreign Key and Primary Key not Matching

I am new to SQL and I am running into trouble.

I have 3 tables:

CREATE TABLE indexCodes 
{
   (indexNum VARCHAR(5) PRIMARY KEY, 
   courseString VARCHAR(10), 
   title VARCHAR(20)
}

CREATE TABLE user
{
     (id  INT NOT NULL PRIMARY KEY AUTO_INCREMENT, 
     email VARCHAR(255) NOT NULL, 
     password VARCHAR(255) NOT NULL)
}

    CREATE TABLE snipes
{ 
      (snipeNumber  INT NOT NULL PRIMARY KEY AUTO_INCREMENT), 
       FOREIGN KEY indexNum REFERENCES indexcodes(indexNum),
       FOREIGN KEY userID REFERENCES user(id)
}

and inserting into snipes with

INSERT INTO snipes(indexNum, userID) VALUES ("06666", 1);
INSERT INTO snipes(indexNum, userID) VALUES ("06675", 1);

When I run

SELECT * FROM snipes, user, indexcodes where id=1

two indexNum columns appear with two different values, I am assuming that the snipes indexNum column shows whatever was inserted but the indexCodes indexNum displays a different result (I believe it is the first two entries in indexCodes)

Upvotes: 0

Views: 341

Answers (1)

G. Peres
G. Peres

Reputation: 31

When you put all the tables in the FROM clause divided only by comma without any restrictions of joining the tables with a WHERE clause you get a cartesian product of all the tables referenced in the FROM clause, for example, or using JOIN syntax instead of putting all the tables int the FROM clause. Here is two examples that would work on your scenario:

  • WHERE clause
SELECT * FROM snipes s, t_user u, indexcodes i where 
s.indexNum = i.indexnum
and s.userid = u.id
and id=1;
  • JOIN syntax
SELECT * FROM snipes s
inner join t_user u on s.userid = u.id
inner join indexcodes i on s.indexNum = i.indexnum
where id=1;

Personally, I prefer to use the join syntax, it's a cleaner way to see the query.

Upvotes: 1

Related Questions