ALTER TABLE add constraint

Question

The tables User and Properties were created properly

CREATE TABLE Properties
(
    ID int AUTO_INCREMENT,
    language int,
    stonecolor int,
    gamefield int,
    UserID int,
    PRIMARY KEY(ID),
    FOREIGN KEY(language) REFERENCES Language(ID),
    FOREIGN KEY(stonecolor) REFERENCES StoneColor(ID),
    FOREIGN KEY(gamefield) REFERENCES GameField(ID)
) ENGINE = INNODB;

CREATE TABLE User
(
    ID int AUTO_INCREMENT,
    vorname varchar(30) NOT NULL,
    name varchar(30) NOT NULL,
    email varchar(40) NOT NULL,
    password varchar(40) NOT NULL,
    nickname varchar(15) NOT NULL,
    score int,
    isadmin int DEFAULT 0,
    gamesPlayed int,
    properties int NOT NULL,
    PRIMARY KEY(ID),
    UNIQUE (email),
    UNIQUE (nickname)

) ENGINE = INNODB;

But ALTER TABLE User doesn't work.

ALTER TABLE User 
(
    ADD CONSTRAINT userPropertie
    FOREIGN KEY(properties)
    REFERENCES Properties(ID)
)

Can't figure out why?

I used this as reference http://www.w3schools.com/sql/sql_foreignkey.asp

Error 1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '( ADD CONSTRAINT userPropertie FOREIGN KEY(properties) REFERENCES Properties(' at line 2

I would like to suggest a title change to help people having this error to find this thread, something like "ALTER TABLE ADD CONSTRAINT syntax error" could help. — stramin, Jan 23 '20 at 16:58
FYI: [adding a new sql column with a default value](https://stackoverflow.com/questions/3569347/) or [adding multiple columns after a specific column in mysql](https://stackoverflow.com/questions/17541312/) or [add a column to mysql table if it does not exist](https://stackoverflow.com/questions/972922/) — surfmuggle, Jul 06 '21 at 12:30

score 99 · Accepted Answer · answered Apr 17 '12 at 14:28

99

Omit the parenthesis:

ALTER TABLE User 
    ADD CONSTRAINT userProperties
    FOREIGN KEY(properties)
    REFERENCES Properties(ID)

answered Apr 17 '12 at 14:28

Andomar

232,371
49
380
404

score 4 · Answer 2 · answered Dec 03 '18 at 20:31

4

ALTER TABLE `User`
ADD CONSTRAINT `user_properties_foreign`
FOREIGN KEY (`properties`)
REFERENCES `Properties` (`ID`)
ON DELETE NO ACTION
ON UPDATE NO ACTION;

answered Dec 03 '18 at 20:31

Vladimir Salguero

5,609
3
42
47

score 0 · Answer 3 · edited Aug 30 '20 at 05:54

0

alter table User 
    add constraint userProperties
    foreign key (properties)
    references Properties(ID)

edited Aug 30 '20 at 05:54

Arghya Sadhu

41,002
9
78
107

answered Aug 30 '20 at 05:49

HEMANTH KUMAR

3
2

2

Please try to add an explanation of how this code solves the question – Arghya Sadhu Aug 30 '20 at 05:54

ALTER TABLE add constraint

3 Answers3