How can I make three columns my primary key

Question

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.

The first file(600.000 rows) contains 4 columns:

Postal, Number, Houseletter, livingspace

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.

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

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.

Thanks for your help!

ps: Im using sql in phpmyadmin

Answer 1

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);

if you got duplicate PKs, you can try this:

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

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)