I am trying to perform a row deletion from a table employee that has a primary key set on 'emp_id' column and is referenced by other tables with their foreign keys. Sybase does not allow me to perform the deletion. The example msg back is :
Dependent foreign key constraint violation in a referential integrity constraint. dbname = 'giraffe', table name = 'branch', constraint name = 'client_800002850'. Command has been aborted.
With MySQL it is possible to easily make this kind of deletion if you specifiy foreign keys with ON DELETE clause, but this is not supported by Sybase. The only optional clause for foreign keys in this RDBMS is 'MATCH FULL'
How can one delete rows or set values to NULL in Sybase if there are foreign keys set up ? I am a total newbie in t-sql and DBA. to my understanding, having the db schema such as this, it would require not having the foreign keys at all or am I missing something ? Maybe this schema is MySQL specific and it should be coded differently to meet Sybase t-sql standards ?
here is the DDL. link to the course - https://www.youtube.com/watch?v=HXV3zeQKqGY
===========================
CREATE TABLE employee (
emp_id INT PRIMARY KEY,
first_name VARCHAR(40),
last_name VARCHAR(40),
birth_day DATE,
sex VARCHAR(1),
salary INT,
super_id INT,
branch_id INT
);
CREATE TABLE branch (
branch_id INT PRIMARY KEY,
branch_name VARCHAR(40),
mgr_id INT,
mgr_start_date DATE,
FOREIGN KEY(mgr_id) REFERENCES employee(emp_id) ON DELETE SET NULL
);
ALTER TABLE employee
ADD FOREIGN KEY(branch_id)
REFERENCES branch(branch_id)
ON DELETE SET NULL;
ALTER TABLE employee
ADD FOREIGN KEY(super_id)
REFERENCES employee(emp_id)
ON DELETE SET NULL;
CREATE TABLE client (
client_id INT PRIMARY KEY,
client_name VARCHAR(40),
branch_id INT,
FOREIGN KEY(branch_id) REFERENCES branch(branch_id) ON DELETE SET NULL
);
CREATE TABLE works_with (
emp_id INT,
client_id INT,
total_sales INT,
PRIMARY KEY(emp_id, client_id),
FOREIGN KEY(emp_id) REFERENCES employee(emp_id) ON DELETE CASCADE,
FOREIGN KEY(client_id) REFERENCES client(client_id) ON DELETE CASCADE
);
CREATE TABLE branch_supplier (
branch_id INT,
supplier_name VARCHAR(40),
supply_type VARCHAR(40),
PRIMARY KEY(branch_id, supplier_name),
FOREIGN KEY(branch_id) REFERENCES branch(branch_id)
Here is a list of constraints on this 'employee' table
I want to perform the following DML:
DELETE from dbo.employee
WHERE employee.emp_id = 102
In that MySQL course the subsequent automatic action would have been setting the branch.mgr_id value from 102 to NULL provided that the foreign key for 'branch' table had been set with ON DELETE SET NULL clause.
I tried setting up branch.mgr_id to NULL since it's not the PK for the table but obviously it did not help.
When you delete a primary-key row, delete corresponding foreign-key rows in dependent tables. This preserves referential integrity by ensuring that detail rows are removed when their master row is deleted. If you do not delete the corresponding rows in the dependent tables, you may end up with a database with detail rows that cannot be retrieved or identified. To properly delete the dependent foreign-key rows, use a trigger that performs a cascading delete.
When a delete statement on titles is executed, one or more rows are removed from the titles table and are added to deleted. A trigger can check the dependent tables—titleauthor, salesdetail, and roysched—to see if they have any rows with a title_id that matches the title_ids removed from titles and is now stored in the deleted table. If the trigger finds any such rows, it removes them.
create trigger delcascadetrig
on titles
for delete
as
delete titleauthor
from titleauthor, deleted
where titleauthor.title_id = deleted.title_id
/* Remove titleauthor rows that match deleted
** (titles) rows.*/
delete salesdetail
from salesdetail, deleted
where salesdetail.title_id = deleted.title_id
/* Remove salesdetail rows that match deleted
** (titles) rows.*/
delete roysched
from roysched, deleted
where roysched.title_id = deleted.title_id
/* Remove roysched rows that match deleted
** (titles) rows.*/
source:-