java2db

Sql FOREIGN KEY constraint on table column



FOREIGN KEY constraint  represent relationships between tables. A foreign key is a column whose values are derived from the primary key or unique key of the primary table.

 

Primary table : The table that defines the primary or unique keys and is referenced by the foreign key.

Foreign table : The table in which the foreign key is defined is called a foreign table or Detail table.

FOREIGN KEY constraint  Requires that the Foreign key column(s) and reference column(s) have the same data type.
FOREIGN KEY constraint  Primary or unique key must be there in the master table , while create foreign table.
FOREIGN KEY constraint  Deletion of rows from the master table is not possible , if  the foreign table having corresponding

…..values.
FOREIGN KEY constraint  Rejects an insert or update of a value in a particular column of the foreign table , if a corresponding

…..value does not exist in the master table.
FOREIGN KEY constraint  The master table can be referenced in the foreign key definition by using references keyword.
FOREIGN KEY constraint  If the column name is not specified, by default, Oracle references the primary key in the master

…..table.

 

FOREIGN KEY constraint creation :

 Primary or Master table creation : Create a table with primary or unique key constraint .

STUD_ID is the PRIMARY KEY  in the  STUDENT  (master) table .

STUD_ID
STUD_NAME
STUD_GENDER
1 Rose F
2 Ben M

 

Foreign or Detail table creation :

FOREIGN KEY constraint in column level:

Specify the foreign key as very next to the desired column.

 

FOREIGN KEY constraint in table level:

Declare the  foreign key constraint at the end of the last column.

Execute any one of the  above  statements to create MARKS (Foreign) table that having the FOREIGN KEY (STUD_ID) .

M_ID
M_MARK1
M_MARK2
M_MARK3
STUD_ID
1 89 80 70 2
2 88 89 79 1

 

ON DELETE CASCADE : If any row deleted form the primary table  , it should delete  all the corresponding records from the foreign table automatically. This can be apply either CREATE or ALTER a table.

 

FOREIGN KEY constraint with ALTER : If the table already exist , apply the foreign key with ALTER command.

 

Drop the FOREIGN KEY constraint :




Related Posts :
Sql CHECK constraint on table column
Sql NOT NULL constraint on table column
Sql UNIQUE constraint on table column
Sql PRIMARY KEY constraint on table column
Sql full outer join on tables with an example
Sql right outer join on tables with an example
Sql left outer join on tables with an example
Sql inner join on tables with an example
   [will not be published]



^ <