Update Set = Select Statement & Primary Key Question

I have a table:

TRADESHOW
id
name
industry

Where industry is currently listed as the text-name of the industry (Software, Hardware, Networking, etc.). I am thinking about moving industry to a separate table, which seems like a better design and also makes the data more开发者_如何学Python consistent and easier for my application to add or edit different industries. The new table would be:

INDUSTRY
id
name

And TRADESHOW.industry would become TRADESHOW.industry_id

My questions:

1. Does this change make sense? Should the INDenter code hereUSTRY table even have a surrogate key (INDUSTRY.id) or would one column of INDUSTRY.name make more sense as the primary key?

2. What would be the be the best way to form an UPDATE statement to fix the TRADESHOW table after implementing the INDUSTRY table? I'm thinking I would need an UPDATE with a SELECT statement? Assuming it makes sense to keep a surrogate key on industry, I'm thinking something like:

..

UPDATE tradeshow SET tradeshow.industry =
(SELECT id FROM industry WHERE tradeshow.industry=industry.name)

Would that work? (I know it won't be perfect, as the industry names now are not 100% consistent.) Is there a better way? Thanks!

1. You should definitely have a industry table, it is good design practice based on the third normal form, and the primary key can be an ID (like you have). The TRADESHOW table will have the indsutry_id as the FK, Name would also make a good primary key, but typically when there is a lot of FK references to it, the storage can become big.

2. The way you have it now, it won't work if there are two records in your INDUSTRY table, that have the same industry name. I assume you are populating the table from your existing TRADESHOW table? in which case, you will do a SELECT UNIQUE industry to create the INDUSTRY table, so it SHOULD be unique. And then you can run the update query you have for the first time population.

This works for me:

UPDATE appm_personnel SET appm_personnel.`PASSWORD`=(
   SELECT authorise.authorise_pass
   FROM authorise 
   WHERE authorise.authorise_idcard = appm_personnel.ID_CODE LIMIT 1 )
WHERE LENGTH(appm_personnel.ID_CODE)=13

It makes sense to go for a different table for industry if for a same Id, Name in TradeShow you have multiple Industries. If you do decide to go ahead with a separate table for Industry I would suggest to have Industry_Id in the TradeShow table than Industry_Name

TradeShow
Id
Name
IndustryId

Industry
Id
Name

Keeping this in mind, your update statement will be

Update TradeShow
Set Tradeshow.IndustryId = (Select Id From Industry where Name = 'abs')
Where TradeShow.Id = 12

Where Industry.Name and TradeShow.Id are inputs

1. Yes it makes sense. But consider if you really need this normalization. If it makes sense to you, it should be done. About the pk: As a rule, always have a id primary key for better performance and consistency. There's almost never any advantage to remove it.

2. Seams almost right:

   ALTER to add tradeshow.industry_id
   UPDATE tradeshow SET tradeshow.industry_id =
   (SELECT id FROM industry WHERE tradeshow.industry=industry.name)
   ALTER to remove tradeshow.industry