1

I'm trying to test this solution of ADD CONSTRAINT IF NOT EXISTS, but I get an error with my code :

IF NOT EXISTS (SELECT NULL FROM information_schema.TABLE_CONSTRAINTS WHERE
                   CONSTRAINT_SCHEMA = 'mydatabase' AND
                   CONSTRAINT_NAME   = 'tb_uq') THEN
   ALTER TABLE `scrape_xpaths` ADD CONSTRAINT `tb_uq` UNIQUE (`LinkCaption`);
END IF

I get a check syntax error.

Also this doesn't work:

IF NOT EXISTS (SELECT NULL FROM information_schema.TABLE_CONSTRAINTS) THEN

END IF

Same error.

Constraint name 'tb_uq' exists because commands :

SELECT NULL FROM information_schema.TABLE_CONSTRAINTS
SELECT * FROM information_schema.TABLE_CONSTRAINTS

work and return all constraints and tb_uq is one of them. What am I doing wrong?

This is how I add the constraint the first time. Second time fails of course :

ALTER TABLE `mydatabase`.tablename ADD CONSTRAINT tb_uq UNIQUE (LinkCaption);

Also the code below fails with the same error : 

if (true)
begin

end else
begin

end
Community
  • 1
  • 1
kawa
  • 422
  • 4
  • 16
  • is the CONSTRAINT_SCHEMA mydatabase not dbo? could you show the line from information_schema.TABLE_CONSTRAINTS you are trying to match? – David Molyneux Jun 18 '14 at 08:47
  • `SELECT * FROM information_schema.TABLE_CONSTRAINTS` gives me # CONSTRAINT_CATALOG, CONSTRAINT_SCHEMA, CONSTRAINT_NAME, TABLE_SCHEMA, TABLE_NAME, CONSTRAINT_TYPE **'def', 'mydatabase', 'tb_uq', 'mydatabase', 'scrape_information', 'UNIQUE'** on the line I'm trying to match – kawa Jun 18 '14 at 08:50
  • Right is the error you are getting 'Incorrect syntax near 'if'.'? you don't need the if on End if – David Molyneux Jun 18 '14 at 08:52
  • That's the error I'm getting. It fails without the if on End if also. Same error : `IF NOT EXISTS (SELECT * FROM information_schema.TABLE_CONSTRAINTS) THEN END ` fails also – kawa Jun 18 '14 at 08:55
  • Sorry just noticed the Then as well sql If Syntax is If [Condition] Begin End – David Molyneux Jun 18 '14 at 09:01
  • Thanks for taking the time. So `SELECT * FROM information_schema.TABLE_CONSTRAINTS WHERE CONSTRAINT_SCHEMA='mydatabase' AND CONSTRAINT_NAME='tb_uq' AND TABLE_NAME='scrape_information';` returns the correct row. BUT, `if exists (SELECT * FROM information_schema.TABLE_CONSTRAINTS WHERE CONSTRAINT_SCHEMA='mydatabase' AND CONSTRAINT_NAME='tb_uq' AND TABLE_NAME='scrape_information') begin end else begin end` gives me the same error – kawa Jun 18 '14 at 09:07
  • as there is nothing in your begin and end blocks you will get an error if exists (SELECT * FROM information_schema.TABLE_CONSTRAINTS WHERE CONSTRAINT_SCHEMA='mydatabase' AND CONSTRAINT_NAME='tb_uq' AND TABLE_NAME='scrape_information') begin print 'run' end else begin print 'dont run' end runs and doesnt give an error for me – David Molyneux Jun 18 '14 at 09:10
  • I'm using MSQLWorkbench , would that be the pb? – kawa Jun 18 '14 at 09:20
  • 1
    Ok I have installed MySQL on a VM and had a look, by the looks of it you cant run an if like this in workbench. The If would need to be in a select not as a function on its own. All i could find is people saying try anyway and handle the error – David Molyneux Jun 18 '14 at 10:29

0 Answers0