Stack Overflow
Stack Overflow

Questions tagged [shared-primary-key]

Shared Primary Key is a technique used in relational database design when it is desired to enforce a one-to-one relationship between rows in two or more tables (relations).

One-to-one relationships are typically of the IS-A variety of relationships. IS-A relationships are known in object modeling as class/subclass designs. IS-A relationships are known in ER modeling as generalization/specialization designs.

Shared primary key comes with a cost. In general, DBMS products support part but not all of the shared primary key concept. In the main table, the primary key is just declared in the usual way. In any secondary tables, the key is declared both as a primary key and as a foreign key, referencing the primary key in the main table. This is enough to enforce the one-to-one relationship.

When new entries are to be made in the main table and in one or more secondary tables, the program doing the insert has to insert the row into the main table first, and then propagate the value of the main table's primary key to the secondary table(s). This propagation can be thought of as "poor man's inheritance".

Note that the entry in the secondary table is optional, while the entry in the main table is mandatory in order to maintain referential integrity. Also note that you shouldn't use the autonumber feature of the DBMS in the primary keys in the secondary table.

Here are the nice features you get from using a shared primary key. Most DBMS products will create indexes on all columns (fields) that are declared as primary keys. And most DBMS products will use a very fast algorithm when performing joins where there is an index on both sides of the join condition. Also, a join between the main table and a secondary table will automatically drop out entries in the main table that do not pertain to the given secondary table.

A foreign key used in some third table that references the main table will automatically reference the related rows in relevant secondary tables. This makes the resulting data structure more flexible.

Shared Primary Key is very often used in conjunction with a design pattern described under the tag . The relationship between classes and subclasses is an IS-A variety of relationship.

39 questions
32
votes
3 answers

Understanding ForeignKey attribute in entity framework code first

See the following post for some background: Entity framework one to zero or one relationship without navigation property I had always thought that ForeignKey was used to show which property in a class held the ForeignKey that determined the…
Mark007
  • 345
  • 1
  • 4
  • 8
5
votes
7 answers

Shared Primary Key

I would guess this is a semi-common question but I can't find it in the list of past questions. I have a set of tables for products which need to share a primary key index. Assume something like the following: product1_table: id, name, …
Endophage
  • 21,038
  • 13
  • 59
  • 90
4
votes
2 answers

Propagate primary key to child tables

I want to propagate the value of a primary key column from a parent table to a specific child table when inserting a new row. For explanatory purposes I've created following tables: Create TABLE Material( MatID serial PRIMARY KEY, materialname…
Dede1989
  • 53
  • 1
  • 8
4
votes
1 answer

EF6 failing to build model for Table Split/Shared Primary Key + Base class?

The problem I'm trying to share a large table (200+fields) around ~7 entities using table splitting as per my previous question. EF6 requires navigation properties not just from the primary model to the child models, but between all the child models…
Brendan Hill
  • 3,406
  • 4
  • 32
  • 61
4
votes
2 answers

EF6 Table splitting vs shared primary key for multiple splits

I am upgrading a legacy database system into .NET + Entity Framework 6 (code-first POCO) + PostgreSQL. For ease of programming, I wish to split a large table (200+ fields) into multiple entities,…
Brendan Hill
  • 3,406
  • 4
  • 32
  • 61
3
votes
1 answer

Custom primary key generator in JPA

I am using org.hibernate.id.IdentifierGenerator to generate a primary key column as follows. In the following given example, currently it just increments the key of type INT(11) (MySQL) sequentially i.e. it does like auto_increment in MySQL but it…
Tiny
  • 27,221
  • 105
  • 339
  • 599
3
votes
2 answers

Shared Primary key versus Foreign Key

I have a laboratory analysis database and I'm working on the bast data layout. I've seen some suggestions based on similar requirements for using a "Shared Primary Key", but I don't see the advantages over just foreign keys. I'm using…
Kevin Nowaczyk
  • 227
  • 1
  • 15
2
votes
2 answers

Can I use my foreign key as my primary key in most of my tables?

I'm trying to make a database structure of an online flight booking website. Its just a project of mine. I was wondering i could use a foreign key as my primary key on a table. Here how one of my table looks like: Table name: Customer_Account it has…
TheAsker101
  • 95
  • 7
2
votes
2 answers

crc32 of natural key as a primary key

I have a database of TCG cards and I am trying to decide a primary key. I settled with a surrogate key initially but I realized that sometimes, there are cards that I forget to add, like promo cards for example. This is problematic with the…
voldomazta
  • 1,300
  • 1
  • 10
  • 19
2
votes
1 answer

Symfony/Doctrine class table inheritance and foreign key as primary key

I am currently designing a web application with Symfony 2.5 (and Doctrine 2.4.2) that has to be flexible to easily plug in new modules/bundles. So I have an entity (let say A) that has two one-to-one associations with abstract classes (B and C). The…
Elioty
  • 81
  • 6
1
vote
2 answers

JPA - One to One relationship with shared primary key among multiple tables

Consider the below example: I have 3 tables: Fruit, Orange and Apple id is generated in fruit table and is the primary key here id is also primary key for Orange and Apple (shared primary key) So for e.g. if id in fruit is 1, 2, 3, 4, 5 -- then…
Vicky
  • 16,679
  • 54
  • 139
  • 232
1
vote
1 answer

How to make sure hibernate 5 persists in the correct order in a one-to-one relationship with shared primary key

TLDR: Switched Hibernate Versions from 3. to 5.. Now, OneToOne Relationship with shared primary key that is only mapped in one class persists the two entities in the wrong order, violating a foreign key constraint. How to change the order? In the…
hpr
  • 36
  • 4
1
vote
1 answer

How to implement a shared primary key using hibernate @ID

I have two tables in my database. The first table auto generates its primary key and I want the second table to get its primary key from the PK of the first table. Is it possible to implement this using hibernate as when I don't include a…
Poppy
  • 11
  • 2
1
vote
1 answer

@PrimaryKeyJoinColumn not picking the shared key

I got the @PrimaryKeyJoinColumn working before , now I'm trying with spring boot and I can't figure what I'm missing , it's very weird as it seems I have done everything right : Person class : @Table(name =…
Mohammad Karmi
  • 1,403
  • 3
  • 26
  • 42
1
vote
1 answer

How to cascade save a simple shared primary key one-to-one mapping in hibernate 3.6

I need a simple hibernate example of an entity with a one-to-one relationship with another entity where they both share the primary key. I need to only have to save the main entity that is auto-generating its primary key and the other dependent…
at.
  • 50,922
  • 104
  • 292
  • 461
1
2 3