CODEWITHSUNDEEP
mysqlkey
user3214262
user3214262

Reputation: 91

mysql foreign key insert

Using this two tables i'm trying to create a query to insert a new course, but i'm using a foreign key and I don't know how to pass that users(table) id into courses(table) id.

I'm trying to get something like this

I'm completely lost, on how to insert data into a table that contains a foreign key, what i'm trying to do is:

  1. first a person can register(user table)

2.Then the user will be available to add courses ( im using the foreign key to identify the courses for a specific user)

Users table

id | username |
---------------
1  | test     |

Courses table

coursesid | coursesname | id(same id that in users table)

1         | courses test| 1

The Create Table commands are

CREATE TABLE `users` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `username` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  `password` char(64) COLLATE utf8_unicode_ci NOT NULL,
  `salt` char(16) COLLATE utf8_unicode_ci NOT NULL, 
  PRIMARY KEY (`id`),
  UNIQUE KEY `username` (`username`) 
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=1;



CREATE TABLE `course` (
  `courseid` int(11) NOT NULL AUTO_INCREMENT,
  `coursename` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  `id` int(11) NOT NULL,
  UNIQUE KEY (`courseid`)
FOREIGN KEY (od) REFERENCES users(id)
)

Upvotes: 1

Views: 141

Answers (1)

AppLend
AppLend

Reputation: 1694

You can use subquery for find user id, e.g.:

insert into course
(courseid, coursename, id)
values
(1, 'test courses',
    (SELECT id FROM users where username = 'test')
)
;

Of cause, if you know user id, you can insert directly this id:

insert into course
(courseid, coursename, id)
values
(1, 'test courses', 1)
;

Additional query for insert just last user id:

insert into course
(courseid, coursename, id)
values
(1, 'test courses',
    (SELECT max(id) FROM users)
)
;

Upvotes: 1

Related Questions