Foreign key in sql | Foreign key command in sql - sql - sql tutorial - learn 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

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

difference between primary key and foreign key in sqlprimary key and foreign key examplesprimary key and foreign key in sql with examples foreign key in oracleprimary key and foreign key definitionunique key in sqlhow to add foreign keyforeign key definitionprimary key and foreign key definitionsql foreign keyforeign key sqlwhat is foreign key in sqlhow to use foreign key in sqlsql keyskeys in sqltypes of keys in sqlwhat is a foreign keydefine foreign keyuse of foreign keykey inkeys in databaseon update cascadekeys in dbms with examplesdefine foreign key in databaseforeign key in database with exampleforeign key in sql server 2012key sqlkey tableprimary key and foreign key in databasewhat is a foreign key in a databasewhat is foreign key in databasesql table foreign keyexample for foreign keysql command for foreign keysql keyforeign key accessreferences mysqlhow to set foreign key in sql server 2008database primary key foreign keyexample of foreign key in databasesql create table with foreign keysql foreign key create tablefind tables with foreign key constraint in databasesql foreign key syntaxforeign key sql syntaxforeign key with example in dbmsdbms foreign keyexplain foreign key with exampleinsert foreign key sqlsql for foreign keyforeign key in accesswhat is a foreign key in accessforeign key command in sqladd foreign key sql server 2012how to create a foreign key in sql serveradd foreign key in sql server 2012alter table add foreign key sql serverforeign key databasethe alter table statement conflicted with the foreign key constraintprimary key in sql