Post reply

Using a foreign Key as the PrimaryKey

  • May 25, 2007 at 10:14 am

    #172522

    Hi All,

    I wanted the expert opinion out there in the use of foreign keys as primary keys in a table.  I am not very good at explaining this concept, but I am going to try - 

     Let us say you have a parent/master table( Ex: purchase order) that is generating number (primary key for the main table)using the seed and increment specified.  We need all the records of this table to be in sequential order - i.e. we need all purchase orders to be in sequence.  Now there are two different types purchase orders different enough to have entity/tables of their own.  So what are the downsides of using the  primary key generated in the main table which would normally be a foreign key to the child table, as the actual primary key in the child tables.

     

    Thanks

    KR

  • May 25, 2007 at 11:40 am

    #709051

    There is nothing wrong with it.  It is unusal for the FK to be the PK because that means you have a 1 to 1 relationship with your parent table.  While unusual it's not unheard of and perfectly acceptable.

     

    James.

  • May 25, 2007 at 12:36 pm

    #709069

    Good!  Thanks for the input

     

    KR

  • July 18, 2007 at 7:06 am

    #720341

    Usually when people speak about master-child relationship, they mean one-to-many. For example, one purchase order contains many line items. Obviously, in this case you couldn't use the PONumber as the PK for the line items table. However, there situations when the child records display one-to-(zero or one) relationship. Consider, say, a typical employee-department database where all employees have a salary but the ones in the sales department don't have a salary but have commissions. One could argue that the proper design for this will be:

    Department (ID, Name)

    Employee(ID, Name, DepartmentID)

    SalariedEmployees(EmployeeID, Salary)

    SalesEmployees(EmployeeID, Commissions)

    In this case, every record in Employee corresponds to one record in SalariedEmployees OR to one record in SalesEmployees. There's nothing wrong with using the EmployeeID column as the primary key in the child tables.

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply