Foreign key in sql | Foreign key command in sql - sql - sql tutorial - learn sql




 foreign key command in sql

What is foreign key in database?

  • A foreign key is a column (or columns) that references a column of another table.
  • The purpose of the foreign key is to ensure referential integrity of the data.
  • Only values that are supposed to appear in the database are permitted.
  • A foreign key is a key used to link two tables together. This is sometimes also called as a referencing key.
  • A Foreign Key is a column or a combination of columns whose values match a Primary Key in a different table.
  • The relationship between 2 tables matches the Primary Key in one of the tables with a Foreign Key in the second table.
  • If a table has a primary key defined on any field(s), then you cannot have two records having the same value of that field(s).

Example:

  • Consider the structure of the following two tables.

CUSTOMERS table

CREATE TABLE CUSTOMERS(
   ID INT NOT NULL,
   NAME VARCHAR (20) NOT NULL,
   AGE INT NOT NULL,
   ADDRESS CHAR (25) ,
   SALARY DECIMAL (18, 2),       
   PRIMARY KEY (ID)
);

ORDERS table

CREATE TABLE ORDERS (
   ID INT NOT NULL,
   DATE DATETIME, 
   CUSTOMER_ID INT references CUSTOMERS(ID),
   AMOUNT double,
   PRIMARY KEY (ID)
);
  • If the ORDERS table has already been created and the foreign key has not yet been set, the use the syntax for specifying a foreign key by altering a table.
ALTER TABLE ORDERS 
   ADD FOREIGN KEY (Customer_ID) REFERENCES CUSTOMERS (ID);
Tags : sql tutorial , pl sql tutorial , mysql tutorial , oracle tutorial , learn sql , sql server tutorial

DROP a FOREIGN KEY Constraint:

  • To drop a FOREIGN KEY constraint, use the following SQL syntax.
ALTER TABLE ORDERS
   DROP FOREIGN KEY;
sql tutorial , pl sql tutorial , mysql tutorial , oracle tutorial , learn sql , sql server tutorial

SQL FOREIGN KEY on CREATE TABLE:

  • The following SQL creates a FOREIGN KEY on the "PersonID" column when the "Orders" table is created:

MySQL:

CREATE TABLE Orders (
    OrderID int NOT NULL,
    OrderNumber int NOT NULL,
    PersonID int,
    PRIMARY KEY (OrderID),
    FOREIGN KEY (PersonID) REFERENCES Persons(PersonID)
);

SQL Server / Oracle / MS Access:

CREATE TABLE Orders (
    OrderID int NOT NULL PRIMARY KEY,
    OrderNumber int NOT NULL,
    PersonID int FOREIGN KEY REFERENCES Persons(PersonID)
);
  • To allow naming of a FOREIGN KEY constraint, and for defining a FOREIGN KEY constraint on multiple columns, use the following SQL syntax:

MySQL / SQL Server / Oracle / MS Access:

CREATE TABLE Orders (
    OrderID int NOT NULL,
    OrderNumber int NOT NULL,
    PersonID int,
    PRIMARY KEY (OrderID),
    CONSTRAINT FK_PersonOrder FOREIGN KEY (PersonID)
    REFERENCES Persons(PersonID) 
);

SQL FOREIGN KEY on ALTER TABLE:

  • To create a FOREIGN KEY constraint on the "PersonID" column when the "Orders" table is already created, use the following SQL:

MySQL / SQL Server / Oracle / MS Access:

ALTER TABLE Orders
ADD FOREIGN KEY (PersonID) REFERENCES Persons(PersonID);
  • To allow naming of a FOREIGN KEY constraint, and for defining a FOREIGN KEY constraint on multiple columns, use the following SQL syntax:

MySQL / SQL Server / Oracle / MS Access:

ALTER TABLE Orders
ADD CONSTRAINT FK_PersonOrder
FOREIGN KEY (PersonID) REFERENCES Persons(PersonID);
sql tutorial , pl sql tutorial , mysql tutorial , oracle tutorial , learn sql , sql server tutorial

SQL Foreign key 1 to many

SQL Foreign key 1 to many

This tutorial provides more the basic needs and informations on sql tutorial , pl sql tutorial , mysql tutorial , sql server , sqlcode , sql queries , sql , sql formatter , sql join , w3schools sql , oracle tutorial , mysql , pl sql , learn sql , sql tutorial for beginners , sql server tutorial , sql query tutorial , oracle sql tutorial , t sql tutorial , ms sql tutorial , database tutorial , sql tutorial point , oracle pl sql tutorial , oracle database tutorial , oracle tutorial for beginners , ms sql server tutorial , sql tutorial pdf

Related Searches to Foreign key in sql | Foreign key command in sql