CODEWITHSUNDEEP
mysql
user2447830
user2447830

Reputation: 53

How to enforce referential constraints (foreign keys)

I am new to mysql and online forums, and still some what of novice in programming, Please I am trying to enforce the Referential Constraint , Everytime I insert or update a value (ie 76) in the Dno column and in the Employee table, It works even though it is a foreign key referencing the PK Dnumber in the Department table which doesn't have the matching value. How can I enforce the referential violation?

CREATE TABLE EMPLOYEE
( Fname VARCHAR(15) NOT NULL,
Minit CHAR,
Lname VARCHAR(15) NOT NULL,
Ssn CHAR(9) NOT NULL,
Bdate DATE,
Address VARCHAR(30),
Sex CHAR,
Salary DECIMAL(10,2),
Super_ssn CHAR(9),
Dno INT NOT NULL,
PRIMARY KEY (Ssn),
FOREIGN KEY (Super_ssn) REFERENCES EMPLOYEE(Ssn),
FOREIGN KEY (Dno) REFERENCES DEPARTMENT(Dnumber) );


CREATE TABLE DEPARTMENT
( Dname VARCHAR(15) NOT NULL,
Dnumber INT NOT NULL,
Mgr_ssn CHAR(9) NOT NULL,
Mgr_start_date DATE,
PRIMARY KEY (Dnumber),
UNIQUE (Dname),
FOREIGN KEY (Mgr_ssn) REFERENCES EMPLOYEE(Ssn) );

--------------------------Example of the Update code------------------
UPDATE EMPlOYEE
SET Dno='12'
WHERE Fname='Sandra';

Upvotes: 0

Views: 475

Answers (1)

Thomas Padron-McCarthy
Thomas Padron-McCarthy

Reputation: 27672

Try to add "engine=InnoDB" when you create the tables. Depending on version and platform, the default storage format for tables is MyISAM, and there referential integrity is not checked.

Here is an example from 5.0.91-log MySQL Community Server running on Linux:

mysql> CREATE TABLE Department
    -> (dno INTEGER NOT NULL,
    -> PRIMARY KEY (dno));
Query OK, 0 rows affected (0.04 sec)

mysql> CREATE TABLE Employee
    -> (eno INTEGER NOT NULL,
    -> works_at INTEGER NOT NULL,
    -> PRIMARY KEY (eno),
    -> FOREIGN KEY (works_at) REFERENCES Department(dno));
Query OK, 0 rows affected (0.03 sec)

mysql> INSERT INTO Employee (eno, works_at) VALUES (17, 4711);
Query OK, 1 row affected (0.02 sec)

mysql> DROP TABLE Employee;
Query OK, 0 rows affected (0.00 sec)

mysql> DROP TABLE Department;
Query OK, 0 rows affected (0.00 sec)

mysql> CREATE TABLE Department
    -> (dno INTEGER NOT NULL,
    -> PRIMARY KEY (dno)) ENGINE=InnoDB;
Query OK, 0 rows affected (0.15 sec)

mysql> CREATE TABLE Employee
    -> (eno INTEGER NOT NULL,
    -> works_at INTEGER NOT NULL,
    -> PRIMARY KEY (eno),
    -> FOREIGN KEY (works_at) REFERENCES Department(dno)) ENGINE=InnoDB;
Query OK, 0 rows affected (0.09 sec)

mysql> INSERT INTO Employee (eno, works_at) VALUES (17, 4711);
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`studentbasen/Employee`, CONSTRAINT `Employee_ibfk_1` FOREIGN KEY (`works_at`) REFERENCES `Department` (`dno`))

mysql>

Upvotes: 2

Related Questions