Foreign Key After Table Creation

Question

I have already created a table in MySQL! And have tried a number of queries to alter the table and add foreign key to the table! But none of them work?? No error message no nothing but still nothing happening...

I need the exact query which would work! :(

Details:

Table1: users column: id
Table2: Pokemon_ref column: pkmn_id

If an insertion is done in Table1 then it should also be added in Table2!

your first change the table to InnoDB and then try it. for more reference link : http://stackoverflow.com/questions/3818759/what-is-innodb-and-myisam-in-mysql — Anand Somasekhar, Apr 15 '14 at 05:22
"*then it should also be added in Table2*" - this is not how foreign keys work. The prevent insertion with values that do not match a valid value in the referenced table. A foreign key will **never** "add" rows automatically to a table. — , Apr 15 '14 at 05:41
I meant if a user registers at #50 as id in table1, the table2 will also be updated id=50 .. — user3531660, Apr 15 '14 at 05:56

score 1 · Answer 1 · answered Apr 15 '14 at 05:18

    category INT NOT NULL, id INT NOT NULL,
    price DECIMAL,
    PRIMARY KEY(category, id)
)   ENGINE=INNODB;

CREATE TABLE customer (
    id INT NOT NULL,
    PRIMARY KEY (id)
)   ENGINE=INNODB;

CREATE TABLE product_order (
    no INT NOT NULL AUTO_INCREMENT,
    product_category INT NOT NULL,
    product_id INT NOT NULL,
    customer_id INT NOT NULL,

    PRIMARY KEY(no),
    INDEX (product_category, product_id),
    INDEX (customer_id),

    FOREIGN KEY (product_category, product_id)
      REFERENCES product(category, id)
      ON UPDATE CASCADE ON DELETE RESTRICT,

    FOREIGN KEY (customer_id)
      REFERENCES customer(id)
)

ALTER TABLE tabl2 ADD COLUMN FOREIGNID INT NOT NULL, ADD FOREIGN KEY (FOREIGNID) REFERENCES tabl1(ID) — aΨVaN, Apr 15 '14 at 05:21

score 1 · Accepted Answer · edited May 23 '17 at 11:57

1

sample:

ALTER TABLE tablename
ADD CONSTRAINT FK_Name_ID FOREIGN KEY (fk_ID)
    REFERENCES (R_id);

https://dev.mysql.com/doc/refman/5.1/en/create-table-foreign-keys.html

http://www.w3schools.com/sql/sql_foreignkey.asp

Alter table to give foreign key constraint

edited May 23 '17 at 11:57

Community

1
1

answered Apr 15 '14 at 05:22

jmail

5,944
3
21
35

Foreign Key After Table Creation

2 Answers2