CODEWITHSUNDEEP
mysqlsqlddlalter-table
ABLX
ABLX

Reputation: 736

ALTER TABLE ADD CONSTRAINT gets Error 1064

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 a 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

Upvotes: 37

Views: 136856

Answers (3)

HEMANTH KUMAR
HEMANTH KUMAR

Reputation: 3

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

Upvotes: 0

Vladimir Salguero
Vladimir Salguero

Reputation: 5947

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

Upvotes: 4

Andomar
Andomar

Reputation: 238086

Omit the parenthesis:

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

Upvotes: 100

Related Questions