首页
学习
活动
专区
圈层
工具
发布
社区首页 >问答首页 >我尝试创建一个基于JSON文件的DB模式

问我尝试创建一个基于JSON文件的DB模式
EN

Stack Overflow用户
提问于 2019-04-23 21:59:05
回答 1查看 1.1K关注 0票数 0

我正在尝试创建一个基于JSON文件的DB,但是我弄乱了键和引用。你们能看看create语句有什么问题吗?json文件基本上是来自人们的评论或评论,这个json文件中有7个response对象和1个cursor对象。JSON文件:

代码语言:javascript
复制
{
   "cursor": {
      "prev": null,
      "hasNext": false,
      "next": "1542275432008325:1:0",
      "hasPrev": false,
      "total": null,
      "id": "1542275432008325:1:0",
      "more": false
   },
   "code": 0,
   "response": [
      {
         "editableUntil": "2018-11-22T11:20:37",
         "dislikes": 0,
         "numReports": 0,
         "likes": 1,
         "message": "<p>A sinking pound, resignations by the bucketful railroading through a so-called agreement nobody wants. For the good of the country Mrs. May 'go and go now'.</p>",
         "id": "4196155749",
         "createdAt": "2018-11-15T11:20:37",
         "author": {
            "username": "mnaid-233509073ed3432027d48b1a83f5fbd2",
            "about": "",
            "name": "baggiebuoy",
            "disable3rdPartyTrackers": true,
            "isPowerContributor": false,
            "joinedAt": "2017-05-03T10:40:52",
            "profileUrl": "https://disqus.com/by/mnaid-233509073ed3432027d48b1a83f5fbd2/",
            "url": "",
            "location": "",
            "isPrivate": false,
            "signedUrl": "",
            "isPrimary": true,
            "isAnonymous": false,
            "id": "250728493",
            "avatar": {
               "small": {
                  "permalink": "https://disqus.com/api/users/avatars/mnaid-233509073ed3432027d48b1a83f5fbd2.jpg",
                  "cache": "//a.disquscdn.com/1541535443/images/noavatar32.png"
               },
               "isCustom": false,
               "permalink": "https://disqus.com/api/users/avatars/mnaid-233509073ed3432027d48b1a83f5fbd2.jpg",
               "cache": "//a.disquscdn.com/1541535443/images/noavatar92.png",
               "large": {
                  "permalink": "https://disqus.com/api/users/avatars/mnaid-233509073ed3432027d48b1a83f5fbd2.jpg",
                  "cache": "//a.disquscdn.com/1541535443/images/noavatar92.png"
               }
            }
         },
         "media": [],
         "isSpam": false,
         "isDeletedByAuthor": false,
         "isDeleted": false,
         "parent": null,
         "isApproved": true,
         "isFlagged": false,
         "raw_message": "A sinking pound, resignations by the bucketful railroading through a so-called agreement nobody wants. For the good of the country Mrs. May 'go and go now'.",
         "isHighlighted": false,
         "canVote": false,
         "thread": "7046384220",
         "forum": "expressandstar",
         "points": 1,
         "moderationLabels": [],
         "isEdited": true,
         "sb": false
      },
      {
         "editableUntil": "2018-11-22T10:37:59",
         "dislikes": 0,
         "numReports": 0,
         "likes": 0,
         "message": "<p>This could be heaven, or this could be hell.</p>",
         "id": "4196048572",
         "createdAt": "2018-11-15T10:37:59",
         "author": {
            "username": "mnaid-29e1c59be16c852670e3be302e8c303b",
            "about": "",
            "name": "Mordecai",
            "disable3rdPartyTrackers": false,
            "isPowerContributor": false,
            "joinedAt": "2017-05-03T13:01:02",
            "profileUrl": "https://disqus.com/by/mnaid-29e1c59be16c852670e3be302e8c303b/",
            "url": "",
            "location": "",
            "isPrivate": false,
            "signedUrl": "",
            "isPrimary": true,
            "isAnonymous": false,
            "id": "250739494",
            "avatar": {
               "small": {
                  "permalink": "https://disqus.com/api/users/avatars/mnaid-29e1c59be16c852670e3be302e8c303b.jpg",
                  "cache": "//a.disquscdn.com/1541535443/images/noavatar32.png"
               },
               "isCustom": false,
               "permalink": "https://disqus.com/api/users/avatars/mnaid-29e1c59be16c852670e3be302e8c303b.jpg",
               "cache": "//a.disquscdn.com/1541535443/images/noavatar92.png",
               "large": {
                  "permalink": "https://disqus.com/api/users/avatars/mnaid-29e1c59be16c852670e3be302e8c303b.jpg",
                  "cache": "//a.disquscdn.com/1541535443/images/noavatar92.png"
               }
            }
         },
         "media": [],
         "isSpam": false,
         "isDeletedByAuthor": false,
         "isDeleted": false,
         "parent": 4196013534,
         "isApproved": true,
         "isFlagged": false,
         "raw_message": "This could be heaven, or this could be hell.",
         "isHighlighted": false,
         "canVote": false,
         "thread": "7046384220",
         "forum": "expressandstar",
         "points": 0,
         "moderationLabels": [],
         "isEdited": false,
         "sb": false
      },
      {
         "editableUntil": "2018-11-22T10:36:50",
         "dislikes": 0,
         "numReports": 0,
         "likes": 0,
         "message": "<p>The crappest of crap deals.<br>Will never get through Parliament.</p><p>You are the weakest link May, goodbye.</p>",
         "id": "4196044068",
         "createdAt": "2018-11-15T10:36:50",
         "author": {
            "username": "mnaid-29e1c59be16c852670e3be302e8c303b",
            "about": "",
            "name": "Mordecai",
            "disable3rdPartyTrackers": false,
            "isPowerContributor": false,
            "joinedAt": "2017-05-03T13:01:02",
            "profileUrl": "https://disqus.com/by/mnaid-29e1c59be16c852670e3be302e8c303b/",
            "url": "",
            "location": "",
            "isPrivate": false,
            "signedUrl": "",
            "isPrimary": true,
            "isAnonymous": false,
            "id": "250739494",
            "avatar": {
               "small": {
                  "permalink": "https://disqus.com/api/users/avatars/mnaid-29e1c59be16c852670e3be302e8c303b.jpg",
                  "cache": "//a.disquscdn.com/1541535443/images/noavatar32.png"
               },
               "isCustom": false,
               "permalink": "https://disqus.com/api/users/avatars/mnaid-29e1c59be16c852670e3be302e8c303b.jpg",
               "cache": "//a.disquscdn.com/1541535443/images/noavatar92.png",
               "large": {
                  "permalink": "https://disqus.com/api/users/avatars/mnaid-29e1c59be16c852670e3be302e8c303b.jpg",
                  "cache": "//a.disquscdn.com/1541535443/images/noavatar92.png"
               }
            }
         },
         "media": [],
         "isSpam": false,
         "isDeletedByAuthor": false,
         "isDeleted": false,
         "parent": null,
         "isApproved": true,
         "isFlagged": false,
         "raw_message": "The crappest of crap deals.\nWill never get through Parliament.\n\nYou are the weakest link May, goodbye.",
         "isHighlighted": false,
         "canVote": false,
         "thread": "7046384220",
         "forum": "expressandstar",
         "points": 0,
         "moderationLabels": [],
         "isEdited": false,
         "sb": false
      },
      {
         "editableUntil": "2018-11-22T10:28:13",
         "dislikes": 0,
         "numReports": 0,
         "likes": 0,
         "message": "<p>We are all just prisoners here of our own device. ?</p>",
         "id": "4196013534",
         "createdAt": "2018-11-15T10:28:13",
         "author": {
            "username": "mnaid-42ba513c42a0fd6558aa44b1de658140",
            "about": "",
            "name": "chaffwolf",
            "disable3rdPartyTrackers": true,
            "isPowerContributor": false,
            "joinedAt": "2017-12-20T16:45:10",
            "profileUrl": "https://disqus.com/by/mnaid-42ba513c42a0fd6558aa44b1de658140/",
            "url": "",
            "location": "",
            "isPrivate": false,
            "signedUrl": "",
            "isPrimary": true,
            "isAnonymous": false,
            "id": "274657487",
            "avatar": {
               "small": {
                  "permalink": "https://disqus.com/api/users/avatars/mnaid-42ba513c42a0fd6558aa44b1de658140.jpg",
                  "cache": "//a.disquscdn.com/1541535443/images/noavatar32.png"
               },
               "isCustom": false,
               "permalink": "https://disqus.com/api/users/avatars/mnaid-42ba513c42a0fd6558aa44b1de658140.jpg",
               "cache": "//a.disquscdn.com/1541535443/images/noavatar92.png",
               "large": {
                  "permalink": "https://disqus.com/api/users/avatars/mnaid-42ba513c42a0fd6558aa44b1de658140.jpg",
                  "cache": "//a.disquscdn.com/1541535443/images/noavatar92.png"
               }
            }
         },
         "media": [],
         "isSpam": false,
         "isDeletedByAuthor": false,
         "isDeleted": false,
         "parent": 4195981074,
         "isApproved": true,
         "isFlagged": false,
         "raw_message": "We are all just prisoners here of our own device. ?",
         "isHighlighted": false,
         "canVote": false,
         "thread": "7046384220",
         "forum": "expressandstar",
         "points": 0,
         "moderationLabels": [],
         "isEdited": false,
         "sb": false
      },
      {
         "editableUntil": "2018-11-22T10:27:03",
         "dislikes": 0,
         "numReports": 0,
         "likes": 0,
         "message": "<p>Should be no deal end off.<br>Nobody could possibly be the winner so it's simple leave and that's it .<br>Their will always be wannabes who think they can do better in it for only their own powers not for the general country or the people of it .,</p>",
         "id": "4196012501",
         "createdAt": "2018-11-15T10:27:03",
         "author": {
            "username": "mnaid-90ecce8d5dad4396f681182cb470872c",
            "about": "",
            "name": "wanderer in eire",
            "disable3rdPartyTrackers": true,
            "isPowerContributor": false,
            "joinedAt": "2017-05-12T20:20:43",
            "profileUrl": "https://disqus.com/by/mnaid-90ecce8d5dad4396f681182cb470872c/",
            "url": "",
            "location": "",
            "isPrivate": false,
            "signedUrl": "",
            "isPrimary": true,
            "isAnonymous": false,
            "id": "251694793",
            "avatar": {
               "small": {
                  "permalink": "https://disqus.com/api/users/avatars/mnaid-90ecce8d5dad4396f681182cb470872c.jpg",
                  "cache": "//a.disquscdn.com/1541535443/images/noavatar32.png"
               },
               "isCustom": false,
               "permalink": "https://disqus.com/api/users/avatars/mnaid-90ecce8d5dad4396f681182cb470872c.jpg",
               "cache": "//a.disquscdn.com/1541535443/images/noavatar92.png",
               "large": {
                  "permalink": "https://disqus.com/api/users/avatars/mnaid-90ecce8d5dad4396f681182cb470872c.jpg",
                  "cache": "//a.disquscdn.com/1541535443/images/noavatar92.png"
               }
            }
         },
         "media": [],
         "isSpam": false,
         "isDeletedByAuthor": false,
         "isDeleted": false,
         "parent": null,
         "isApproved": true,
         "isFlagged": false,
         "raw_message": "Should be no deal end off.\nNobody could possibly be the winner so it's simple leave and that's it .\nTheir will always be wannabes who think they can do better in it for only their own powers not for the general country or the people of it .,",
         "isHighlighted": false,
         "canVote": false,
         "thread": "7046384220",
         "forum": "expressandstar",
         "points": 0,
         "moderationLabels": [],
         "isEdited": false,
         "sb": false
      },
      {
         "editableUntil": "2018-11-22T10:26:42",
         "dislikes": 0,
         "numReports": 0,
         "likes": 0,
         "message": "<p>Damned if she does, damned if she doesn't. All for a cause she does not believe in.<br>She will go down in history whatever happens. <br>The question is: who better to lead the country at such an important juncture??<br>Answers on a postcard!</p>",
         "id": "4196012237",
         "createdAt": "2018-11-15T10:26:42",
         "author": {
            "username": "mnaid-42ba513c42a0fd6558aa44b1de658140",
            "about": "",
            "name": "chaffwolf",
            "disable3rdPartyTrackers": true,
            "isPowerContributor": false,
            "joinedAt": "2017-12-20T16:45:10",
            "profileUrl": "https://disqus.com/by/mnaid-42ba513c42a0fd6558aa44b1de658140/",
            "url": "",
            "location": "",
            "isPrivate": false,
            "signedUrl": "",
            "isPrimary": true,
            "isAnonymous": false,
            "id": "274657487",
            "avatar": {
               "small": {
                  "permalink": "https://disqus.com/api/users/avatars/mnaid-42ba513c42a0fd6558aa44b1de658140.jpg",
                  "cache": "//a.disquscdn.com/1541535443/images/noavatar32.png"
               },
               "isCustom": false,
               "permalink": "https://disqus.com/api/users/avatars/mnaid-42ba513c42a0fd6558aa44b1de658140.jpg",
               "cache": "//a.disquscdn.com/1541535443/images/noavatar92.png",
               "large": {
                  "permalink": "https://disqus.com/api/users/avatars/mnaid-42ba513c42a0fd6558aa44b1de658140.jpg",
                  "cache": "//a.disquscdn.com/1541535443/images/noavatar92.png"
               }
            }
         },
         "media": [],
         "isSpam": false,
         "isDeletedByAuthor": false,
         "isDeleted": false,
         "parent": null,
         "isApproved": true,
         "isFlagged": false,
         "raw_message": "Damned if she does, damned if she doesn't. All for a cause she does not believe in.\nShe will go down in history whatever happens. \nThe question is: who better to lead the country at such an important juncture??\nAnswers on a postcard!",
         "isHighlighted": false,
         "canVote": false,
         "thread": "7046384220",
         "forum": "expressandstar",
         "points": 0,
         "moderationLabels": [],
         "isEdited": false,
         "sb": false
      },
      {
         "editableUntil": "2018-11-22T09:50:32",
         "dislikes": 0,
         "numReports": 0,
         "likes": 0,
         "message": "<p>Theresa May is using The Eagles Hotel California lyrics as the inspiration for her Brexit plan.</p><p>You can check out any time you like, but you can never leave.</p>",
         "id": "4195981074",
         "createdAt": "2018-11-15T09:50:32",
         "author": {
            "username": "mnaid-e82a88d937e60267fd2c866b01131ada",
            "about": "",
            "name": "Olly the cat",
            "disable3rdPartyTrackers": false,
            "isPowerContributor": false,
            "joinedAt": "2017-05-03T10:27:23",
            "profileUrl": "https://disqus.com/by/mnaid-e82a88d937e60267fd2c866b01131ada/",
            "url": "",
            "location": "",
            "isPrivate": false,
            "signedUrl": "",
            "isPrimary": true,
            "isAnonymous": false,
            "id": "250727584",
            "avatar": {
               "small": {
                  "permalink": "https://disqus.com/api/users/avatars/mnaid-e82a88d937e60267fd2c866b01131ada.jpg",
                  "cache": "//a.disquscdn.com/1541535443/images/noavatar32.png"
               },
               "isCustom": false,
               "permalink": "https://disqus.com/api/users/avatars/mnaid-e82a88d937e60267fd2c866b01131ada.jpg",
               "cache": "//a.disquscdn.com/1541535443/images/noavatar92.png",
               "large": {
                  "permalink": "https://disqus.com/api/users/avatars/mnaid-e82a88d937e60267fd2c866b01131ada.jpg",
                  "cache": "//a.disquscdn.com/1541535443/images/noavatar92.png"
               }
            }
         },
         "media": [],
         "isSpam": false,
         "isDeletedByAuthor": false,
         "isDeleted": false,
         "parent": null,
         "isApproved": true,
         "isFlagged": false,
         "raw_message": "Theresa May is using The Eagles Hotel California lyrics as the inspiration for her Brexit plan.\n\nYou can check out any time you like, but you can never leave.",
         "isHighlighted": false,
         "canVote": false,
         "thread": "7046384220",
         "forum": "expressandstar",
         "points": 0,
         "moderationLabels": [],
         "isEdited": false,
         "sb": false
      }
   ]
}

SQL语句:

代码语言:javascript
复制
CREATE TABLE cursor(
code DOUBLE NOT NULL,
hasNext VARCHAR(50) NOT NULL,
next VARCHAR(50) NOT NULL,
hasPrev VARCHAR(50) NOT NULL,
id VARCHAR(50) NOT NULL,
more VARCHAR(50) NOT NULL,
PRIMARY KEY (hasNext),
FOREIGN KEY (code) REFERENCES dbjson(code)
)


CREATE TABLE small(
isCustom VARCHAR(50) NOT NULL,
permalink VARCHAR(100) NOT NULL,
cache VARCHAR(100) NOT NULL,
PRIMARY KEY (permalink),
FOREIGN KEY (username) REFERENCES avatar(isCustom)
)


CREATE TABLE moderationLabels(
editableUntil VARCHAR(50) NOT NULL,
FOREIGN KEY (code) REFERENCES response(editableUntil)
)


CREATE TABLE large(
isCustom VARCHAR(50) NOT NULL,
permalink VARCHAR(100) NOT NULL,
cache VARCHAR(100) NOT NULL,
PRIMARY KEY (permalink),
FOREIGN KEY (username) REFERENCES avatar(isCustom)
)


CREATE TABLE response(
code DOUBLE NOT NULL,
editableUntil VARCHAR(50) NOT NULL,
dislikes DOUBLE NOT NULL,
numReports DOUBLE NOT NULL,
likes DOUBLE NOT NULL,
message VARCHAR(200) NOT NULL,
id VARCHAR(50) NOT NULL,
createdAt VARCHAR(50) NOT NULL,
isSpam VARCHAR(50) NOT NULL,
isDeletedByAuthor VARCHAR(50) NOT NULL,
isDeleted VARCHAR(50) NOT NULL,
isApproved VARCHAR(50) NOT NULL,
isFlagged VARCHAR(50) NOT NULL,
raw_message VARCHAR(200) NOT NULL,
isHighlighted VARCHAR(50) NOT NULL,
canVote VARCHAR(50) NOT NULL,
thread VARCHAR(50) NOT NULL,
forum VARCHAR(50) NOT NULL,
points DOUBLE NOT NULL,
isEdited VARCHAR(50) NOT NULL,
sb VARCHAR(50) NOT NULL,
PRIMARY KEY (editableUntil),
FOREIGN KEY (code) REFERENCES dbjson(code)
)


CREATE TABLE author(
editableUntil VARCHAR(50) NOT NULL,
username VARCHAR(50) NOT NULL,
about VARCHAR(50) NOT NULL,
name VARCHAR(50) NOT NULL,
disable3rdPartyTrackers VARCHAR(50) NOT NULL,
isPowerContributor VARCHAR(50) NOT NULL,
joinedAt VARCHAR(50) NOT NULL,
profileUrl VARCHAR(100) NOT NULL,
url VARCHAR(50) NOT NULL,
location VARCHAR(50) NOT NULL,
isPrivate VARCHAR(50) NOT NULL,
signedUrl VARCHAR(50) NOT NULL,
isPrimary VARCHAR(50) NOT NULL,
isAnonymous VARCHAR(50) NOT NULL,
id DOUBLE NOT NULL,
PRIMARY KEY (username),
FOREIGN KEY (code) REFERENCES response(editableUntil)
)


CREATE TABLE avatar(
username VARCHAR(50) NOT NULL,
isCustom VARCHAR(50) NOT NULL,
permalink VARCHAR(100) NOT NULL,
cache VARCHAR(100) NOT NULL,
PRIMARY KEY (isCustom),
FOREIGN KEY (editableUntil) REFERENCES author(username)
)


CREATE TABLE media(
editableUntil VARCHAR(50) NOT NULL,
FOREIGN KEY (code) REFERENCES response(editableUntil)
)


CREATE TABLE dbjson(
code DOUBLE NOT NULL,
PRIMARY KEY (code))
EN

回答 1

Stack Overflow用户

发布于 2019-04-23 23:47:56

定义外键时,必须先定义父表,然后才能定义引用父表的子表。您的表是按照这样的顺序定义的,即在创建父表之前,多个表引用其父表。

下一个问题:cursor是MySQL中的reserved keyword。您必须像这样用反勾号分隔它:

代码语言:javascript
复制
CREATE TABLE `cursor`(
...

下一个问题:几个表在表中不存在的列上声明了一个外键。例如,author具有:

代码语言:javascript
复制
FOREIGN KEY (code) REFERENCES response(editableUntil)

但是author表没有名为code的列。该列必须存在于表中,然后才能在其上声明外键。表avatar、small、moderationLabels、large和media中也存在同样的问题。

票数 1
EN
页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接:

https://stackoverflow.com/questions/55812970

复制
相关文章

相似问题

领券
问题归档专栏文章快讯文章归档关键词归档开发者手册归档开发者手册 Section 归档