migration primary key

[ostapetc]

Why migration creates primary key without unsigned attribute?

[qiangxue]

What is "unsigned attribute"? What is the problem?

[klimov-paul]

I suppose @ostapetc means migration should create ‘unsigned integer’ instead of simple ‘integer’ for the primary key with auto increment. Unsigned types have higher maximum value, then normal, so the auto increment less likely will be exhausted.

Similar request was already posted at Yii1:
yiisoft/yii#1923

[qiangxue]

I see. Does my comment yiisoft/yii#1923 (comment) make sense to you then?

[cebe]

Comment makes sense, when you need an unsigned integer you can write your own column definition instead of using the abstract types.

[ostapetc]

@klimov-paul thanks for help

[klimov-paul]

@ostapetc, I understand you are upset, but I am afraid there is nothing I can do here. But, OK, I’ll try.
Let’s have a look on this @qiangxue’s comment once more:

Using int has the benefit that it can be represented in PHP as an integer. If unsigned, for 32 bit system, we would have to use string to represent it

Representing the high integer at 32-bit system could be a problem only, if its actual value will overflow PHP max_int for 32-bit system. In general it should be safe. Also just when you last saw 32-bit server system?

Also although unsigned int doubles the size

Well, I have checked the MySQL manual once again
http://dev.mysql.com/doc/refman/5.1/en/integer-types.html
It says rather clear that both ‘signed’ and ‘unsigned’ integer has just the same size.
Do you remember difference between ‘signed’ and ‘unsigned’ data types? At the lowest level (CPU) any integer value is stored in binary representation, for example number ‘8’ will be ‘00001000’. The trick is now to remember the sign of the number: ‘+8’ or ‘-8’. To track the sign the first bit is used. So ‘+8’ will be ‘00001000’, while ‘-8’ will be ‘10001000’. Tracking the sign of the number decrease it maximum value, because the first bit, which covers 2^7 = 128 for single byte values, is reserved to remember sign of the number.
Using ‘unsigned’ integer instead of ‘signed’ one increases its maximum positive value by the cost of disallowing negative value storage. It is just what is needed for ‘Auto Increment’ primary key, otherwise you are losing 1 bit for values, because auto increment value is always more then 0.

Do not forget, that using magic ‘pk’ in migration increases its university, so migration written for MySQL can be applied to PostgreSQL as well. While custom PK definition will be stuck for the particular DBMS.

@qiangxue, @cebe, if you are still think this feature should not be implemented, just close this issue once more.

[ostapetc]

+1
ps: unsigned attribute provides some data integrity. I can be sure that my pk values has only positive integers

[qiangxue]

PHP on Windows are usually 32bits build since php.net states the x64 builds of PHP for Windows should be considered experimental.

Also imagine in practice, if you are defining a PK for a table which is very likely to have billions of rows, do you think you would consider using unsigned int or bigint? The extra doubling doesn't really relieve you from worrying the key space.

[ostapetc]

"consider using unsigned int or bigint?" - i will choose bigint unsigned, because i do not plan store negative integers

[qiangxue]

Many DBMS actually don't even support unsigned types: mssql, oracle, pgsql, cubrid...

[cebe]

I do not remember why I reopened it but as @qiangxue said there are many dbms that do not support unsigned integer. if you want your PK to be unsigned you can write explicit column definition. we will not add it to the abstract types for consistency.

[cebe]

Do not forget, that using magic ‘pk’ in migration increases its university, so migration written for MySQL can be applied to PostgreSQL as well. While custom PK definition will be stuck for the particular DBMS.

btw: If you want to create portable code, unsigned is not your friend. So not an argument for it.