Postgresql – Adding constraint on data

No, the constraint name is completely unpredictable. If you want your names to be consistent, you can name them correctly by applying a predictable / repeatable name manually. I have no idea how you would do this in the code you have, but in T-SQL instead of:

CREATE TABLE dbo.foo(bar INT PRIMARY KEY);

CREATE TABLE dbo.blat(bar INT FOREIGN KEY REFERENCES dbo.foo(bar));

(The above end up with constraints having names like PK__foo__DE90ECFF6CF25EF6 and FK__blat__bar__1B1EE1BE.)

You would say:

CREATE TABLE dbo.foo(bar INT, CONSTRAINT PK_foo PRIMARY KEY (bar));

CREATE TABLE dbo.blat(bar INT, CONSTRAINT fk_foobar FOREIGN KEY(bar) 
  REFERENCES dbo.foo(bar));

There is no need to name your constraints, but if you do, then that is the name that must be unique within each schema. The error is telling you that You already have constraints with the same name elsewhere.

To see where, you can SHOW ENGINE INNODB STATUS;

------------------------
LATEST FOREIGN KEY ERROR
------------------------
130129 19:45:00 Error in foreign key constraint creation for table `test`.`baz`.
A foreign key constraint of name `test`.`foo_id`
already exists. 

But in this case, we don't need to look that up, since it's apparent from the script that you're reusing the same constraint names in multple table definitions.

If you remove lines like this...

CONSTRAINT `Comune`

... but leave the rest of your table definition intact...

  FOREIGN KEY (`Comune` )
  REFERENCES `PROGETTO`.`PAESE` (`Comune` )
  ON DELETE NO ACTION
  ON UPDATE CASCADE)

... you should not have a problem. You will find that InnoDB will generate names for your constraints, like PAESE_ibfk_1, PAESE_ibfk_2, etc.

Or, you can continue to declare the names of your constraints, remembering that whatever comes after the keyword CONSTRAINT has to be unique within each schema.

"If the CONSTRAINT symbol clause is given, the symbol value must be unique in the database. If the clause is not given, InnoDB creates the name automatically."

http://dev.mysql.com/doc/refman/5.5/en/innodb-foreign-key-constraints.html