Is it necessary to have a primary key ID with Auto Increment if I have a UNIQUE field INT?

I want to store in a table a lis开发者_如何学Got of IP ADDRESS to check later if some IP is already used in my system.

I want to store the ip in longip mode (signed int). And since each IP is unique i want to know if is necessary to have a primery key field (id, with autoincrement) or if its okey (and better) to just use my longip field as primary key.

---

if in the future you have to use the key for joining it with another table, the other table should contain all the number, and that's a lot of space wasted.

for example, you have a "computer" table

in that table, you have computes with ip's. For joining you need a key right? so, if you join by key, you should have the computer id and the key (in this case the ip)

I higly recommend to use a simpler id with autoincrement, like it's beeing done since mainframe (as400), iSeries, etc.

---

I think Marc_s' answer to the question When not to use surrogate primary keys? can guide us

I would say the following criteria must be met:

• your natural key must be absolutely, positively, no-exceptions-allowed, unique (things like names, social security numbers etc. usually seem to be unique - but really aren't)

• your natural key should be as small as an INT, e.g. not significantly more than 4 bytes in size (don't use a VARCHAR(50) for your PK, and especially not for your clustering key in SQL Server!)

• your natural key ought to be stable, e.g. never change (OK, with ISO country codes, this is almost a given - except when countries like Yugoslavia or the USSR collapse, or other like the two Germanies unite - but that's rare enough)

If those conditions are met, you can consider a natural key as your PK - but that should be the 2% exception in all your tables - not the norm.

So I would say you should probably use a surrogate primary key. You can always use IP as a unique key if you want to

Since you're using the longip which as you pointed out is probably ok to use it.

---

Almost every time you start out with a natural key, you will end up regretting it later. Something will happen happen, its Murphy's law. Spare yourself the trouble, just add the ID column.

If its the auto-increment you don't like just use a uuid. MySql has a uuid function to make that easy.