BigInt vs Int

2021_11_29_124458_create_orders_table.php

	public function up()
	{
    Schema::create('orders', function (Blueprint $table) {
        $table->id();
        $table->integer('user_id');
        $table->string('order_name');
        $table->string('order_address');
        $table->string('order_email');
        $table->char('order_phone', 15);
        $table->string('order_province');
        $table->string('order_city');
        $table->string('order_province_destination');
        $table->string('order_city_destination');
        $table->string('order_confirm_name');
        $table->integer('order_bank_id');
        $table->char('order_bank_pin', 15);
        $table->string('order_shipping_number');
        $table->bigInteger('order_shipping_cost');
        $table->char('order_shipping_type', 8);
        $table->char('order_coupon_id', 10);
        $table->bigInteger('order_discount');
        $table->bigInteger('order_total_cost');
        $table->bigInteger('order_total_weight');
        $table->integer('order_total_unit');
        $table->integer('order_status');    
        $table->timestamps();
    	});
	}

Hello, I am trying to distinguish between integer and bigInteger, what is the difference?

Looks like in my case I don't need to use bigInteger then since integer has the maximum value of: 2,147,483,647

and also, do I need to assign the number of the integer? such as: $table->bigInteger('order_discount', 8);

ref: https://www.google.com/search?client=firefox-b-d&q=integer+vs+biginteger

Sergiu17

4 years ago

Level 60

https://dev.mysql.com/doc/refman/8.0/en/integer-types.html

Looks like in my case I don't need to use bigInteger then since integer has the maximum value of: 2,147,483,647

Yes, INT should be fine, also make them unsigned ( check first link )

and also, do I need to assign the number of the integer? such as: $table->bigInteger('order_discount', 8);

Second parameter is $autoIncrement https://github.com/laravel/framework/blob/8.x/src/Illuminate/Database/Schema/Blueprint.php#L808

Tray2

4 years ago

Level 73

The only difference is that one is bigger than the other.

However since Laravel uses big int as default for the primary key (->id()) you need to use the same data type on all your foreign keys that points towards the id of anoother table.

So in your migration you need to change

$table->integer('user_id');
$table->integer('order_bank_id');

To bigInteger()->unsigned or use the ->foreignId() instead.

https://laravel.com/docs/8.x/migrations#foreign-key-constraints

davy_yg

4 years ago

Level 27

@Tray2

Looks like in my case I don't even need to use BIGINT I don't need any number upto hundred of million.

	$table->integer('user_id');
	$table->foreignId('user_id');

Also, I don't understand this sentences: "If your table name does not match Laravel's conventions, you may specify the table name by passing it as an argument to the constrained method".

Do I need to put ->constrained() in the backof my $table->foreignId('user_id') ?

Also what is the point of putting a number like $table->integer('user_id', 100) , do I even need to do that ?

Tray2

4 years ago

Level 73

@davy_yg Laravel has a naming convention when it comes to models, tables, foreign key and a few other things.

Models should always be in singular form. So if you want to store information about cars, then you call that model the singular form of the word cars. So the model becomes Car. Notice the capital C models alway start with a capital letter, Car, Book, Movie and so on.
Database tables are always named in plural form of the model name. As in the example above, the Car model would have a table named cars. All table names are in lower case letters. cars, books, movies and so on.
When it comes to a many-to-many relations then the naming convention for the pivot table is the combination of the model names all in lower case letters and in alphabetical order separated by an underscore , car_user, book_user, movie_user and so on.
If a table name need two words like blog post then the model becomes BlogPost and the table name becomes blog_posts.
Foreign keys should always be named the table it references in singular form followed by an underscore and the column if references, as in your example user_id.

Now back to your questions, if you for some reason don't follow the naming conventions you can pass the custom column name as an argument. I highly recommend sticking to the naming convention that Laravel uses.

Do you need to use ->constrained(), I'd say yes to that. That makes it impossible to add a foreign key into your table that doesn't exist in the table the foreign key is referencing. It also gives you the possibility to use cascades to delete the records that has the foreign key that is deleted in the referencing table.

Let's say we have a blog post and it has a foreign key user_id with on delete cascade set, and the user is deleted from the users table then the constraint would kick in and the blog post would be deleted as well. If you don't add any cascade and you try to delete the user the database will give you an error stating that you have an integrity constraint violation and that it has child records that needs to be deleted first.

Limiting the user_id foreign key is not necessary and I would say a bad practice. I suggest you stick to the defaults even if you don't need to use unsigned big integers in your primary and foreign keys.

If you really need change make sure that you use unsigned integers, that you primary key has auto increment.

2 likes

lbecket

4 years ago

Level 39

The link you provided pretty well sums it up; it boils down to the size of the value the field can hold. You do not have to define it with an additional parameter as illustrated in the docs: https://laravel.com/docs/master/migrations#column-method-bigInteger

One thing to note, however, is that you will have to be consistent when it comes to any foreign key relationships and that the standard $table->id(); field will be a big integer by default. This means that any foreign keys would also need to be defined as bigInteger.

Please or to participate in this conversation.