Can I have a one to many relationship between a primary key in one table and a primary key in a second table?

Question

Whenever i try to create a one to many relationship between two tables, i realized that it is only working if i have the relationship between a primary key in table one an element which is not a primary key in table two. If it has a primary key then it automatically changes to a one to one relationship

Answer 1

Can I have a one to many relationship between a primary key in one table and a primary key in a second table?

No, because the Primary Key values in each table must be unique so there is no way that you could have multiple values of the same Primary Key in either table. We can (and usually do) establish a one-to-many relationship between the Primary Key in one table (the "parent table") and a Foreign Key in the other ("child") table, where the Foreign Key in the child table holds the Primary Key value from the parent table.

Example:

[tblCustomers]

CustomerID  CompanyName
----------  -----------
         1  Company1
         2  Company2

[tblInvoices]

InvoiceID  CustomerID  InvoiceDate
---------  ----------  -----------
        1           1  2014-07-21
        2           1  2014-08-25
        3           2  2014-08-28

We establish the one-to-many relationship between tblCustomers.CustomerID (the Primary Key in the [tblCustomers] "parent" table) and tblInvoices.CustomerID (the Foreign Key in the [tblInvoices] "child" table). tblInvoices.CustomerID is not unique because there can be many invoices for the same customer.

Note that if you were to try and establish a relationship between tblCustomers.CustomerID (the Primary Key in the [tblCustomers] "parent" table) and tblInvoices.InvoiceID (the Primary Key in the [tblInvoices] "child" table) then that relationship would have to be a one-to-one relationship. It would also be meaningless.