locked
foreign keys question RRS feed

  • Question

  • Hello, I am still new at SQL... As I was creating a database I noticed that some databases set up foreign keys during the creation portion.

     

    CREATE

    TABLE USERS (

     user_id SMALLINT NOT NULL identity (1,1),

     

    user_name

    VARCHAR(50),

     department_id SMALLINT NOT NULL ,

     

    FOREIGN

    KEY (department_ID) references DEPARTMENTS,
     

     

     

    primary

    key (user_id )

    )

    Why should you define a foreign Key during creation? When you are querying you can link the tables together without defining the foreign keys at design without any errors.

    select

     

    department_name,

    from

    user_name

     departments cross join users

    -- No errors - with this table the foreign key was not defined in the design of the table, and there were no errors.

    If this is the case what is the point of defineing the foreign key

     

     

     

     


    Bryan Revelant
    Friday, January 6, 2012 2:58 PM

Answers

  • The primary key and foreign keys are fundamental concepts for the relational database theory. You may want to educate yourself a little bit on this topic

    http://en.wikipedia.org/wiki/Foreign_key


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog
    • Marked as answer by Kalman Toth Friday, January 6, 2012 5:12 PM
    Friday, January 6, 2012 3:03 PM
  • Bryan,

    If you do a CROSS JOIN, yes, sure, you don't need a foreign key.

    The CROSS JOIN will simply return all possible combinations of DEPARTMENTS and USERS.

    But if you need to know which USER are in which DEPARTMENTS, then you need a foreign key.



    • Edited by Sygrien Friday, January 6, 2012 3:06 PM
    • Proposed as answer by Naomi N Friday, January 6, 2012 3:06 PM
    • Marked as answer by Kalman Toth Friday, January 6, 2012 5:12 PM
    Friday, January 6, 2012 3:05 PM
  • I am not sure i got your Question completely.

    Foreign Keys are defined to have data integrity(referential integrity) in the system.

    It does not matter if you define foreign key during the creation of table or after creating the table.the purpose is same.

    In your case you are defining a foreign key on USERS.Department_ID Column to refer Departments table,By that you are ensuring any value that you are going to insert in USERS.Department_ID column is already existing in department table.you can not insert a value that does not exists in department table.

    So the set of values that you can insert in USERS.Department_ID are always a subset of Department.Department_ID set.

    when you select data, it's up to the user to select what he wants.Foreign keys are not defined to restrict the way selects are written.


    Vinay Valeti| If you think my suggestion is useful, please rate it as helpful. If it has helped you to resolve the problem, please Mark it as Answer
    • Marked as answer by Bryan Rev Friday, January 6, 2012 3:33 PM
    Friday, January 6, 2012 3:11 PM
  • Hello Bryan. 

                          You are completely misunderstanding the concept. Yes above query will not give any error because there is nothing wrong in this. For example you have a user table where unique usernames are present and another table referncing those users.

    Now if you have created foreign key in that case you can not insert a user in second table without inserting it in first and can not delete a user in first table without deleting all entries in second. That is the main purpose of this constraints.

    Otherwise there will be entries in second table which are different /not present in first(master) table for users.

    read the link below

    http://www.w3schools.com/sql/sql_foreignkey.asp


    Want to add MVP with my name.
    • Marked as answer by Kalman Toth Friday, January 6, 2012 5:12 PM
    Friday, January 6, 2012 3:13 PM
  • The purpose of defining foreign keys is to disallow incorrect data to be entered to the database. Say that you have a table countries, where the key is the country code according to ISO 3166. Furthermore, say that the table includes the country that your organisation makes business in.

    Say now that you enter a customer, and on a whim you specify the country code BV as the country where the customer is living. BV is not in your countries table (it's an island in the South Ocean, not too far from Antartica and has no steadfast population). If you have defined a foreign key on Customers.CountryCode, you will get an error, and those protecting you from entering bad data in the database.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    • Marked as answer by Kalman Toth Friday, January 6, 2012 5:12 PM
    Friday, January 6, 2012 3:46 PM

All replies

  • The primary key and foreign keys are fundamental concepts for the relational database theory. You may want to educate yourself a little bit on this topic

    http://en.wikipedia.org/wiki/Foreign_key


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog
    • Marked as answer by Kalman Toth Friday, January 6, 2012 5:12 PM
    Friday, January 6, 2012 3:03 PM
  • Bryan,

    If you do a CROSS JOIN, yes, sure, you don't need a foreign key.

    The CROSS JOIN will simply return all possible combinations of DEPARTMENTS and USERS.

    But if you need to know which USER are in which DEPARTMENTS, then you need a foreign key.



    • Edited by Sygrien Friday, January 6, 2012 3:06 PM
    • Proposed as answer by Naomi N Friday, January 6, 2012 3:06 PM
    • Marked as answer by Kalman Toth Friday, January 6, 2012 5:12 PM
    Friday, January 6, 2012 3:05 PM
  • I am not sure i got your Question completely.

    Foreign Keys are defined to have data integrity(referential integrity) in the system.

    It does not matter if you define foreign key during the creation of table or after creating the table.the purpose is same.

    In your case you are defining a foreign key on USERS.Department_ID Column to refer Departments table,By that you are ensuring any value that you are going to insert in USERS.Department_ID column is already existing in department table.you can not insert a value that does not exists in department table.

    So the set of values that you can insert in USERS.Department_ID are always a subset of Department.Department_ID set.

    when you select data, it's up to the user to select what he wants.Foreign keys are not defined to restrict the way selects are written.


    Vinay Valeti| If you think my suggestion is useful, please rate it as helpful. If it has helped you to resolve the problem, please Mark it as Answer
    • Marked as answer by Bryan Rev Friday, January 6, 2012 3:33 PM
    Friday, January 6, 2012 3:11 PM
  • Hello Bryan. 

                          You are completely misunderstanding the concept. Yes above query will not give any error because there is nothing wrong in this. For example you have a user table where unique usernames are present and another table referncing those users.

    Now if you have created foreign key in that case you can not insert a user in second table without inserting it in first and can not delete a user in first table without deleting all entries in second. That is the main purpose of this constraints.

    Otherwise there will be entries in second table which are different /not present in first(master) table for users.

    read the link below

    http://www.w3schools.com/sql/sql_foreignkey.asp


    Want to add MVP with my name.
    • Marked as answer by Kalman Toth Friday, January 6, 2012 5:12 PM
    Friday, January 6, 2012 3:13 PM
  • Create

     

    table departments

    (

    department_id

     

    int identity(1,1)

    Dptname

     

    varchar(50) not

    null

    primary

     

    key (department_id

    )

     

    CREATE

     

    TABLE USERS ( user_id SMALLINT NOT NULL identity (1,1),

    user_name

     

    VARCHAR(50),

    department_id

     

    SMALLINT NOT NULL

     

    primary

     

    key (user_id )

    )

    I can understand that however if you place department_id in the department table and department_id in the users table - Then you can join all of the tables without defining the foreign key in the table design. So I am still left wondering what the point of writting out 

    FOREIGN

    KEY (department_ID) references DEPARTMENTS,
     

     

     


    Bryan Revelant
    Friday, January 6, 2012 3:13 PM
  • The purpose of defining foreign keys is to disallow incorrect data to be entered to the database. Say that you have a table countries, where the key is the country code according to ISO 3166. Furthermore, say that the table includes the country that your organisation makes business in.

    Say now that you enter a customer, and on a whim you specify the country code BV as the country where the customer is living. BV is not in your countries table (it's an island in the South Ocean, not too far from Antartica and has no steadfast population). If you have defined a foreign key on Customers.CountryCode, you will get an error, and those protecting you from entering bad data in the database.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    • Marked as answer by Kalman Toth Friday, January 6, 2012 5:12 PM
    Friday, January 6, 2012 3:46 PM