lundi 14 novembre 2016

Update table set primary key if it doesn't exist

I'm attempting to update a table and set a column as a primary key, but I need to make sure it first does not exist. I've tried several options outlined in other answers on here, but none seem to work and I just get a syntax error. Here is what I'm currently working with in mySql 5.5:

SELECT IF EXISTS (SELECT
*
FROM information_schema.TABLE_CONSTRAINTS
WHERE CONSTRAINT_TYPE = 'PRIMARY KEY'
AND TABLE_SCHEMA = 'database1'
AND TABLE_NAME = 'table1') THEN
BEGIN
-- PRIMARY KEY EXISTS
END;
ELSE
BEGIN
ALTER TABLE table1 ADD PRIMARY KEY (table1_id);
END;
END IF;

Does anyone know of a way to check for a foreign key and create one if it does not exist? All in the same statement?

Aucun commentaire:

Enregistrer un commentaire