SQL ON DELETE CASCADE based on a condition

Question

I have two tables which are linked with the foreign key in the Staff member table. I'm trying to setup the foreign key so that a row in the position table is removed if it has no associated staff members from the Staff Member table. I am using ON DELETE CASCADE however this removes the row even if there are still staff members associated with the position.

Position Table
----------------------------------------------
----Attribute-------Type-------------Keys-----
----------------------------------------------
-    Title     -- VARCHAR(20)  --Primary Key--
-    MaxSalary -- DECIMAL(8,2) --     No     -
-    MinSalary -- DECIMAL(8,2) --     No     -
----------------------------------------------

StaffMember Table
-----------------------------------------------------
----Attribute-------Type-----------------Keys--------
-----------------------------------------------------
-    StaffNo    -- INTEGER     --    Primary Key   --
-    Name       -- VARCHAR(20) --         No        -
-    Salary     -- DECIMAL(8,2)--         No        -
-    StaffTitle -- VARCHAR(20) --FK Position(Title) -
-----------------------------------------------------

CREATE TABLE StaffMember(
StaffNo INTEGER PRIMARY KEY,
Name VARCHAR(20),
Salary DECIMAL(8,2),
StaffTitle VARCHAR(20) FOREIGN KEY REFERENCES Position(Title) ON DELETE CASCADE);

Answer 1

If I understood you correctly, you're doing exactly the opposite from what you'd want to.

ON DELETE CASCADE is used if you want to remove both master and detail rows using a single DELETE command, i.e. deleting a master will delete all its details.

You said you want to prevent it from happen.

So - remove that option from the constraint statement, i.e.

stafftitle varchar2(20) constraint fk_staff_position references position (title)

Here's an example:

SQL> create table position (title varchar2(20) primary key);

Table created.

SQL> create table staffmember
  2    (staffno    integer primary key,
  3     name       varchar2(20),
  4     salary     decimal (8, 2),
  5     stafftitle varchar2(20) constraint fk_staff_pos references position (title)
  6    );

Table created.

SQL> insert into position (title) values ('some title');

1 row created.

SQL> insert into staffmember (staffno, name, salary, stafftitle)
  2    values (1, 'name', 100, 'some title');

1 row created.

SQL> delete from position;
delete from position
*
ERROR at line 1:
ORA-02292: integrity constraint (SCOTT.FK_STAFF_POS) violated - child record found


SQL>

See? You can't delete master as long as detail exists.