Add column with FK to an existing table in MySQL 5.6

Question

Good day everyone. Hope someone can helps me in my experiments.

I have a simple Table for my needs and it's even have some data inside.

CREATE TABLE IF NOT exists main.test (
ID INT(11) NOT NULL, 
NAME varchar(30) NOT NULL, 
DATE_CREATED timestamp NOT NULL, 
PRIMARY KEY (ID));

But then I should update this table with adding column FK_.

How can I check if table had already has field FK_? If such column is not exist do:

ALTER TABLE main.test
ADD COLUMN FK INT(11),
ADD FOREIGN KEY (FK)
REFERENCES test2(ID_test2)

possible duplicate of [MySQL check if a table exists without throwing an exception](http://stackoverflow.com/questions/1525784/mysql-check-if-a-table-exists-without-throwing-an-exception) — IgnazioC, Aug 19 '15 at 15:32
do you want to create a foreign key constraint or add a column? Are you sure you want to add a column? — Drew, Aug 19 '15 at 15:45
please check out this [Answer](http://stackoverflow.com/a/32097401/1816093) I just wrote up for a guy that thought adding columns on the fly was what he really wanted to do. In his case, it was not. May not be your issue. Good luck ! And Welcome to the Stack — Drew, Aug 19 '15 at 15:47
@Drew Greate thanks! Yep, I need to add new column and set it as FK. And then, possible, I'll be need to add one more column with fk and update data to new structure. — o.solod, Aug 20 '15 at 06:18

score 0 · Accepted Answer · answered Aug 20 '15 at 09:33

As I use java decision of my problem was using ResultSet.

ResultSet set = statement.executeQuery(query);
set.next();
int result = set.getInt(1); //it always return only one row
set.close();

And this is my sql-query:

SELECT COUNT(COLUMN_NAME) FROM information_schema.COLUMNS
WHERE
TABLE_SCHEMA = 'main'
AND TABLE_NAME = 'test'
AND COLUMN_NAME = 'FK";

When I'll get a result I can decide what query I should to use.

1 Answers1