Carlos Sanchez Carlos Sanchez - 5 months ago 25
MySQL Question

How to make Laravel migrations of SQL

I'm having some difficulties trying to make a laravel migration from SQL database, this is my SQL:

CREATE TABLE `customers` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`user_id` int(11) unsigned NOT NULL,
`taxtype_id` int(10) NOT NULL,
`code` varchar(50) COLLATE utf8_unicode_ci NOT NULL,
`name` varchar(100) COLLATE utf8_unicode_ci NOT NULL,
`rnc` varchar(30) COLLATE utf8_unicode_ci NOT NULL,
`ced` varchar(30) COLLATE utf8_unicode_ci NOT NULL,
`address` mediumtext COLLATE utf8_unicode_ci NOT NULL,
`address2` mediumtext COLLATE utf8_unicode_ci NOT NULL,
`sector` varchar(100) COLLATE utf8_unicode_ci NOT NULL,
`city` varchar(100) COLLATE utf8_unicode_ci NOT NULL,
`state` varchar(100) COLLATE utf8_unicode_ci NOT NULL,
`country` varchar(100) COLLATE utf8_unicode_ci NOT NULL,
`phone` varchar(20) COLLATE utf8_unicode_ci NOT NULL,
`email` varchar(100) COLLATE utf8_unicode_ci NOT NULL,
`website` varchar(100) COLLATE utf8_unicode_ci NOT NULL,
`active` tinyint(1) NOT NULL,
`created_date` date NOT NULL,
`created_by` varchar(100) COLLATE utf8_unicode_ci NOT NULL,
`created_at` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
`updated_at` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
`deleted_at` timestamp NULL DEFAULT NULL,
PRIMARY KEY (`id`,`taxtype_id`),
UNIQUE KEY `Unique Fields` (`rnc`,`code`,`ced`) USING BTREE,
KEY `id` (`id`) USING BTREE,
KEY `user_id` (`user_id`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=9 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;


and this is what ive been trying to do but its not working in the laravel database migration:

Schema::create('customers', function (Blueprint $table) {
$table->increments('id');
$table->integer('user_id',11)->unsigned();
$table->integer('taxtype_id',10)->unsigned();
$table->integer('code',11);
$table->string('name', 100);
$table->string('rnc');
$table->string('ced');
$table->mediumText('address');
$table->mediumText('address2');
$table->string('sector');
$table->string('city');
$table->string('state');
$table->string('country');
$table->string('phone');
$table->string('email');
$table->string('website');
$table->boolean('active');
$table->timestamp('created_date');
$table->string('created_by');
$table->timestamps();
$table->softDeletes();
});


Does anyone have more experience to help me out on this migration? or guide me on what im doing wrong? when I run php artisan migrate i always get SQLSTATE[42000]: Synxtax error or access violation, any help would be really appreciate it. Thank you all!

Answer

I am going with ButtonPiglet on this one, Laravel doesn't allow specifying lengths on integer fields. It is causing user_id, taxtype, and code to be auto_increment primary keys.

I change these three from

$table->integer('user_id',11)->unsigned();
$table->integer('taxtype_id',10)->unsigned();
$table->integer('code',11);

to this

$table->integer('user_id')->unsigned();
$table->integer('taxtype_id')->unsigned();
$table->integer('code');

and was able to run the migration just fine on my machine using laravel 5.2. Using integer() will set it to the length of 10 in DB. If you need anything smaller user tinyInteger(), smallInteger(), or mediumInteger(). For larger length use bigInteger()