325dK5ti

1CREATE TABLE role_groups (
2  `role_group_id` int(11) NOT NULL `AUTO_INCREMENT`,
3  `name` varchar(20),
4  `description` varchar(200),
5  PRIMARY KEY (`role_group_id`)
6) ENGINE=InnoDB;
7
8CREATE TABLE IF NOT EXISTS `roles` (
9  `role_id` int(11) NOT NULL AUTO_INCREMENT,
10  `name` varchar(50),
11  `description` varchar(200),
12  PRIMARY KEY (`role_id`)
13) ENGINE=InnoDB;
14
15create table role_map (
16  `role_map_id` int not null `auto_increment`,
17  `role_id` int not null,
18  `role_group_id` int not null,
19  primary key(`role_map_id`),
20  foreign key(`role_id`) references roles(`role_id`),
21  foreign key(`role_group_id`) references role_groups(`role_group_id`)
22) engine=InnoDB; 
23     
24SELECT COUNT(*) FROM Child LEFT OUTER JOIN Parent ON Child.FK = Parent.PK 
25WHERE Parent.PK IS NULL; 
26     
27ALTER TABLE `dbname`.`tablename` CHANGE `fieldname` `fieldname` int(10) UNSIGNED NULL; 
28     
29CREATE TABLE users(
30   id INT AUTO_INCREMENT PRIMARY KEY,
31   username VARCHAR(40));
32
33CREATE TABLE userroles(
34   id INT AUTO_INCREMENT PRIMARY KEY,
35   user_id INT NOT NULL,
36   FOREIGN KEY(user_id) REFERENCES users(id)); 
37     
38---------------------------------------------------------------------------------------------------------+
39| Level   | Code | Message                                                                                                                                                                                                                                 |
40+---------+------+--------------------------------------------------------------------------                          --------------------------------------------------------------------------------------------                          ---------------+
41| Warning |  150 | Create table 'fakeDatabase/exampleTable' with foreign key constraint failed. There is no index in the referenced table where the referenced columns appear as the first columns.
42|
43| Error   | 1005 | Can't create table 'exampleTable' (errno:150)                                                                                                                                                                           |
44+---------+------+--------------------------------------------------------------------------                          --------------------------------------------------------------------------------------------                          ---------------+ 
45     
46CREATE TABLE user (
47  userId int PRIMARY KEY AUTO_INCREMENT,
48  username varchar(30) NOT NULL
49) ENGINE=InnoDB; 
50     
51CREATE TABLE product (
52  id int PRIMARY KEY AUTO_INCREMENT,
53  userId int,
54  FOREIGN KEY fkProductUser1(userId) REFERENCES **u**ser(userId)
55) ENGINE=InnoDB; 
56     
57CREATE TABLE product (
58  id int PRIMARY KEY AUTO_INCREMENT,
59  userId int,
60  FOREIGN KEY fkProductUser1(userId) REFERENCES User(userId)
61) ENGINE=InnoDB;