locked
What is the advantage of Foreign keys in database RRS feed

  • Question

  • suppose if we do not have FK then what kind of advantage we could not avail. we can fetch data from two table by creating a relation in sql.....then why FK is required. thanks
    Wednesday, April 22, 2015 12:28 PM

Answers

  • Integrity, when you define a FK on a table you are defining a rule that mean a table has a dependency of other. With it you are forcing the each table fill the FK fields.


    Att. Andre de Mattos Ferraz

    Wednesday, April 22, 2015 12:32 PM
  • Having a foreign key ensures data integrity - that you cannot insert a row in the dependent table that does not have a matching value in the source table. Same goes for deletes and updates as well.

    For example, if you have Customer table and an Orders table, and if the CustomerID from Customer table is a foreign key into a non-nullable CustomerID column in Orders table, you won't be able to insert a record into Orders table with a CustomerID value that didn't exist in the Customers table.

    • Proposed as answer by André de Mattos Ferraz Wednesday, April 22, 2015 12:36 PM
    • Marked as answer by Mou_kolkata Wednesday, April 22, 2015 1:28 PM
    • Unmarked as answer by Mou_kolkata Wednesday, April 22, 2015 1:30 PM
    • Marked as answer by Mou_kolkata Wednesday, April 22, 2015 1:30 PM
    Wednesday, April 22, 2015 12:33 PM
  • suppose if we do not have FK then what kind of advantage we could not avail. we can fetch data from two table by creating a relation in sql.....then why FK is required. thanks

    Yes

    But nothing is stopping you from putting values in second table which is not present (invalid) in the first table. Thats where FK comes into play. Once you define FK it will ensure only the values that honor the relationship is getting inserted. You can also set additional conditions like automatically remove / blank entries in the refered table when master entry it points to is removed (cascade options)


    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page

    • Marked as answer by Mou_kolkata Wednesday, April 22, 2015 1:30 PM
    Wednesday, April 22, 2015 12:47 PM
  • Hi Mou_kolkata

    Overall foreign keys are very useful for maintaining data integrity.

    A nice article written by  at mssqltips.com explains about importance of FKs.

    Advantages and disadvantages of using Foreign keys was also discussed under the design section of msdn forums at.

    SQL Server > Database Design.

    Hope they are helpful.



    Thanks and Regards, Ibrahim Mehdi. MCSE-DataPlatform


    • Edited by Ibrahim Mehdi Wednesday, April 22, 2015 12:59 PM
    • Proposed as answer by Ibrahim Mehdi Wednesday, April 22, 2015 1:00 PM
    • Marked as answer by Mou_kolkata Wednesday, April 22, 2015 1:30 PM
    Wednesday, April 22, 2015 12:59 PM
  • Yes, you can have a nullable column (assuming what you meant is like in the following example)
    CREATE TABLE A(id INT NOT NULL PRIMARY KEY);
    GO
    
    CREATE TABLE B( id INT NULL REFERENCES a(id));
    GO
    
    INSERT INTO a VALUES (1),(2);
    
    INSERT INTO b VALUES (1),(2); -- OK
    INSERT INTO b VALUES (NULL);  -- OK;

    • Marked as answer by Mou_kolkata Wednesday, April 22, 2015 8:26 PM
    Wednesday, April 22, 2015 1:36 PM

All replies

  • Integrity, when you define a FK on a table you are defining a rule that mean a table has a dependency of other. With it you are forcing the each table fill the FK fields.


    Att. Andre de Mattos Ferraz

    Wednesday, April 22, 2015 12:32 PM
  • Having a foreign key ensures data integrity - that you cannot insert a row in the dependent table that does not have a matching value in the source table. Same goes for deletes and updates as well.

    For example, if you have Customer table and an Orders table, and if the CustomerID from Customer table is a foreign key into a non-nullable CustomerID column in Orders table, you won't be able to insert a record into Orders table with a CustomerID value that didn't exist in the Customers table.

    • Proposed as answer by André de Mattos Ferraz Wednesday, April 22, 2015 12:36 PM
    • Marked as answer by Mou_kolkata Wednesday, April 22, 2015 1:28 PM
    • Unmarked as answer by Mou_kolkata Wednesday, April 22, 2015 1:30 PM
    • Marked as answer by Mou_kolkata Wednesday, April 22, 2015 1:30 PM
    Wednesday, April 22, 2015 12:33 PM
  • suppose if we do not have FK then what kind of advantage we could not avail. we can fetch data from two table by creating a relation in sql.....then why FK is required. thanks

    Yes

    But nothing is stopping you from putting values in second table which is not present (invalid) in the first table. Thats where FK comes into play. Once you define FK it will ensure only the values that honor the relationship is getting inserted. You can also set additional conditions like automatically remove / blank entries in the refered table when master entry it points to is removed (cascade options)


    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page

    • Marked as answer by Mou_kolkata Wednesday, April 22, 2015 1:30 PM
    Wednesday, April 22, 2015 12:47 PM
  • Hi Mou_kolkata

    Overall foreign keys are very useful for maintaining data integrity.

    A nice article written by  at mssqltips.com explains about importance of FKs.

    Advantages and disadvantages of using Foreign keys was also discussed under the design section of msdn forums at.

    SQL Server > Database Design.

    Hope they are helpful.



    Thanks and Regards, Ibrahim Mehdi. MCSE-DataPlatform


    • Edited by Ibrahim Mehdi Wednesday, April 22, 2015 12:59 PM
    • Proposed as answer by Ibrahim Mehdi Wednesday, April 22, 2015 1:00 PM
    • Marked as answer by Mou_kolkata Wednesday, April 22, 2015 1:30 PM
    Wednesday, April 22, 2015 12:59 PM
  • now PK value could be null now i means null is allowed in FK field from SQL Server 2005? am i right?
    Wednesday, April 22, 2015 1:30 PM
  • Yes, you can have a nullable column (assuming what you meant is like in the following example)
    CREATE TABLE A(id INT NOT NULL PRIMARY KEY);
    GO
    
    CREATE TABLE B( id INT NULL REFERENCES a(id));
    GO
    
    INSERT INTO a VALUES (1),(2);
    
    INSERT INTO b VALUES (1),(2); -- OK
    INSERT INTO b VALUES (NULL);  -- OK;

    • Marked as answer by Mou_kolkata Wednesday, April 22, 2015 8:26 PM
    Wednesday, April 22, 2015 1:36 PM