SQL Query returning duplicate with any command with foreign keys-CodePudding

Other answers seem to be using JOIN, which correct me if I'm wrong you don't need to use if you have a foreign key? If you do need to use JOIN would you mind showing what would work with this?

CREATE TABLE Dogs (
DogID int NOT NULL,
DogSize int NOT NULL,
fName VARCHAR(255),
ID int,
PRIMARY KEY (DogID),

CREATE TABLE Owners (
ID int NOT NULL,
Fname VARCHAR(255) NOT NULL,
Lname VARCHAR(255),
Area VARCHAR(255) NOT NULL,
Pay INT NOT NULL,
Extra INT,
PRIMARY KEY (ID)
FOREIGN KEY (ID) REFERENCES Owners(ID)

The result of a SELECT * query

CodePudding user response：

A foreign key is only there to make sure your data are well-organized. It does not help you "behind the scenes" when making queries. And it certainly doesn't make it good to not use joins.

Your query is an old-style query, which for many reasons you shouldn't use. The equivalent of what you wrote is the following:

SELECT * FROM Dogs CROSS JOIN Owners

This is called a cartesian product, which means all the combinations are returned, since you didn't provide a join condition. I assume you didn't because you thought it would be automatically provided by the foreign key....but it doesn't work that way. You have to provide what you want yourself.

Now for the next problem. You do not really have a connection. The foreign key on Owners is targeting not only the table itself, but actually the column itself. That makes zero sense. Did you maybe want to assign one dog to each owner? There should be another column on Owners (not the primary key one) that would be a foreign key to Dogs:

[DogID] int,FOREIGN KEY (DogID) REFERENCES Dogs(ID)

This is better. Still, not very good; it would limit the dogs owned by each owner to one or zero. A better idea would be to make a new column in Dogs that would reference the owner:

[OwnerID] int,FOREIGN KEY (OwnerID) REFERENCES Owners(ID)