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;
ALTER TABLE User
(
ADD CONSTRAINT userPropertie
FOREIGN KEY(properties)
REFERENCES Properties(ID)
)
The tables were created properly, but the ALTER TABLE doesn't work. cant 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
This question is related to
mysql
sql
database
phpmyadmin
Omit the parenthesis:
ALTER TABLE User
ADD CONSTRAINT userProperties
FOREIGN KEY(properties)
REFERENCES Properties(ID)
alter table User
add constraint userProperties
foreign key (properties)
references Properties(ID)
ALTER TABLE `User`
ADD CONSTRAINT `user_properties_foreign`
FOREIGN KEY (`properties`)
REFERENCES `Properties` (`ID`)
ON DELETE NO ACTION
ON UPDATE NO ACTION;
Source: Stackoverflow.com