Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

MySQL Syntax in creating Foreign Key

Is this syntax correct in creating a Foreign Key?

create table department
(
  departmentID int not null auto_increment primary key,
  name varchar(30)
) type=InnoDB;

create table employee
(
  employeeID int not null auto_increment primary key,
  name varchar(80),
  job varchar(30),
  departmentID int not null references department(departmentID)
) type=InnoDB;
like image 734
Aaron Avatar asked Aug 08 '11 01:08

Aaron


People also ask

How do I manually create a foreign key in MySQL?

To add a foreign key, click the last row in the Foreign Key Name list. Enter a name for the foreign key and select the column or columns that you wish to index by checking the column name in the Column list. You can remove a column from the index by removing the check mark from the appropriate column.

How do I add a foreign key to a column in MySQL?

You can add foreign key constraint using CREATE TABLE or ALTER TABLE statements in SQL. Here's the syntax to create foreign key in MySQL. ALTER TABLE table_name ADD CONSTRAINT constraint_name FOREIGN KEY (foreign_key_name,...) REFERENCES parent_table(column_name,...);


2 Answers

It looks like MySQL accepts it (doesn't complain about the syntax) but the foreign key is not actually created.

To create this foreign key, run this command:

ALTER TABLE employee ADD CONSTRAINT fk_department FOREIGN KEY (departmentID) REFERENCES department (departmentID);
like image 156
Doug Avatar answered Oct 24 '22 04:10

Doug


create table employee
(
  employeeID int not null auto_increment primary key,
  name varchar(80),
  job varchar(30),
  departmentID int not null ADD CONSTRAINT fk_department FOREIGN KEY (departmentID) references department(departmentID)
) 
like image 20
user2725649 Avatar answered Oct 24 '22 04:10

user2725649