How can I share the same primary key across two tables?

Question

I'm reading a book on EF4 and I came across this problem situation:

enter image description here

So I was wondering how to create this database so I can follow along with the example in the book.

How would I create these tables, using simple TSQL commands? Forget about creating the database, imagine it already exists.

@Jeff: The book doesn't show the TSQL, it assumes this database already exists, hence the question. — Only Bolivian Here, May 18 '11 at 15:00
Have you tried a different section of the book that does explain how to create a database? You accepted an answer that doesn't have any code to create a database. — JeffO, May 19 '11 at 14:04

HLGEM · Answer 1 · 2016-12-01T21:05:06.897

You've been given the code. I want to share some information on why you might want to have two tables in a relationship like that.

First when two tables have the same Primary Key and have a foreign key relationship, that means they have a one-to-one relationship. So why not just put them in the same table? There are several reasons why you might split some information out to a separate table.

First the information is conceptually separate. If the information contained in the second table relates to a separate specific concern, it makes it easier to work with it the data is in a separate table. For instance in your example they have separated out images even though they only intend to have one record per SKU. This gives you the flexibility to easily change the table later to a one-many relationship if you decide you need multiple images. It also means that when you query just for images you don't have to actually hit the other (perhaps significantly larger) table.

Which bring us to reason two to do this. You currently have a one-one relationship but you know that a future release is already scheduled to turn that to a one-many relationship. In this case it's easier to design into a separate table, so that you won't break all your code when you move to that structure. If I were planning to do this I would go ahead and create a surrogate key as the PK and create a unique index on the FK. This way when you go to the one-many relationship, all you have to do is drop the unique index and replace it with a regular index.

Another reason to separate out a one-one relationship is if the table is getting too wide. Sometimes you just have too much information about an entity to easily fit it in the maximum size a record can have. In this case, you tend to take the least used fields (or those that conceptually fit together) and move them to a separate table.

Another reason to separate them out is that although you have a one-one relationship, you may not need a record of what is in the child table for most records in the parent table. So rather than having a lot of null values in the parent table, you split it out.

The code shown by the others assumes a character-based PK. If you want a relationship of this sort when you have an auto-generating Int or GUID, you need to do the autogeneration only on the parent table. Then you store that value in the child table rather than generating a new one on that table.

Thanks for taking the time to write this up, very good read. :) — Only Bolivian Here, May 18 '11 at 15:28

Jaymz · Accepted Answer · 2011-05-18T15:10:46.123

~~When it says the tables share the same primary key, it just means that there is a field with the same name in each table, both set as Primary Keys.~~

Create Tables

CREATE TABLE [Product (Chapter 2)](
    SKU varchar(50) NOT NULL,
    Description varchar(50) NULL,
    Price numeric(18, 2) NULL,
    CONSTRAINT [PK_Product (Chapter 2)] PRIMARY KEY CLUSTERED 
    (
        SKU ASC
    )
)

CREATE TABLE [ProductWebInfo (Chapter 2)](
    SKU varchar(50) NOT NULL,
    ImageURL varchar(50) NULL,
    CONSTRAINT [PK_ProductWebInfo (Chapter 2)] PRIMARY KEY CLUSTERED 
    (
        SKU ASC
    )
)

Create Relationships

ALTER TABLE [ProductWebInfo (Chapter 2)] 
    ADD CONSTRAINT fk_SKU 
    FOREIGN KEY(SKU)
REFERENCES [Product (Chapter 2)] (SKU)

It may look a bit simpler if the table names are just single words (and not key words, either), for example, if the table names were just Product and ProductWebInfo, without the (Chapter 2) appended:

ALTER TABLE ProductWebInfo
    ADD CONSTRAINT fk_SKU
    FOREIGN KEY(SKU)
REFERENCES Product(SKU)

You're first sentence is misleading. It has nothing to do with the column name and everything to do with the relationship which you correctly note later in your answer — Michael Haren, May 18 '11 at 15:03
You're right, my terminology is a bit rusty. False assumption on my part. — Jaymz, May 18 '11 at 15:06

Bobby D · Answer 3 · 2011-05-18T14:59:01.087

1

This simply an example that I threw together using the table designer in SSMS, but should give you an idea (note the foreign key constraint at the end):

CREATE TABLE dbo.Product
    (
    SKU int NOT NULL IDENTITY (1, 1),
    Description varchar(50) NOT NULL,
    Price numeric(18, 2) NOT NULL
    )  ON [PRIMARY]

ALTER TABLE dbo.Product ADD CONSTRAINT
    PK_Product PRIMARY KEY CLUSTERED 
    (
    SKU
    )

CREATE TABLE dbo.ProductWebInfo
    (
    SKU int NOT NULL,
    ImageUrl varchar(50) NULL
    )  ON [PRIMARY]

ALTER TABLE dbo.ProductWebInfo ADD CONSTRAINT
    FK_ProductWebInfo_Product FOREIGN KEY
    (
    SKU
    ) REFERENCES dbo.Product
    (
    SKU
    ) ON UPDATE  NO ACTION 
     ON DELETE  NO ACTION

edited May 18 '11 at 14:59

answered May 18 '11 at 14:48

Bobby D

2,129
14
21

That seems a bit over complicated for this simple example. Care dumbing it down to the bare essentials? – Only Bolivian Here May 18 '11 at 14:51
It's about as bare-bones as you can get. The only thing really missing is the `CREATE DATABASE` statement at the top. Logically, what we are doing is creating a parent table (`Product`) and a child table (`ProductWebInfo`). We are then creating a foreign key from the primary key of the child to the parent. – Bobby D May 18 '11 at 14:55
it might help a little to remove the `WITH(...)`, `ON [...]`, `dbo.` and `GO` pieces – Michael Haren May 18 '11 at 14:57
@Michael agreed. I was debating the value of keeping those around and initially decided to go with the default output of the designer so it would not look confusing if the user used the designer later. Took them out, though, for readability. Thanks for the tip! – Bobby D May 18 '11 at 14:59

score 0 · Answer 4 · answered May 18 '11 at 14:53

See how to create a foreign key constraint. http://msdn.microsoft.com/en-us/library/ms175464.aspx This also has links to creating tables. You'll need to create the database as well.

To answer your question:

ALTER TABLE ProductWebInfo
ADD CONSTRAINT fk_SKU
FOREIGN KEY (SKU)
REFERENCES Product(SKU)

How can I share the same primary key across two tables?

4 Answers4

Create Tables

Create Relationships

Linked