Directly execute SQL (create table, insert into fields...) to create a collection and modify fields

Help
1 
/*
  数据集表: organizations
  用途：存储机构元信息
	
  部分字段需要在 Directus 中进行配置
	- 关系
		m2o
			parent 父级机构
	- 文件
		picture
			logo LOGO图片
*/
CREATE TABLE `organizations` (
	`id` INT (10) UNSIGNED NOT NULL AUTO_INCREMENT,
	`organization_id` int(10) unsigned NOT NULL,
  `lang` varchar(10) COLLATE utf8mb4_bin NOT NULL DEFAULT 'cn' COMMENT '语言 Dropdown',
	`name` VARCHAR (255) COLLATE utf8mb4_bin DEFAULT NULL COMMENT '名称',
	`slug` VARCHAR (255) COLLATE utf8mb4_bin DEFAULT NULL COMMENT '别名',
	`parent` int(10) unsigned DEFAULT NULL COMMENT '父级机构',
	`description` TEXT COLLATE utf8mb4_bin COMMENT '描述',
	`logo` char(36) COLLATE utf8mb4_bin DEFAULT NULL COMMENT 'LOGO',
	`logo_option` json DEFAULT NULL COMMENT 'Logo 选项 Checkboxes',
	`website` VARCHAR (255) COLLATE utf8mb4_bin DEFAULT NULL COMMENT '机构网站',
	`name_full_cn` VARCHAR (255) COLLATE utf8mb4_bin DEFAULT NULL COMMENT '中文全称',
	`name_full_en` VARCHAR (255) COLLATE utf8mb4_bin DEFAULT NULL COMMENT '英文全称',
	`link` VARCHAR (255) COLLATE utf8mb4_bin DEFAULT NULL,
	`count` INT (10) UNSIGNED DEFAULT '0',
	`taxonomy` VARCHAR (255) COLLATE utf8mb4_bin DEFAULT 'organization',
	PRIMARY KEY (`id`),
	UNIQUE KEY `uk_organization_lang` (`organization_id`,`lang`),
	KEY `organizations_parent_foreign` (`parent`),
  KEY `organizations_logo_foreign` (`logo`),
  CONSTRAINT `organizations_logo_foreign` FOREIGN KEY (`logo`) REFERENCES `directus_files` (`id`) ON DELETE SET NULL,
  CONSTRAINT `organizations_parent_foreign` FOREIGN KEY (`parent`) REFERENCES `organizations` (`id`) ON DELETE SET NULL
) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4 COLLATE = utf8mb4_bin;

INSERT INTO `directus`.`directus_fields` (`collection`, `field`, `special`, `interface`, `options`, `display`, `display_options`, `readonly`, `hidden`, `sort`, `width`, `translations`, `note`, `conditions`, `required`, `group`, `validation`, `validation_message`) VALUES ('organizations', 'id', NULL, NULL, NULL, NULL, NULL, 0, 1, 1, 'full', NULL, NULL, NULL, 0, NULL, NULL, NULL);
INSERT INTO `directus`.`directus_fields` (`collection`, `field`, `special`, `interface`, `options`, `display`, `display_options`, `readonly`, `hidden`, `sort`, `width`, `translations`, `note`, `conditions`, `required`, `group`, `validation`, `validation_message`) VALUES ('organizations', 'organization_id', NULL, 'input', '{\"min\": 1}', NULL, NULL, 0, 0, 2, 'half', NULL, '机构 ID', NULL, 0, NULL, NULL, NULL);
INSERT INTO `directus`.`directus_fields` (`collection`, `field`, `special`, `interface`, `options`, `display`, `display_options`, `readonly`, `hidden`, `sort`, `width`, `translations`, `note`, `conditions`, `required`, `group`, `validation`, `validation_message`) VALUES ('organizations', 'lang', NULL, 'select-dropdown', '{\"choices\": [{\"text\": \"中文\", \"value\": \"cn\"}, {\"text\": \"英文\", \"value\": \"en\"}]}', 'labels', '{\"choices\": [{\"text\": \"中文\", \"value\": \"cn\"}, {\"text\": \"英文\", \"value\": \"en\"}]}', 0, 0, 3, 'half', NULL, '语言', NULL, 0, NULL, NULL, NULL);
INSERT INTO `directus`.`directus_fields` (`collection`, `field`, `special`, `interface`, `options`, `display`, `display_options`, `readonly`, `hidden`, `sort`, `width`, `translations`, `note`, `conditions`, `required`, `group`, `validation`, `validation_message`) VALUES ('organizations', 'name', NULL, NULL, NULL, NULL, NULL, 0, 0, 4, 'full', NULL, '名称', NULL, 0, NULL, NULL, NULL);
INSERT INTO `directus`.`directus_fields` (`collection`, `field`, `special`, `interface`, `options`, `display`, `display_options`, `readonly`, `hidden`, `sort`, `width`, `translations`, `note`, `conditions`, `required`, `group`, `validation`, `validation_message`) VALUES ('organizations', 'slug', NULL, NULL, NULL, NULL, NULL, 0, 0, 5, 'full', NULL, '别名', NULL, 0, NULL, NULL, NULL);
INSERT INTO `directus`.`directus_fields` (`collection`, `field`, `special`, `interface`, `options`, `display`, `display_options`, `readonly`, `hidden`, `sort`, `width`, `translations`, `note`, `conditions`, `required`, `group`, `validation`, `validation_message`) VALUES ('organizations', 'description', NULL, NULL, NULL, NULL, NULL, 0, 0, 7, 'full', NULL, '描述', NULL, 0, NULL, NULL, NULL);
INSERT INTO `directus`.`directus_fields` (`collection`, `field`, `special`, `interface`, `options`, `display`, `display_options`, `readonly`, `hidden`, `sort`, `width`, `translations`, `note`, `conditions`, `required`, `group`, `validation`, `validation_message`) VALUES ('organizations', 'logo_option', NULL, 'select-multiple-checkbox', '{\"choices\": [{\"text\": \"圆形 Logo\", \"value\": \"round_logo\"}, {\"text\": \"增加白色背景\", \"value\": \"bg_white\"}, {\"text\": \"增加黑色背景\", \"value\": \"bg_black\"}, {\"text\": \"额外边框\", \"value\": \"extra_border\"}]}', NULL, NULL, 0, 0, 9, 'full', NULL, NULL, NULL, 0, NULL, NULL, NULL);
INSERT INTO `directus`.`directus_fields` (`collection`, `field`, `special`, `interface`, `options`, `display`, `display_options`, `readonly`, `hidden`, `sort`, `width`, `translations`, `note`, `conditions`, `required`, `group`, `validation`, `validation_message`) VALUES ('organizations', 'website', NULL, NULL, NULL, NULL, NULL, 0, 0, 10, 'full', NULL, '机构网站', NULL, 0, NULL, NULL, NULL);
INSERT INTO `directus`.`directus_fields` (`collection`, `field`, `special`, `interface`, `options`, `display`, `display_options`, `readonly`, `hidden`, `sort`, `width`, `translations`, `note`, `conditions`, `required`, `group`, `validation`, `validation_message`) VALUES ('organizations', 'name_full_cn', NULL, NULL, NULL, NULL, NULL, 0, 0, 11, 'full', NULL, '中文全称', NULL, 0, NULL, NULL, NULL);
INSERT INTO `directus`.`directus_fields` (`collection`, `field`, `special`, `interface`, `options`, `display`, `display_options`, `readonly`, `hidden`, `sort`, `width`, `translations`, `note`, `conditions`, `required`, `group`, `validation`, `validation_message`) VALUES ('organizations', 'name_full_en', NULL, NULL, NULL, NULL, NULL, 0, 0, 12, 'full', NULL, '英文全称：例如「Stanford University」的全称为「Leland Stanford Junior University」，则需要在这里定义，若全称与机构名称相同，则此处留空', NULL, 0, NULL, NULL, NULL);
INSERT INTO `directus`.`directus_fields` (`collection`, `field`, `special`, `interface`, `options`, `display`, `display_options`, `readonly`, `hidden`, `sort`, `width`, `translations`, `note`, `conditions`, `required`, `group`, `validation`, `validation_message`) VALUES ('organizations', 'link', NULL, NULL, NULL, NULL, NULL, 0, 1, 13, 'full', NULL, NULL, NULL, 0, NULL, NULL, NULL);
INSERT INTO `directus`.`directus_fields` (`collection`, `field`, `special`, `interface`, `options`, `display`, `display_options`, `readonly`, `hidden`, `sort`, `width`, `translations`, `note`, `conditions`, `required`, `group`, `validation`, `validation_message`) VALUES ('organizations', 'count', NULL, NULL, NULL, NULL, NULL, 0, 1, 14, 'full', NULL, NULL, NULL, 0, NULL, NULL, NULL);
INSERT INTO `directus`.`directus_fields` (`collection`, `field`, `special`, `interface`, `options`, `display`, `display_options`, `readonly`, `hidden`, `sort`, `width`, `translations`, `note`, `conditions`, `required`, `group`, `validation`, `validation_message`) VALUES ('organizations', 'taxonomy', NULL, NULL, NULL, NULL, NULL, 0, 1, 15, 'full', NULL, NULL, NULL, 0, NULL, NULL, NULL);
INSERT INTO `directus`.`directus_fields` (`collection`, `field`, `special`, `interface`, `options`, `display`, `display_options`, `readonly`, `hidden`, `sort`, `width`, `translations`, `note`, `conditions`, `required`, `group`, `validation`, `validation_message`) VALUES ('organizations', 'parent', 'm2o', 'select-dropdown-m2o', '{\"template\": \"{{name}}\"}', 'related-values', '{\"template\": \"{{name}}\"}', 0, 0, 6, 'full', NULL, '父级机构', NULL, 0, NULL, NULL, NULL);
INSERT INTO `directus`.`directus_fields` (`collection`, `field`, `special`, `interface`, `options`, `display`, `display_options`, `readonly`, `hidden`, `sort`, `width`, `translations`, `note`, `conditions`, `required`, `group`, `validation`, `validation_message`) VALUES ('organizations', 'logo', 'file', 'file-image', NULL, 'image', NULL, 0, 0, 8, 'full', NULL, '机构 Logo：尺寸必须为正方形', NULL, 0, NULL, NULL, NULL);

INSERT INTO `directus`.`directus_presets` (`bookmark`, `user`, `role`, `collection`, `search`, `layout`, `layout_query`, `layout_options`, `refresh_interval`, `filter`, `icon`, `color`) VALUES (NULL, NULL, NULL, 'organizations', NULL, NULL, '{\"tabular\": {\"fields\": [\"organization_id\", \"lang\", \"name\", \"slug\", \"logo\", \"name_full_cn\", \"name_full_en\"]}}', '{\"tabular\": {\"widths\": {\"lang\": 94.765625, \"logo\": 139.80078125, \"name\": 160, \"slug\": 160, \"organization_id\": 152.375}, \"spacing\": \"comfortable\"}}', NULL, NULL, 'bookmark', NULL);

INSERT INTO `directus`.`directus_relations` (`many_collection`, `many_field`, `one_collection`, `one_field`, `one_collection_field`, `one_allowed_collections`, `junction_field`, `sort_field`, `one_deselect_action`) VALUES ('organizations', 'parent', 'organizations', NULL, NULL, NULL, NULL, NULL, 'nullify');
INSERT INTO `directus`.`directus_relations` (`many_collection`, `many_field`, `one_collection`, `one_field`, `one_collection_field`, `one_allowed_collections`, `junction_field`, `sort_field`, `one_deselect_action`) VALUES ('organizations', 'logo', 'directus_files', NULL, NULL, NULL, NULL, NULL, 'nullify');

I first manually created a collection and field in directus to observe the changes in the database, and found that directus_field and directus_relations had changed. Then I manually deleted the collection and directly executed the above SQL, expecting to get the same effect, but the following errors occurred for the parent, logo and other m2o fields:

af6a524c-9c90-4656-a07a-7054661202b1
af6a524c-9c90-4656-a07a-7054661202b11588×436 33.8 KB

What I want to ask is whether the same effect can be achieved by directly executing SQL？

Thanks for your answers

2

I have solved this bug。

After executing the SQL, you need to click collection in the settings, refresh the page, and then click content.

I don’t know the reason, but it’s solved.

3

This most definitely shouldn’t be expected behaviour; creating via SDK or rest also introduces this error. It should be configured as per the payload. Why would I then have to go into every single field with a relation and click / refresh?

If anyone ever solves this, please ping me. It’s been like this for years.