rhC260we

1CREATE TABLE IF NOT EXISTS `testDb`.`users` (
2  `id` INT NOT NULL AUTO_INCREMENT,
3  `nickname` VARCHAR(255) NULL,
4  `first_name` VARCHAR(255) NULL,
5  `last_name` VARCHAR(255) NULL,
6  `e_mail` VARCHAR(255) NOT NULL,
7  `activated` TINYINT(1) NOT NULL DEFAULT 0,
8  `birth_date` DATE NULL,
9  `locked` TINYINT(1) NOT NULL DEFAULT 0,
10  `locked_date_time` DATETIME NULL,
11  `street` VARCHAR(255) NULL,
12  `street_number` VARCHAR(255) NULL,
13  `city` VARCHAR(255) NULL,
14  `postal_code` VARCHAR(255) NULL,
15  `country` VARCHAR(255) NULL,
16  `phone` VARCHAR(255) NULL,
17  PRIMARY KEY (`id`),
18  UNIQUE INDEX `user_id_UNIQUE` (`id` ASC)
19)
20ENGINE = InnoDB
21AUTO_INCREMENT = 1;
22
23
24CREATE TABLE IF NOT EXISTS `testDb`.`articles` (
25  `id` INT NOT NULL AUTO_INCREMENT,
26  `name` VARCHAR(255) NULL,
27  `description` VARCHAR(255) NULL,
28  `create_user` INT ZEROFILL NOT NULL,
29  `create_date_time` DATETIME NULL,
30  `last_modifie_user` INT ZEROFILL NOT NULL,
31  `last_modifie_date_time` DATETIME NULL,
32  PRIMARY KEY (`id`),
33  UNIQUE INDEX `article_id_UNIQUE` (`id` ASC),
34  INDEX `fk_articles_users1_idx` (`create_user` ASC),
35  INDEX `fk_articles_users2_idx` (`last_modifie_user` ASC)
36)
37ENGINE = InnoDB
38AUTO_INCREMENT = 1;
39
40
41ALTER TABLE `testDb`.`articles`
42  ADD CONSTRAINT `fk_articles_users1`
43    FOREIGN KEY (`create_user`)
44    REFERENCES `testDb`.`users` (`id`)
45    ON DELETE NO ACTION
46    ON UPDATE NO ACTION,
47  ADD CONSTRAINT `fk_articles_users2`
48    FOREIGN KEY (`last_modifie_user`)
49    REFERENCES `testDb`.`users` (`id`)
50    ON DELETE NO ACTION
51    ON UPDATE NO ACTION;
52	
53SHOW ENGINE innodb STATUS;
54	
55CREATE TABLE IF NOT EXISTS `articles` (
56  `id` INT NOT NULL AUTO_INCREMENT,
57  `name` VARCHAR(255) NULL,
58  `description` VARCHAR(255) NULL,
59  `create_user` INT ZEROFILL NOT NULL REFERENCES users(id),
60  `create_date_time` DATETIME NULL,
61  `last_modifie_user` INT ZEROFILL NOT NULL REFERENCES users(id),
62  `last_modifie_date_time` DATETIME NULL,
63  PRIMARY KEY (`id`),
64  INDEX `fk_articles_users1_idx` (`create_user` ASC),
65  INDEX `fk_articles_users2_idx` (`last_modifie_user` ASC)
66)
67ENGINE = InnoDB
68AUTO_INCREMENT = 1;
69	
70mysql> SHOW FULL COLUMNS FROM accounts;
71+----------+--------------+-------------------+------+-----+---------+----------+
72| Field    | Type         | Collation         | Null | Key | Default | Extra    |
73+----------+--------------+-------------------+------+-----+---------+----------|
74| id       | int(11)      | NULL              | NO   | PRI | NULL    | auto_inc |
75| name     | varchar(255) | utf8_bin          | YES  |     | NULL    |          |
76| email    | varchar(255) | latin1_swedish_ci | YES  |     | NULL    |          |
77...
78	
79ALTER TABLE t1 MODIFY
80    col1 VARCHAR(5)
81      CHARACTER SET latin1
82      COLLATE latin1_swedish_ci;