What is FOREIGN KEY? Easy Lesson
Wall Script
Follow Me:
Saturday, September 20, 2008

What is FOREIGN KEY? Easy Lesson

What's in a Database?

a database is a container that holds tables and other SQL structures related to those tables.


An easier way to diagram accout table.


Every one having different hobbies. Remove the hobbiles column and put it in its own table


Add name colum that will let us identify which hobbies belong to which person in the account table.



Linking two tables in a diagram

Connecting Two tables

The problem we're trying to use name field to somehow let use connect the two tables. But what if two people in the accout table have the same name ?



Foreing Key Facts :

A foreign key can have a different name that the primary key it comes form.

The primary key used by a foreign key is also knownas a parent key. the table where the primary key is from is knows as a parent table.

The foreing key can be used to make sure that the rows in on table have corresponding rows in another table.

Foreign key values can be null, even thugh primary key values can't.

Foreign key don't have to be unique - in facts, they ofter aren't



CREATE TABLE accout(
user_id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(20),
email VARCHAR(30),
birthday DATE,
gender VARCHAR(10),
city VARCHAR(30));





CREATE TABLE hobbies(
hob_id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,hobby VARCHAR(100) NOT NULL,
user_id INT NOT NULL,
CONSTRAINT account_user_id_fK
FOREIGN KEY(user_id)
REFERENCES accout(user_id));


Was this article helpful?
Thanks! Your feedback helps us to improve 9lessons.info


8 comments:

  1. one question please how would i assign a foreign key automatically every time i add a new record?

    ReplyDelete
  2. After making the database in this case, for example how do add values specifically to the hobbies field after making the table?

    ReplyDelete
  3. very well explained srinivas...now i got some clear picture in my mind...

    ReplyDelete
  4. Well explained Just to add :


    Sql Server is a relational database that means we can create relation like parent child using the combination of primary key and foreign key.
    A child can`t happen without parent same rules apply here like a table containing foreign key can`t keep data(in foreign key column) Which is not in its parent table (Primary key column).
    Like a parent can have many children so a primary key can have multiple foreign keys
    Why use Foreign key:

    Enforce Parent Child Relationship (Referential Integrity):
    As we have already specified that main aim of introducing the foreign key in sql is to create a parent child relationship among the tables.
    Cleanup Of Child Tables:
    On delete cascade in sql server provide the facility to delete the data in child table when data is deleted on the parent table, so you no need to delete manually from all the child tables.
    On update cascade in sql server Means update child data as well when data is updating in parent table.
    As now we know to create a foreign we must have a primary key so let`s create a parent table with primary key column:

    REF:

    http://www.tutorialsqlserver.com/Create/Foreign-Key-In-Sql-Server.htm

    ReplyDelete
  5. Very well explained but I think its better to add hob_id into ACCOUNT table as a foreign key. This way we dont need to write seperate record of hobby for every user.

    ReplyDelete
  6. Well if you put hobbyid into account table, then the user can have only 1 hobby at a time, if you would like to reuse the hobby for every users then you should create many to many relationship by creating a 'middle man' table where this table only consist of userid and hobbyid

    ReplyDelete
  7. suggestion for comment above: if you would like to reuse the hobby for every user, it is better to use many to many relationship by creating a 'middle man' table which consist of only userid and hobbyid, because 1 user might have many hobbies, and putting hobbyid into account table would restrict user to have only 1 hobby.

    ReplyDelete

Make in India