0

I would like to create a primary key that is complex from two different columns and also IDENTITY. For example:

My table:

create table Location (
     locationID int primary key,
     lat int not null,
     lng int not null,
)

lat = 43
lng = 567

I would like the primary key value will be like the following:

locationID = 43.567-1

The "-1" will be add if there is already a locationID that starts with "43.567".

Is it possible at all?

philipxy
  • 14,867
  • 6
  • 39
  • 83
Gal Polak
  • 3
  • 4
  • 1
    I think you should rethink your DB schema. Why have `locationID` if your `lat`/`long` are truly the composite primary key? Why would you want to have multiple location IDs with the same lat/long? It just doesn't make any sense. lat/long shouldn't be `int` and `locationID` stops being an `identity` if you force populate it like that too. – Eric Hotinger Feb 17 '16 at 16:50
  • thank you for reference, but what i wrote here is just an example. the idea is for later on to use the way the key was combined in order to understand and use it in different ways. – Gal Polak Feb 18 '16 at 07:23
  • 1
    It's a bad idea to encode information in primary/candidate keys. Search re [smart/intelligent keys](http://stackoverflow.com/a/31397135/3404097). – philipxy Feb 22 '16 at 05:52
  • What do you mean, can you do it? Yes you can have the values be like that. (Although it's a bad idea.) Do you mean something other than that? – philipxy Feb 22 '16 at 06:05
  • Bad idea. Either go with composite primary key or keep it strictly autogenerated. Remember there's a difference between primary key and indexing – Allan S. Hansen Feb 22 '16 at 07:36

3 Answers3

0

Not tested by myself, but it could be possible using triggers

http://dev.mysql.com/doc/refman/5.7/en/trigger-syntax.html

However you may have problems if you try to issue the query without a value for the primary key

Nadir
  • 1,799
  • 12
  • 20
  • The link provided is for mysql, most of the other sql providers supports triggers as well – Nadir Feb 17 '16 at 16:29
0

You can also just use compound keys, here you can do so by using lat and lng. The beauty of compound keys is that only one of the values of the constituting columns has to be unique.

Jay T.
  • 307
  • 1
  • 6
0

You can make a compound primary key but it would still have to be unique. There is no point to what you are trying to do. You are trying to force non unique values to be unique by adding arbitrary values to duplicates in order to make then unique.

The correct way to approach this would be to have either no repeated lat/lng combinations possible or to simply use your LocationID as the primary key.

Anton
  • 422
  • 2
  • 9