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)
)
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
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)
)
insert into student(id,name,age) values(5,'Farhan',18)
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) ;
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 .
Top comments (0)