Results 1 to 5 of 5
  1. #1
    Join Date
    Sep 2011
    Posts
    2

    Unanswered: Can a column act as both primary key and foreign key, if so how

    Hi all

    Can a column act as both primary key and foreign key, if so what is script.

    And can a table have more than one primary keys/foreign keys, if so what is the script.

    i really appreciate your help.

    thank you.

  2. #2
    Join Date
    Feb 2004
    Location
    Bangalore, India
    Posts
    250
    a table can hav only one primary key, but more than one foreign keys.

    and yes, a column act as a FK and PK
    Cheers....

    baburajv

  3. #3
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    a table can have only one primary key but it may consist of multiple columns
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  4. #4
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    Can a column act as both primary key and foreign key, if so what is script.
    The only situation that I know of where a single column is both primary key and foreign key, is with supertype/subtype tables: Like in the supertype PERSON table and the subtype EMPLOYEE table.

    The PK PersonId in the EMPLOYEE table is both PK and a FK to the PERSON table. In the PERSON table, PersonId is the PK.

    In many cases with composite PK's (a PK consisting of more than one column) one of the columns is a FK to another table.
    For example, in the table with the history of addresses:
    Code:
    CREATE TABLE AddressHist (
      PersonId  INT  NOT NULL,
      StartDate  DATE NOT NULL,
      Streetname ....
      ...
      CONSTRAINT AddressHist_Person FOREIGN KEY (PersonId) REFERENCES Person (Id),
      CONSTRAINT PK_AddressHist PRIMARY KEY (PersonId, StartDate)
    )
    Sometimes all the columns of the composite PK are FK's. Consider the cross table to construct a many-to-many relation, like a shop can offer many products and a product can be sold in many shops:
    Code:
    CREATE TABLE Product_Shop (
      Product  BIGINT  NOT NULL,
      ShopId  BIGINT NOT NULL,
      ...
      CONSTRAINT Product_Shop_Product FOREIGN KEY (ProductId) REFERENCES Product (Id),
      CONSTRAINT Product_Shop_Shop FOREIGN KEY (ShopId) REFERENCES Shop (Id),
      CONSTRAINT PK_Product_Shop PRIMARY KEY (ProductId, ShopId)
    )

    And can a table have more than one primary keys/foreign keys, if so what is the script.
    And can a table have more than one primary keys: see the other answers on this thread.

    And can a table have more than one foreign keys: Yes. Product_Shop is such an example.
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

  5. #5
    Join Date
    Dec 2011
    Posts
    1

    Yes

    Yes.

    In fact, it is a fundamental step for vertical fragmentation of database table with large number of columns.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •