locked
Does a foreign key have to be a primary key RRS feed

  • Question

  • Hey all.I was checking on the database code written by sambapos.To my surprise, I found a foreign key that is not a primary key anywhere.

    Is that possible?

    If, so why?

    I am really astonished.

    Wednesday, July 30, 2014 10:43 AM

Answers

  • Limitations and Restrictions

    • A foreign key constraint does not have to be linked only to a primary key constraint in another table; it can also be defined to reference the columns of a UNIQUE constraint in another table.

    • When a value other than NULL is entered into the column of a FOREIGN KEY constraint, the value must exist in the referenced column; otherwise, a foreign key violation error message is returned. To make sure that all values of a composite foreign key constraint are verified, specify NOT NULL on all the participating columns.

    • FOREIGN KEY constraints can reference only tables within the same database on the same server. Cross-database referential integrity must be implemented through triggers. For more information, see CREATE TRIGGER (Transact-SQL).

    • FOREIGN KEY constraints can reference another column in the same table. This is referred to as a self-reference.

    • A FOREIGN KEY constraint specified at the column level can list only one reference column. This column must have the same data type as the column on which the constraint is defined.

    • A FOREIGN KEY constraint specified at the table level must have the same number of reference columns as the number of columns in the constraint column list. The data type of each reference column must also be the same as the corresponding column in the column list.

    • The Database Engine does not have a predefined limit on either the number of FOREIGN KEY constraints a table can contain that reference other tables, or the number of FOREIGN KEY constraints that are owned by other tables that reference a specific table. Nevertheless, the actual number of FOREIGN KEY constraints that can be used is limited by the hardware configuration and by the design of the database and application. We recommend that a table contain no more than 253 FOREIGN KEY constraints, and that it be referenced by no more than 253 FOREIGN KEY constraints.

    • FOREIGN KEY constraints are not enforced on temporary tables.

    • If a foreign key is defined on a CLR user-defined type column, the implementation of the type must support binary ordering. For more information, see CLR User-Defined Types.

    • A column of type varchar(max) can participate in a FOREIGN KEY constraint only if the primary key it references is also defined as type varchar(max).

    Read this article http://msdn.microsoft.com/en-us/library/ms189049.aspx


    Regards, Ashwin Menon My Blog - http:\\sqllearnings.com

    • Proposed as answer by SaravanaC Wednesday, July 30, 2014 10:50 AM
    • Marked as answer by tracycai Thursday, August 7, 2014 1:36 PM
    Wednesday, July 30, 2014 10:46 AM
  • Its enough to be a unique key. Not necessarily a primary key

    Regards, Ashwin Menon My Blog - http:\\sqllearnings.com

    • Proposed as answer by tracycai Thursday, July 31, 2014 4:30 AM
    • Marked as answer by tracycai Thursday, August 7, 2014 1:36 PM
    Wednesday, July 30, 2014 11:22 AM
  • You ask questions that only the original database designer can answer.  Ashwin gives you the theory and the functionality - which is documented in BOL BTW.  THe decision to add this column to "some tables" and NOT make it a foreign key might have been a mistake - or it might have been intentionally (even in based on a mis-understanding). 

    It is unusual to see a foreign key that does not reference a primary key. Your questions do not inspire much confidence in the skills of the original designer.  A lack of consistent constraint usage also casts doubts on the consistency of your data - something you may want to verify.

    • Proposed as answer by tracycai Thursday, July 31, 2014 4:30 AM
    • Marked as answer by tracycai Thursday, August 7, 2014 1:36 PM
    Wednesday, July 30, 2014 12:38 PM

All replies

  • Hey.I have to develop a restaurant POS system.So I decided to view open source ones to get an idea.

    I opened a .sdf file, and I found an attribute, which is foreign keys in tables, but is not a primary key i any table.Is that possible.When I run it it executes with out errors.

    Is that really possible.

    • Merged by tracycai Thursday, July 31, 2014 4:25 AM same question
    Wednesday, July 30, 2014 10:30 AM
  • Limitations and Restrictions

    • A foreign key constraint does not have to be linked only to a primary key constraint in another table; it can also be defined to reference the columns of a UNIQUE constraint in another table.

    • When a value other than NULL is entered into the column of a FOREIGN KEY constraint, the value must exist in the referenced column; otherwise, a foreign key violation error message is returned. To make sure that all values of a composite foreign key constraint are verified, specify NOT NULL on all the participating columns.

    • FOREIGN KEY constraints can reference only tables within the same database on the same server. Cross-database referential integrity must be implemented through triggers. For more information, see CREATE TRIGGER (Transact-SQL).

    • FOREIGN KEY constraints can reference another column in the same table. This is referred to as a self-reference.

    • A FOREIGN KEY constraint specified at the column level can list only one reference column. This column must have the same data type as the column on which the constraint is defined.

    • A FOREIGN KEY constraint specified at the table level must have the same number of reference columns as the number of columns in the constraint column list. The data type of each reference column must also be the same as the corresponding column in the column list.

    • The Database Engine does not have a predefined limit on either the number of FOREIGN KEY constraints a table can contain that reference other tables, or the number of FOREIGN KEY constraints that are owned by other tables that reference a specific table. Nevertheless, the actual number of FOREIGN KEY constraints that can be used is limited by the hardware configuration and by the design of the database and application. We recommend that a table contain no more than 253 FOREIGN KEY constraints, and that it be referenced by no more than 253 FOREIGN KEY constraints.

    • FOREIGN KEY constraints are not enforced on temporary tables.

    • If a foreign key is defined on a CLR user-defined type column, the implementation of the type must support binary ordering. For more information, see CLR User-Defined Types.

    • A column of type varchar(max) can participate in a FOREIGN KEY constraint only if the primary key it references is also defined as type varchar(max).

    Read this article http://msdn.microsoft.com/en-us/library/ms189049.aspx


    Regards, Ashwin Menon My Blog - http:\\sqllearnings.com

    • Proposed as answer by SaravanaC Wednesday, July 30, 2014 10:50 AM
    • Marked as answer by tracycai Thursday, August 7, 2014 1:36 PM
    Wednesday, July 30, 2014 10:46 AM
  • Great Ashwin. Educated myself with something I did not know at all.But it gets weirder. The said foreign key "menuItemid", is used in 5 tables.On two tables, it is not a foreign key, on the other three it is.And yet it is not a primary key.I don't understand, how can that happen.


    Wednesday, July 30, 2014 11:02 AM
  • Setting a foreign key is upto the person who designed the database or the dba/developer.

    If you dont feel that you benefit in enforcing a relationship between two columns then you dont have to.

    Sometimes, the constraint will be enforced from the application itself or dba/developer/architect decided that adding another constraint is an overkill . Its completely upto the person who designs whether he needs to enforce a constraint.


    Regards, Ashwin Menon My Blog - http:\\sqllearnings.com


    Wednesday, July 30, 2014 11:07 AM
  • So, you are saying that its possible, to have an attribute which is a  foreign key in some tables and in some tables it is ordinary attribute.The very same attribute is not a primary key anywhere?
    Wednesday, July 30, 2014 11:13 AM
  • Its enough to be a unique key. Not necessarily a primary key

    Regards, Ashwin Menon My Blog - http:\\sqllearnings.com

    • Proposed as answer by tracycai Thursday, July 31, 2014 4:30 AM
    • Marked as answer by tracycai Thursday, August 7, 2014 1:36 PM
    Wednesday, July 30, 2014 11:22 AM
  • You ask questions that only the original database designer can answer.  Ashwin gives you the theory and the functionality - which is documented in BOL BTW.  THe decision to add this column to "some tables" and NOT make it a foreign key might have been a mistake - or it might have been intentionally (even in based on a mis-understanding). 

    It is unusual to see a foreign key that does not reference a primary key. Your questions do not inspire much confidence in the skills of the original designer.  A lack of consistent constraint usage also casts doubts on the consistency of your data - something you may want to verify.

    • Proposed as answer by tracycai Thursday, July 31, 2014 4:30 AM
    • Marked as answer by tracycai Thursday, August 7, 2014 1:36 PM
    Wednesday, July 30, 2014 12:38 PM
  • Hello ,
    Please see below information :

    1. Foreign key (FK) is a column or combination of columns that is used to establish and enforce a link between the data in two tables

    2. Link is created between two tables when the column or columns that hold the primary key value for one table are referenced by the column or columns in another table. This column becomes a foreign key in the second table.

    3.A FOREIGN KEY constraint can reference columns in tables in the same database or within the same table.

    4.Purpose of a FOREIGN KEY constraint is to control the data that can be stored in the foreign key table, it also controls changes to data in the primary key table

    Hope you can understand what you should do.


    Ahsan Kabir Please remember to click Mark as Answer and Vote as Helpful on posts that help you. This can be beneficial to other community members reading the thread. http://www.aktechforum.blogspot.com/

    Thursday, August 7, 2014 6:12 AM