DEV Community

Mujahida Joynab
Mujahida Joynab

Posted on

Foreign Key in DBMS

If a primary key is used as a reference in another table is called Foreign key

How to write the query?
We know that , Constraints have to declared while creating the table .

create table sections
(
section_id int primary key,
section_name vachar(10) 
) 
Enter fullscreen mode Exit fullscreen mode

Foreign key in Student Table

create table student
(
id int primary key,
name varchar(20),
age int,
section_id int foreign key REFERENCES sections (section_id)
)

insert into student (id,name,age,section_id) values(1,'MUJAHIDA',22,1)
insert into student (id,name,age,section_id) values(2,'Farha',21,2)
insert into student (id,name,age,section_id) values(3,'Neela',23,3)
insert into student (id,name,age,section_id) values(3,'Aneela',01,8)----error
Enter fullscreen mode Exit fullscreen mode

For MySQL

section id int
foreign key(section id )
reference section(section id)

create table student 
(id int pu,
name , age,
section_id int,
foreign key(section_id) 
references student(ID)
)


Enter fullscreen mode Exit fullscreen mode
insert into student(id,name,age) values(5,'Farhan',18) 

Enter fullscreen mode Exit fullscreen mode

Foreign key can be NULL.

Let's say we have two table
One is Section .Another is Student .
Now I want to apply foreign key in student table .

Section id is primary key in section table and used as a reference key in student table . So it is a foreign key in student table .

ADD CONSTRAINTS

ALTER TABLE Student
ADD FOREIGN KEY(Section_id) // I want to add foreign key . In what table ? In section id
FEFERENCES Section(Section_id); //

ALTER TABLE Student
ADD CONSTRAINT FK_student
FOREIGN KEY(Section_id)
REFERENCES Sections(section_id) ;

Enter fullscreen mode Exit fullscreen mode

DROP CONSTRAINT
ALTER TABLE Student
DROP FOREIGN KEY FK_Sections ; // Here we have to write constraint name . Like FK_Student

*Find Constraint Name
*

SELECT COLUMN_NAME,CONSTRAINT_NAME
FROM INFROMATION_SCHEMA,KEY_COLUMN_USAGE
WHERE TABLE_NAME ='Student' ;

We can use here (**Find Constraint Name
) small letter .If do , write all letter small .

Heroku

This site is built on Heroku

Join the ranks of developers at Salesforce, Airbase, DEV, and more who deploy their mission critical applications on Heroku. Sign up today and launch your first app!

Get Started

Top comments (0)

A Workflow Copilot. Tailored to You.

Pieces.app image

Our desktop app, with its intelligent copilot, streamlines coding by generating snippets, extracting code from screenshots, and accelerating problem-solving.

Read the docs

👋 Kindness is contagious

Please leave a ❤️ or a friendly comment on this post if you found it helpful!

Okay