CODEWITHSUNDEEP
mysqlcreate-table
user17451
user17451

Reputation: 665

How can I add a foreign key when creating a new table?

I have these two CREATE TABLE statements: 

CREATE TABLE GUEST (
  id int(15) not null auto_increment PRIMARY KEY,
  GuestName char(25) not null
);

CREATE TABLE PAYMENT (
  id int(15) not null auto_increment
  Foreign Key(id) references GUEST(id),
  BillNr int(15) not null
);

What is the problem in the second statement? It did not create a new table.

Upvotes: 16

Views: 36572

Answers (5)

Thirumurugan K
Thirumurugan K

Reputation: 1

create table course(ccode int(2) primary key,course varchar(10));

create table student1(rollno int(5) primary key,name varchar(10),coursecode int(2) not 
null,mark1 int(3),mark2 int(3),foreign key(coursecode) references course(ccode));

Upvotes: 0

Hapkido
Hapkido

Reputation: 1441

I will suggest having a unique key for the payment table. On it's side, the foreign key should not be auto_increment as it refer to an already existing key.

CREATE TABLE GUEST(
    id int(15) not null auto_increment PRIMARY KEY, 
    GuestName char(25) not null
)  ENGINE=INNODB;

CREATE TABLE PAYMENT(
    id int(15)not null auto_increment, 
    Guest_id int(15) not null, 
    INDEX G_id (Guest_id), 
    Foreign Key(Guest_id) references GUEST(id),
    BillNr int(15) not null
)  ENGINE=INNODB;

Upvotes: 8

DOK
DOK

Reputation: 32831

The answer to your question is almost the same as the answer to this one .

You need to specify in the table containing the foreign key the name of the table containing the primary key, and the name of the primary key field (using "references").

This has some code showing how to create foreign keys by themselves, and in CREATE TABLE.

Here's one of the simpler examples from that:

CREATE TABLE parent (id INT NOT NULL,
   PRIMARY KEY (id)
) ENGINE=INNODB;
CREATE TABLE child (id INT, parent_id INT,
   INDEX par_ind (parent_id),
   FOREIGN KEY (parent_id) REFERENCES parent(id)
   ON DELETE CASCADE
) ENGINE=INNODB;

Upvotes: 13

Piyush Sharma
Piyush Sharma

Reputation: 1

There should be space between int(15) and not null

Upvotes: -1

mattoc
mattoc

Reputation: 712

Make sure you're using the InnoDB engine for either the database, or for both tables. From the MySQL Reference:

For storage engines other than InnoDB, MySQL Server parses the FOREIGN KEY syntax in CREATE TABLE statements, but does not use or store it.

Upvotes: 0

Related Questions