SQL CREATE TABLE CREATE TABLE With FOREIGN KEY
Example
Below you could find the table Employees with a reference to the table Cities.
CREATE TABLE Cities(
CityID INT IDENTITY(1,1) NOT NULL,
Name VARCHAR(20) NOT NULL,
Zip VARCHAR(10) NOT NULL
);
CREATE TABLE Employees(
EmployeeID INT IDENTITY (1,1) NOT NULL,
FirstName VARCHAR(20) NOT NULL,
LastName VARCHAR(20) NOT NULL,
PhoneNumber VARCHAR(10) NOT NULL,
CityID INT FOREIGN KEY REFERENCES Cities(CityID)
);
Here could you find a database diagram.
The column CityID of table Employees will reference to the column CityID of table Cities. Below you could find the syntax to make this.
CityID INT FOREIGN KEY REFERENCES Cities(CityID)
| Value | Meaning |
|---|---|
CityID | Name of the column |
int | type of the column |
FOREIGN KEY | Makes the foreign key (optional) |
REFERENCESCities(CityID) | Makes the reference to the table Cities column CityID |
Important: You couldn't make a reference to a table that not exists in the database. Be source to make first the table Cities and second the table Employees. If you do it vise versa, it will throw an error.
