Stack Overflow
Stack Overflow

Questions tagged [natural-key]

A key in a database table that is used to identify entities in the business domain outside the database. It may be comprised of attributes that have intrinsic logical meaning.

The term natural key describes a key which is used for external real world identification of things and is different from a surrogate key which is not used in the business domain.

57 questions
3
votes
3 answers

Surrogate key 'preference' explanation

As I understand there is a war going on between purists of natural key and purists of surrogate key. In likes to this this post (there are more) people say 'natural key is bad for you, always use surrogate... However, either I am stupid or blind but…
RandomWhiteTrash
  • 3,974
  • 5
  • 29
  • 42
3
votes
3 answers

Natural vs surrogate keys on support tables

I have read many articles about the battle between natural versus surrogate primary keys. I agree in the use of surrogate keys to identify records of tables whose contents are created by the user. But in the case of supporting tables what should I…
Marco Staffoli
  • 2,475
  • 2
  • 27
  • 29
2
votes
1 answer

Find a Natural Key in a dataset

I inherited a table with about 40 columns and about 1400 rows. Unfortunately, the original designers didn't include an identifiable PK in the table so I'm assuming they used a natural key with a combination of 4 or 5 columns. However what those…
Jabsy
  • 171
  • 4
  • 16
2
votes
2 answers

Using National ID as Primary Key

I'm developing a system that make use of national ID one of "identifying record" is it a good idea to embed nat_id in all the tables to make query easier? What are the downsides of doing so? I just want to get opinions from people here in SO because…
Random Joe
  • 640
  • 4
  • 10
  • 25
2
votes
1 answer

Why would one consider using Surrogate keys vs Natural with ON UPDATE CASCADE?

Disclaimer: This is not the same question as the other topics. One of the cons we face when using Natural Keys, is that, if the business logic changes and we need to change one key, we need to propagate this change throughout all linked…
PedroD
  • 5,670
  • 12
  • 46
  • 84
2
votes
1 answer

Mostly immutable NaturalId

I have a simple public class SomeEntity { @Id private Integer id; @NaturalId private User owner; @NaturalId(mutable=true) private String name; ... } and the natural id creates a unique key (good!) and it makes owner immutable from…
maaartinus
  • 44,714
  • 32
  • 161
  • 320
2
votes
3 answers

Lookup Table -- Natural or Surrogate key as primary key?

I have a table for recording license usage. Each license usage needs to be associated with a user and a host machine. The table definition looks like this. create table if not exists per_user_fact ( per_user_fact_id int unsigned …
Pankaj Dwivedi
  • 379
  • 1
  • 6
  • 16
2
votes
2 answers

Natural Key causes Django tests to fail

Consider a clean django 1.7.7 project with one app called testrunner. The models look like this: class Contact(AbstractBaseUser, PermissionsMixin, models.Model): relation = models.ForeignKey('tests.Relation', related_name='contacts') …
Michel Rugenbrink
  • 446
  • 6
  • 14
2
votes
2 answers

Why doesn't @NaturalId create a unique constraint in the database?

I'm having following Entity-Snipped @Entity public class EntityWithNaturalId extends BaseEntity { @NaturalId public String name; private EntityWithNaturalId() {} public EntityWithNaturalId(String name) { this.name = name; } } If I…
Marcel
  • 4,054
  • 5
  • 36
  • 50
2
votes
2 answers

Can I use a non-numerical primary key for a MySQL table?

In my web application, the user can define documents and give them a unique name that identifies that document and a friendly name that a human will use to refer to the document. Take the following table schema as an example: | id | name …
John Dorean
  • 3,744
  • 9
  • 51
  • 82
2
votes
1 answer

@NaturalId only valid on root entity (or its @MappedSuperclasses), using Natural Id in Joined, Multiple Table Inheritance

basically I cant find in google some similar problems by just pasting the root exception "@NaturalId only valid on root entity (or its @MappedSuperclasses)" in the search tab. I'm using a Joined, Multiple Table inheritance strategy to map my…
Jeff
  • 519
  • 1
  • 10
  • 18
2
votes
1 answer

Django natural keys not working for fixtures?

I'm having trouble with fixtures/natural keys in Django. I think I've addressed most of the usual problems, like those specified in this answer. This might all be a red herring, as get_by_natural_key works just fine in the shell: >>> artifact =…
allanberry
  • 7,325
  • 6
  • 42
  • 71
2
votes
1 answer

Django fixtures primary key error, need natural keys solution

So I have a Film model that holds a list of Actors model in a many to many field: class Person(models.Model): full = models.TextField() short = models.TextField() num = models.CharField(max_length=5) class Film(models.Model): name =…
dl8
  • 1,270
  • 1
  • 14
  • 34
2
votes
2 answers

How to create a fact table using natural keys

We've got a data warehouse design with four dimension tables and one fact table: dimUser id, email, firstName, lastName dimAddress id, city dimLanguage id, language dimDate id, startDate, endDate factStatistic id, dimUserId, dimAddressId,…
s.froehlich
  • 863
  • 1
  • 11
  • 19
2
votes
1 answer

Gorm change primary key, not found with id null

I have read tons of questions about this and it seems that nobody gets it to work. I am using grails, and I am creating a class that doesn't have id as the primary key. I get the message "usuario not found with id null". this is the code of my…
Juan Sebastian
  • 968
  • 1
  • 7
  • 20
1
2
3 4