I’ve been looking at foreign key constraints today. I’ve managed to set a FK constraint in an Oracle and a MySQL database without too much trouble. But I was having trouble understanding why you would set up a FK constraint. It seems some of the reasons are: –
- ensure referential integrity
- assist with visualizing and preparing database diagrams
- may assist with performance
- assist programmers by requiring less code.
I did some testing in Oracle and MySQL to make sure i understand how they work and it was a very interesting learning expierence.
Here’s what I did. First you need to create two tables. All SQL is for Oracle, minor editing my be required to get it to work in MySql or other DBMS systems.
-- Create Subjects table with PK on Subject column
CREATE TABLE STATES ( STATE VARCHAR(4) NOT NULL,
CONSTRAINT PK_STATE PRIMARY KEY (STATE) );
-- Create TIPS table with PK and FK
CREATE TABLE CITIES ( CITYID NUMBER(5) NOT NULL,
CITY VARCHAR2(20) NOT NULL,
STATE VARCHAR2(4) NOT NULL,
CONSTRAINT PK_CITYID PRIMARY KEY (CITYID) VALIDATE ,
CONSTRAINT FK_STATE FOREIGN KEY (STATE)
REFERENCES STATES (STATE) ON DELETE CASCADE);
-- Insert data into subject table
insert into STATES values ('QLD');
insert into STATES values ('VIC');
-- Insert data into tips table
insert into CITIES values (1,'Brisbane','QLD');
insert into CITIES values (2,'Melbourne','VIC');
-- show data
select * from STATES;
select * from CITIES;
If you now delete the row containing the value ‘VIC’ from the STATES table,
DELETE FROM STATES where STATE = ‘VIC’;
it will also delete all rows in the CITIES table that have a value of ‘VIC’ in the STATE column.
Conversely, if you delete all rows in CITIES table that contain ‘VIC’ in the STATES column, the row for ‘VIC’ is NOT deleted from the from STATES table.
This type of thing also works in MySQL but only if you are using the InnoDB storage engine for your table(s), not the default MyISAM engine.