What is FOREIGN KEY? Easy Lesson

Posted by : Unknown Sabtu, 20 September 2008

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));