0

I have two table: users and 'bills'. In bills as a foreign key to users.

I want to automatically delete rows from the bills table when I delete from the users table. For that I alter table with following query, still it doesn't delete entry from bills table.

My alter statement is:

ALTER TABLE bills
ADD CONSTRAINT fk_pid
FOREIGN KEY (pid)
REFERENCES users(id)
ON DELETE CASCADE

here pid is foreign key in bills table, whereas id primary key in users table

Please help me to resolve above issue, thank you in advance.

Danny
  • 47
  • 1
  • 11
  • 1
    Please show CRAETE TABLE bills, users. – Devart Mar 15 '13 at 08:11
  • use create instead of alter CREATE TABLE bills ADD CONSTRAINT fk_pid FOREIGN KEY (pid) REFERENCES users(id) ON DELETE CASCADE – Rahul Mar 15 '13 at 08:21
  • Have a look at this link - [MySQL Foreign Key “ON DELETE CASCADE” across 3 tables](http://stackoverflow.com/questions/8878453/mysql-foreign-key-on-delete-cascade-across-3-tables/8878843#8878843). – Devart Mar 15 '13 at 08:28

2 Answers2

1

Use create instead of alter,Otherwise your syntax is ok

Create TABLE bills(
Your columns details
------
------
ADD CONSTRAINT fk_pid
FOREIGN KEY (pid)
REFERENCES users(id)
ON DELETE CASCADE
)

Try this..

If it's doesn't work then try for this thing as well. if your both tables having mismatch primary key and foreign key problem then you cannot add Delete Cascade.For that you need to fix that key problem.like you don't have primary key value in your user table and you are using that same id in your bills table as a foreign key then you cannot add cascade in bills table.For that remove that key from bills table and then try your adding cascade script with Alter.I had same problem but i use this way and it worked.Hope it will work for you as well.Thanks

Rahul
  • 5,603
  • 6
  • 34
  • 57
  • 1
    CREATE TABLE syntax requires columns at least. – Devart Mar 15 '13 at 08:24
  • i dont want wanna create table again , i want to apply it on my existing table , so i am using alter command – Danny Mar 15 '13 at 08:59
  • try my second option as well.I think it will be keys mismatch problem.Check that all your foreign key in bills table has it's primary key in users table or not. – Rahul Mar 15 '13 at 10:44
0

I think this solution for create can help you in figuring out what actually you are missing.

Go thru it and try customizing it as per your need, as you haven't provided enough detail along with your question.

I suspect it has to do something with your create table statement

OR try this for ALTER1 OR ALTER2

Community
  • 1
  • 1
DeltaCap019
  • 6,532
  • 3
  • 48
  • 70
  • i dont want wanna create table again , i want to apply it on my existing table , so i am using alter command – Danny Mar 15 '13 at 08:59
  • look at my UPDATED answer.. I think it can help you in finding how to use ALTER.. As you haven't provided enough detail for your tables. You have to figure that out yourself from this link.. I hope it will help you... – DeltaCap019 Mar 15 '13 at 10:01