SQL查询用外国钥匙的任何命令返回重复
其他答案似乎正在使用加入，如果我错了，如果您有外键，您不需要使用哪个答案？如果您确实需要使用加入，您会介意显示与此作用的功能吗？
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)
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)
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（1）
外键才能确保您的数据井井有条。进行查询时，它无济于事。当然，不使用加入并不是一件好事。您的查询是一个老式的查询，出于许多原因，您不应该使用。相当于您所写的内容如下：
这称为笛卡尔产品，这意味着所有组合都会返回，因为您没有提供
加入条件。我认为您不是因为您认为它会由外键自动提供。...但是它不起作用。您必须提供自己想要的东西。现在解决下一个问题。您实际上没有连接。所有者上的
外键不仅针对表本身，而且针对列本身。这是零有意义的。您可能想为每个主人分配一只狗吗？ 应该有所有者上的另一列（不是主键一个），这将是dogs的外键：[Dogid] INT，外键（Dogid）参考狗（ID）这更好。不过，不是很好；它将将每个主人拥有的狗限制为一个或零。一个更好的主意是在狗中制作一个新的专栏，以引用所有者：
[lansalId] int，forefer键（handerID）参考所有者（id）A
foreign keyis 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:
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 keyon 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 onOwners(not theprimary keyone) that would be a foreign key toDogs:[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)