SQL : ALTER TABLE Add does not work

0
votes

I have successfully created a table using:

CREATE TABLE "countermeasure" 
(
     "id" INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
     "name" VARCHAR, 
     "description" TEXT check(typeof("description") = 'text'), 
     "probability" DOUBLE, 
     "cost" DOUBLE
)

and now I need to add a foreign key to it using:

ALTER TABLE "countermeasure" 
(ADD "attack_id" INTEGER,
 FOREIGN KEY ( "attack_id") REFERENCES attack(id)
)

but it complains with the error:

SQLiteManager: Likely SQL syntax error: ALTER TABLE "countermeasure" (
ADD "attack_id" INTEGER ,
FOREIGN KEY ( "attack_id") REFERENCES attack(id) ) [ near "(": syntax error ]
Exception Name: NS_ERROR_FAILURE

I am sure I've created the attack table well.

3
sqlsqlite
Have you tried removing all double quotes? Or adding a constraint name for foreign key? - Anand
no, but what kind of problem they make? - user5363938
Your SQL is inconsistent, you are using double quotes everywhere but REFERENCES attack(id) so just a suggestion. - Anand
No parens in the alter table statement when you're adding a column. - Mike Sherrill 'Cat Recall'
I'm not sqlite expert, but according to their documentation your parantheses around ADD are not suppose to be there. - JNevill

3 Answers

0
votes

Use the following syntax: 

 ALTER TABLE table1
 ADD FOREIGN KEY (P_Id)
 REFERENCES table2(P_Id)
0
votes

As suggested, parenthesis around add shouldn't be there. This is the norm generally used for foreign keys

    ALTER TABLE table1 ADD COLUMN FOREIGNID INT NOT NULL, ADD FOREIGN KEY     
    (FOREIGNID) REFERENCES table2(ID)
0
votes

change (,) for CONSTRAINT

ALTER TABLE countermeasure 
ADD attack_id INTEGER CONSTRAINT fk FOREIGN KEY (attack_id) REFERENCES attack(id)

or just 

ALTER TABLE countermeasure 
ADD attack_id INTEGER FOREIGN KEY REFERENCES attack(id)