Rhett Claypool Rhett Claypool - 17 days ago 6
MySQL Question

Do you need to use constraints when actually building the tables in sql?

CREATE TABLE ORDER (
OrderNumber Int AUTO_INCREMENT NOT NULL,
CustomerID Int NOT NULL,
ItemID Char(25) NOT NULL,
CONSTRAINT OrderPK PRIMARY KEY(OrderNumber),
CONSTRAINT OrderFK FOREIGN KEY(CustomerID)
REFERENCES CUSTOMER(CustomerID),
CONSTRAINT OrderFK FOREIGN KEY(ItemID)
REFERENCES ITEM(ItemID)
);

Answer

Here is an example of two tables to set up FK's:

I type in:

CREATE TABLE `users`
(   `userId` INT AUTO_INCREMENT PRIMARY KEY,
    `name` VARCHAR(100) NOT NULL
)ENGINE=InnoDB;

CREATE TABLE `accounts`
(   `acctId` INT AUTO_INCREMENT PRIMARY KEY,
    `userId` INT NOT NULL,
    `acctType` CHAR(5) NOT NULL,
    CONSTRAINT `c_acct_2_user` FOREIGN KEY (`userId`) REFERENCES `users`(`userId`)
)ENGINE=InnoDB;

The server interprets it as:

CREATE TABLE `users` (
  `userId` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(100) NOT NULL,
  PRIMARY KEY (`userId`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `accounts` (
  `acctId` int(11) NOT NULL AUTO_INCREMENT,
  `userId` int(11) NOT NULL,
  `acctType` char(5) NOT NULL,
  PRIMARY KEY (`acctId`),
  KEY `c_acct_2_user` (`userId`),
  CONSTRAINT `c_acct_2_user` FOREIGN KEY (`userId`) REFERENCES `users` (`userId`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

The line above that states

KEY `c_acct_2_user` (`userId`),

is the server creating a helper key from the referencing to the referenced. Often those helper keys are not needed to be made for you depending on what other keys you have available in your table.

Please see the MySQL Manual Page entitled Using FOREIGN KEY Constraints.

Edit1

To help OP in request in comments:

CREATE SCHEMA testMonday07;
USE testMonday07;

CREATE TABLE `CUSTOMER`
(   `CustomerID` INT AUTO_INCREMENT PRIMARY KEY
    -- other columns etc
);
CREATE TABLE `ITEM`
(   `ItemID` CHAR(25) PRIMARY KEY
    -- other columns etc
);
CREATE TABLE `ORDER` 
(   -- literally your table name. Awful name for a table as it is a Reserved Word
    `OrderNumber` INT AUTO_INCREMENT PRIMARY KEY,
    `CustomerID` INT NOT NULL,
    `ItemID` CHAR(25) NOT NULL,
    CONSTRAINT `some_name_1` FOREIGN KEY (`CustomerID`) REFERENCES `CUSTOMER`(`CustomerID`),
    CONSTRAINT `sdome_name_2` FOREIGN KEY (`ItemID`) REFERENCES `ITEM`(`ItemID`)
);

DROP SCHEMA testMonday07; -- clean up, poof, all gone