CODEWITHSUNDEEP
mysqlsqllaravelkey
Thomsath
Thomsath

Reputation: 197

A foreign key can be primary in sql

I'm building an application (under Laravel). In this application, in want that users can share they current location. My sql scheme is relatively simple : I've a location table and users.

In my location table I've an id, longitude, altitude, and the user_id. The user id is a foreign key to the user table, and the id the primary.

My issue is : I would like to insert column into my location table for each user (and update altitude and longitude if user_id already in the location table).

Is it a good option to make the foreign key 'user_id', also a primary key in my location table ? The aim is to use 'INSERT OR UPDATE' to update a column if the user id is in the location table (to avoid 1200000 lines in my table).

Upvotes: 0

Views: 49

Answers (1)

UfguFugullu
UfguFugullu

Reputation: 2147

to aim insert or update

You should use the id of the location table as a primary key and you can set some other fields as a unique key. I used a online generator to get the following table definition.

CREATE TABLE `locations` (
    `id` BIGINT unsigned NOT NULL AUTO_INCREMENT,
    `user_id` BIGINT unsigned,
    `latitude` FLOAT(11.8),
    `longitude` FLOAT(11.8),
    UNIQUE KEY `locations_primary_key` (`id`) USING BTREE,
    UNIQUE KEY `locations_unique_01` (`user_id`,`latitude`,`longitude`) USING BTREE,
    PRIMARY KEY (`id`)
) ENGINE=InnoDB;

In laravel you can use FirstOrNew or FirstOrCreate to insert or update your record. Any help you can get in the laravel docs here. Here is an example just for orientation (I can't test the code right now):

$user_id = 1;
$location = App\Location::firstOrCreate(
    ['user_id' => $user_id],
    ['latitude' => 10.12345, 'longitude' => 9.876]
);

Upvotes: 1

Related Questions