I am creating a database where users can create a login. If they are logged in, they can submit data through a form and their data should match up to their primary key/login info if that makes sense.
There are two tables, Login_Info and Numbers. The primary key for the first table is UserID, and I placed UserId in the Numbers table too:
CREATE TABLE `Login_Info` (
`UserID` int(11) NOT NULL AUTO_INCREMENT PRIMARY KEY,
`Username` varchar(25),
`Password` varchar (25)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1;
CREATE TABLE `Numbers` (
`UserID` int(11),
`Savings` varchar(25),
`Goal1` varchar(25),
`Goal2` varchar(25),
`Goal3` varchar(25),
`Cost1` varchar(25),
`Cost2` varchar(25),
`Cost3` varchar(25),
`Weight1` varchar(25),
`Weight2` varchar(25),
`Weight3` varchar(25),
`TotalCost` varchar(25),
`PercentRound` varchar(25)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1;
To make UserID a foreign key, I created a new attribute in the second table called EntryNumber as the primary key then made UserId the foreign key:
CREATE TABLE `Numbers` (
`EntryNumber` int(11) NOT NULL AUTO_INCREMENT PRIMARY KEY,
`UserID` FOREIGN KEY REFERENCES Login_Info(UserID),
`Savings` varchar(25),
`Goal1` varchar(25),
`Goal2` varchar(25),
`Goal3` varchar(25),
`Cost1` varchar(25),
`Cost2` varchar(25),
`Cost3` varchar(25),
`Weight1` varchar(25),
`Weight2` varchar(25),
`Weight3` varchar(25),
`TotalCost` varchar(25),
`PercentRound` varchar(25)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1;
Now when I try to add the database, I get a message that the syntax for FOREIGN KEY REFERENCES is incorrect. I have tried many ways to add the FK but I always get a message that the FK syntax is not correct. I am not sure what my error may be here. I would appreciate if someone could take a look and let me know what element I’m missing in creating a FK through a PK used in another table, to create a link between the two tables.
You need to define the table as:
CREATE TABLE `Numbers` (
`EntryNumber` int(11) NOT NULL AUTO_INCREMENT PRIMARY KEY,
`UserID` int(11) not null,
`Savings` varchar(25),
`Goal1` varchar(25),
`Goal2` varchar(25),
`Goal3` varchar(25),
`Cost1` varchar(25),
`Cost2` varchar(25),
`Cost3` varchar(25),
`Weight1` varchar(25),
`Weight2` varchar(25),
`Weight3` varchar(25),
`TotalCost` varchar(25),
`PercentRound` varchar(25),
FOREIGN KEY (UserID) REFERENCES Login_Info(UserID)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1;