CODEWITHSUNDEEP
mysqlsql
Tilak Raj
Tilak Raj

Reputation: 1499

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

            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 ?

Upvotes: 2

Views: 69

Answers (2)

Alexei - check Codidact
Alexei - check Codidact

Reputation: 23078

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.

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269563

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.

Upvotes: 1

Related Questions