Cascade delete query on clients table

Question

I am trying to configure the CLIENTS table on my database such that if an employee is deleted from the EMP table any associated clients are automatically deleted from the CLIENTS table.

This is what I've tried so far.

ALTER TABLE clients
MODIFY empno REFERENCES emp(empno) ON DELETE CASCADE

Unfortunately this returns a near "MODIFY": syntax error every time I try to run it. Any idea what is going wrong?

Thanks in advance.

According to the SQLite manual, you cannot add a foreign key once the table has been created: http://www.sqlite.org/lang_altertable.html — , Jun 18 '12 at 12:38

score 3 · Accepted Answer · edited May 23 '17 at 10:24

3

Use this:

ALTER TABLE Clients
  ADD CONSTRAINT `Client_1`
    FOREIGN KEY empno REFERENCES emp(empno) ON DELETE CASCADE;

UPD:

According to this post, there is no way you can do that in SQLite. Therefore, my suggestion is this:

Create a temporary table.
Copy all the info there.
Drop the original table and create it with foreign key.
Copy all info from temporary table and remove the temporary table.

Of course, if the Clients table is empty right now, you can simply delete it and recreate.

edited May 23 '17 at 10:24

Community

1
1

answered Jun 18 '12 at 12:27

SPIRiT_1984

2,717
3
29
46

I am using this command in SQLite but it keeps returning a syntax error **near constraint**. – methuselah Jun 18 '12 at 12:31

Cascade delete query on clients table

1 Answers1