Fix the Integrity Constraint Violation in the folowing SQL code: drop table Stud

Question

Fix the Integrity Constraint Violation in the folowing SQL code:

drop table StudentCourse;
drop table student;
create table student(
sid number (10) constraint pk_student primary key,
stuname varchar2(30),
localtel number(10));

insert into student values (12, 'a', 1);
insert into student values (13, 'b', 1);

create table studentcourse(
sid number(10),
cid number(8),
grade number(3,1),
constraint pk_studentcourse primary key (sid, cid),
constraint fk_sid_student foreign key (sid) references student(sid));

insert into studentcourse values(12, 54,3.5);
insert into studentcourse values(412, 54,3.5);

Explanation / Answer

Integrity constraint violation occurs when there domain OR foreign key constraint violation happens.

Under the category of foreign key constraint we have following rules:

1. we can not enter a new value in the foreign key of the referencing table which does not exist in the primary key of the referenced table.

2. we can not change a primary key value in the referenced table if that record exists in referencing table..
3.we can not remove a row from a primary(referenced) table if matching records exist in a related referencing table.

The studentcourse schema(table) has the foreign key as sid which is referencing to primary key of student schema(table) sid.hence we can't drop the student schema(table) since it is being referenced by an another table hence the sql statement drop table student; must be removed(Rule no-3) in order to escape from violation of foreign key constraint.

Apart from above this the last sql statement insert into studentcourse values(412, 54,3.5); must also be removed(Rule no-1) because sid of studentcourse is the foreign key of table student. so very value of sid(studencourse) must be from values of sid(student). it means we can't have any value of sid(studencourse) which isn't present in values of sid(student).this will lead to forengin key integrity constraint violation.we can get read from this violation either by deleting sql statement insert into studentcourse values(412, 54,3.5); or by adding a new row to student table which has 412 as sid.

Related Questions

Navigate

• Browse (All)
• Browse F
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.