Is it necessary to have a primary key in the foriegn table?

Question

            Table one
                ID | product_code | product name | company_id 
                1  |  12345       | beer cake    |343434defee
                2  |  12346       | vodka cake   |343434deereee

            Table two
                   Product_code |Quantity | price | weight 
                   12345        |  34     |345    |0.5 
                   12345        |  343    |600    |1.0 
                   12345        |  4      |845    |1.5 
                   12346        |  341    |345    |0.5 


         CREATE TABLE `one`(
         ID INT  NOT NULL AUTO_INCREMENT PRIMARY KEY,
         product_code VARCHAR(32) NOT NULL ,
         name VARCHAR(30) NOT NULL ,
         company_id VARCHAR(30) NOT NULL)

         CREATE TABLE two(
         product_code VARCHAR(32) ,
         weight VARCHAR(20) NOT NULL ,
         price  INT(4) NOT NULL , 
         Quantity INT(4) NOT NULL ,
         FOREIGN KEY (product_code) REFERENCES one(product_code))

This is what my table looks like, each type of cake has to be displayed on the product landing page.The relationship between two tables is given by the column product_code.

Is it necessary to have a primary key in the foreign table?

Please, Show me a proper schema creation for these ?

It is not necessary but it will be useful when you query the table. How will you uniquely identify a record in table `two` ?. I will probably create `composite primary key` on table `two` instead of `Identity` — Pரதீப், Oct 08 '16 at 16:59
Table `two` looks like a transaction table. How will you keep track and pull individual transactions? — drum, Oct 08 '16 at 17:01
If your question is "Is it necessary to have a primary key in the foreign table?" then the answer is no. But an adequate key, not even unique, must be present for a fast lookup related to first-most or left-most (especially for composite keys)... for validity. Naturally PK's are highly suggested in any table, but they don't need to be related to FK enforcement. If your question is someone show me a proper schema creation for this, then ask that question. — Drew, Oct 08 '16 at 18:02
What is fine to me is output on par with **Section2 / What does Show your Schema Mean?** of [What is Sqlfiddle and why should I care?](http://stackoverflow.com/a/38899465) to help us to help you. Let me show a visual of where in this [Image](http://i.imgur.com/IrrZKf0.jpg) of it. Your data visual is nice but not really helpful alone. It is a nice talking point *in addition to* the above. — Drew, Oct 08 '16 at 18:13
Then people can easily cut and paste and suggest differing `create table` blocks for you. — Drew, Oct 08 '16 at 18:14
Perfect, thx. Downvote flipped. :p ... I will ping the other guys and let them know in their answers. So with those two guys answering, you don't need me. If you do, I am in [Campaigns](http://chat.stackoverflow.com/rooms/95290) chat. Thx for doing what 90% of people tend not to do: improve their questions and seem to care greatly — Drew, Oct 08 '16 at 18:59
In a DBMS following the Relational Model *every* relation needs a Unique Key. In one of your comments you mentioned that *product_code and weight can't be same for any item*, so this is your Primary Key for table two. — dnoeth, Oct 08 '16 at 21:53

score 1 · Answer 1 · answered Oct 08 '16 at 17:08

1

Your data structure is set up wrong. This is your first table:

ID | product_code | product name | company_id 
1  |  12345       | beer cake    |343434defee
2  |  12346       | vodka cake   |343434deereee

This is fine. ID is -- presumably -- a unique id for each row. It should be declared as a primary key. product_code should be declared unique.

The second table should contain id, not product_code. Product_code is an attribute on each row of the first table. You have the id to refer to the row.

answered Oct 08 '16 at 17:08

Gordon Linoff

1,242,037
58
646
786

Product code is unique and no two items can have the same product code , even then do i need to change it to id? – Tilak Raj Oct 08 '16 at 17:14
@BOTJr. . . . You could use it as the primary key, but I'm in favor of synthetic primary keys. Your foreign key relationship should refer to the primary key of the reference table. – Gordon Linoff Oct 08 '16 at 17:15
I have been using product codes to make any queries regarding the products.Suppose if i were to reference the second table , i could do `product_code` and `weight` because they both can't be same for any item but if i were to use id instead of product code in second table, i have to first call the first table , get the ID corresponding to that product code and then refer the second table. – Tilak Raj Oct 08 '16 at 17:18
@BOTJr. . . . That is how relational databases work. Separate the data that belongs together in one entity table and then use `JOIN` to bring together the data when you need it. – Gordon Linoff Oct 08 '16 at 17:38
1

@GordonLinoff the OP has done an update if it is germane to anything here. – Drew Oct 08 '16 at 19:01

score 1 · Answer 2 · edited May 23 '17 at 12:00

It is possible, if your FK points to a column that has a unique constraint - check here for details.

Before doing this, you should clarify your design:

1) Provide meaningful names for your tables. E.g. Table 1 - Product, Table 2 - ProductTransaction

2) Create a primary key for table ProductTransaction: a ProductTransactionId column should be fine for most usage scenarios

3) Create a foreign key between ProductTransaction and Product tables. Although, you can use product_code column, I would recommend to normalize your definitions:

a) remove product_code from ProductTransaction b) add ProductId and define a foreign key that points to Product.ID

This way, you will store less data (just some integers instead of strings) and joins between the tables will be faster.

Is it necessary to have a primary key in the foriegn table?

2 Answers2