我怎样才能使三列成为我的主键 [英] How can I make three columns my primary key

问题描述

我正在尝试学习如何将两个文件匹配在一起.但是我现在正在尝试everythng 5小时了……仍然不知道该怎么办.

Im trying to learn how to match two files together. But I'm trying everythng for 5 hours now... And still no idea what to do.

第一个文件(600.000行)包含4列:

The first file(600.000 rows) contains 4 columns:

Postal, Number, Houseletter, livingspace

第二个文件(7.000行)包含4列:

The second file(7.000 rows) contains 4 columns:

Postal, Number, Houseletter, Furniturevalue

在我的第一个文件中，我拥有一个大区域的所有生活空间，而在我的第二个文件中，我具有该大区域中几个地址的家具价值.

In my first file I have all the livingspaces from a big area and in my second file I have the Furniturevalue of a couple of adresses in that big area.

我想在第二个文件的地址中添加文件1中的生活空间.

I want to add to the adresses in my second file the livingspaces from file 1.

因此，我将文件导入了数据库中.

So I imported the files in a database.

Table first file -> Space
Table second file -> Furniture

现在我正在尝试对表进行主键操作:

Now i'm trying to make Primary keys to the tables:

Primary key --> Postal, Number, Houseletter

但这是行不通的，因为这些列仅在Postal + Number + Houseletter时才是唯一的，而不能彼此分开.

But this doesn't work, because the columns are only unique when Postal+Number+Houseletter, but not apart from each other.

有人知道下一步吗?要使此查询正常工作，我该怎么做:

Does anyone know the next step? What do I have to do to make this query work:

SELECT postal, number, houseletter, furniturevalue, livingspace
FROM space, furniture
WHERE ( space.postal = furniture.postal
AND     space.number = furniture.number
AND     space.houseletter = furniture.houseletter)

我正在尝试使用此查询创建一个新的视图，其中包含邮政，电话号码，房屋，家具价值，居住空间" 因此，数据来自两个表. 但是首先我需要解决主键问题.

Im trying to make with this query a new view with `postal, number, houseletter, furniturevalue, livingspace' So data from two tables. But first I need a solution for my problem with the primary key.

感谢您的帮助！

ps:我在phpmyadmin中使用sql

ps: Im using sql in phpmyadmin

推荐答案

ALTER TABLE space ADD PRIMARY KEY(Postal, Number, Houseletter);

如果主键已经存在，那么您要执行以下操作:

If a primary key already exists then you want to do this:

ALTER TABLE space DROP PRIMARY KEY, ADD PRIMARY KEY(Postal, Number, Houseletter);

如果您有重复的PK，可以尝试以下操作:

if you got duplicate PKs, you can try this:

ALTER IGNORE TABLE space ADD UNIQUE INDEX idx_name (Postal, Number, Houseletter );

这将删除所有重复的行.另外一个好处是，将来重复出现的INSERT将出错.与往常一样，您可能需要先备份，然后再运行类似的操作

This will drop all the duplicate rows. As an added benefit, future INSERTs that are duplicates will error out. As always, you may want to take a backup before running something like this

第二个问题，您的查询应如下所示:

Second question, your query should look like this :

SELECT postal, number, houseletter, furniturevalue, livingspace
FROM space INNER JOIN furniture
ON ( space.postal = furniture.postal
AND     space.number = furniture.number
AND     space.houseletter = furniture.houseletter)

这篇关于我怎样才能使三列成为我的主键的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！