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
1
vote
2 answers

Hibernate zeroToOne

I am trying to establish a relationship between 2 entities which would be zero-to-one. That is, the Parent can be saved without the associated Child entity and also along with the assoicated Child. Following are the 2 Entity classes... Employee…
PaiS
  • 1,282
  • 5
  • 16
  • 23
1
vote
2 answers

Shared primary key generation. Hibernate

I'm having problems with generating primary keys with one-to-one relations that use shared primary key. Here's code: @Entity @Table(name = "osoba") public class Osoba implements Serializable { @Id @GeneratedValue(strategy =…
Tuan Pham
  • 1,171
  • 2
  • 15
  • 27
1
vote
0 answers

database design, connecting common data of different entities

We have entities like Customer, Broker, Company. These have different attributes and must be on different tables. But they can have common things, in our case Contact Info (entity->contact is one to many relationship). Whats the best way to do…
patrox
  • 408
  • 3
  • 11
1
vote
2 answers

SQL, two tables, one shared primary key

I want to have two tables so I can move old records into a second table so I keep the main table small but still want to be able to link to both tables using the one int Primary Key. eg: (I have simplified this example, from what is in my real…
user802599
  • 787
  • 2
  • 12
  • 35
0
votes
2 answers

Entity framework- Adding entities (and their navigation properties) with shared primary keys

This is a simplified version of the problem I am trying to solve: There are two entities: Item ItemID (PK) Other simple properties... WorkItem (navigation property) WorkItem ItemID (PK) Other Simple Properties... Item (Navigation…
oldman_andy
  • 37
  • 6
0
votes
1 answer

Creating a master table with two child tables linking one-to-zero-or-one with EF 4.1

Using MVC EF4.1, I am trying to link a table (TableMaster) to TableChildOne (relationship one-to-zero-or-one) and also to TableChildTwo (also one-to-zero-or-one). TableChildOne and TableChildTwo are not directly linked. TablechildOne and…
David Smit
  • 829
  • 1
  • 13
  • 31
0
votes
1 answer

Using schema in a primary key sharing situation

I'm having problems with a bidirectional one-to-one mapping that share the same Id when I try to use a schema. When I don't use the schema attribute in the @Table annotation the program runs normally and creates the SERVICES(id, description) and…
Rebeca Maria Fialho Camilo
  • 35
  • 4
0
votes
1 answer

SQL Server use same Guid as primary key in 2 tables

We have 2 tables with a 1:1 relationship. 1 table should reference the other, typically one would use a FK relationship. Since there is a 1:1 relationship, we could also directly use the same Guid in both tables as primary key. Additional info: the…
Andreas Reiff
  • 7,961
  • 10
  • 50
  • 104
0
votes
1 answer

How would I use an autogenerated primary key which is also a foreign key of another table to insert data using JPA?

I have two tables. The structure is Personal table Auto Id Number, int, Primary Key | Name, Varchar | Age, int | Demographic table Auto id number, int, foreign key | Address, varchar | I've created entity classes as…
Kiran Cyrus Ken
  • 379
  • 1
  • 3
  • 17
0
votes
1 answer

How to define and use a foreign key as primary key in Laravel?

I have a Users table that can store 8 different types of users. Depending on the user type, I have some other tables with the specific data set for it. So, I would like to use the ID from the Users table as a foreign key and primary key at the same…
Janbalik
  • 574
  • 6
  • 15
0
votes
2 answers

SQL Update: Cannot change one of composite key value of first record

In MSSQL Server, I have a table StudentCourse with a Composite Primary Key (StudentID, CourseID). I am trying to change the selected student to another course. One student record of each course group is preventing me to do UPDATE…
Jonas T
  • 2,989
  • 4
  • 32
  • 43
0
votes
1 answer

Insert rows to 1:1 related tables

I want to put new rows into tables related 1:1 in sql-server. As you see, tables reference each other with the same primary key. And here is the problem- I know that putting data with two INSERT calls will throw me an error saying I violate PK…
Embid123
  • 467
  • 5
  • 17
0
votes
1 answer

NHibernate primary key is a 1-1 mapping?

Maybe I have just designed this incorrectly, and if so I am more than happy to bow out and loot at it another way... I currently have 2 tables like this, which have a shared primary key: person - id (PK), name, created_date, ... person_details -…
Grofit
  • 17,693
  • 24
  • 96
  • 176
0
votes
2 answers

Retrieve last row in mysql with shared primary key

I have a table with a primary key of 2 values - id and timestamp. The table contains multiple rows for each item_id, and the timestamp is what should make it unique. Can I execute an efficient query considering this setup? I want to find the latest…
Yuval
  • 764
  • 1
  • 9
  • 23
0
votes
1 answer

How do we handle database schema with artwork types which unique has data for each type?

artwork.jpg I am trying to develop an artwork solution but stuck on the category type issue on how to code this into a database schema. I just started to understand the concept of parent and foreign key. An artwork belongs to a category type like…
user6458835
1
2
3