Make unique key as a Foreign key?

Hello i want to make unique key as a foreign key but i got an error i already checked this question

mY member Table

CREATE TABLE IF NOT EXISTS `member` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `username` varchar(50) NOT NULL,
  `password` varchar(50) NOT NULL,
  `email` varchar(100) NOT NULL,
  `contact` varchar(100) NOT NULL,
  `facebook` varchar(100) NOT NULL,
  `cnic` varchar(100) NOT NULL,
  `expertise` varchar(100) NOT NULL,
  `com_code` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `username` (`username`)
) ENGINE=InnoDB

and my profile image tabl

create table profileimage(
imageid int primary key not null,
username varchar(100) foreign key references member(username) 
ON DELETE CASCADE
 ON UPDATE CASCADE,
imagepath varchar(255) not null
);

and error i faced
#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 'foreign key references member(username) ON DELETE CASCADE ON UPDATE CASCADE,' at line 6

Yes, you can have a FOREIGN KEY constraint that references a column with a UNIQUE constraint.

The syntax error you get is because you didn't provide a datatype for the column. It should be the same type as the referenced column: username varchar(50).

Another issue is the syntax for foreign constraints. If you put them "inline", i.e immediately after the column definition, you skip the FOREIGN KEY and have only REFERENCES:

-- Don't do this in MySQL !!!!
CREATE TABLE profileimage
( imageid int  NOT NULL PRIMARY KEY,
  username varchar(50)
     REFERENCES member (username) 
     ON DELETE CASCADE
     ON UPDATE CASCADE,
  imagepath varchar(255) NOT NULL
) ENGINE=InnoDB ;

or you put them separately, with the FOREIGN KEY keyword:

-- Do this in MySQL
CREATE TABLE profileimage
( imageid int NOT NULL PRIMARY KEY,
  username varchar(50),
  imagepath varchar(255) NOT NULL,
  FOREIGN KEY (username)
     REFERENCES member (username) 
     ON DELETE CASCADE
     ON UPDATE CASCADE
) ENGINE=InnoDB ;

Big surprise: Both syntaxes work in MySQL with a catch: the first is parsed for syntax validity (and it passes) but the foreign key is not created! (you get a warning)

The second syntax works fine.

StackExchange Database Administrators Q#151099, answer score: 6