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,…

Accepted Answer

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

How can I make three columns my primary key

Solution

Problem