Syntax error in SQL code with "invalid table name" and "missing right parthensis&quot-CodePudding

I have a school project due tonight and my team and I are struggling to figure this out. We compare to our professor's code but we cannot find the error. We keep getting "invalid table name" and "missing right parenthesis" We know the error is somewhere in the CONSTRAINT portion of the code. The User table is successfully created and throws no errors though all other tables do. Is anyone able to help point out this error? I know it's something with the syntax. The desired result will not have any errors when creating tables.

DROP TABLE "User";
DROP TABLE "UserFeed";
DROP TABLE "Post";
DROP TABLE "Story";
DROP TABLE "FactCheck";
DROP TABLE "Archive";


CREATE TABLE "User" (
    UserID  INT NOT NULL PRIMARY KEY ,
    Username VARCHAR(30) NOT NULL,
    FirstName VARCHAR(30) NOT NULL,
    LastName VARCHAR(30) NOT NULL,
    DateOfBirth DATE NOT NULL,
    Email   VARCHAR(50) NOT NULL);

CREATE TABLE UserFeed (
        UserFeedID      CHAR(10)    NOT NULL PRIMARY KEY,
        PostCount       INT         NOT NULL,
        FollowerCount   INT     NOT NULL,
        FollowingCount  INT     NOT NULL,
        UserID          INT     NOT NULL,
        CONSTRAINT  FK_UserUserFeed
        FOREIGN KEY (UserID)
        REFERENCES User(UserID)
    );

CREATE TABLE "FactCheck" (
    RevisionID  INT     PRIMARY KEY NOT NULL,
    TrueFalse   VARCHAR(1)  NOT NULL,
    UserFeedID  CHAR(10)    NOT NULL,
    CONSTRAINT  FK_UserFeedID
    FOREIGN KEY (UserFeedID)
    REFERENCES  UserFeed(UserFeedID)
    );

CREATE TABLE "Post" (
    PostID      CHAR(10)    PRIMARY KEY  NOT NULL,
    Caption     VARCHAR(300)    NULL,
    LikeCount   INT     NOT NULL,
    CommentCount    INT     NOT NULL,
    DatePosted  DATE        NULL,
    UserFeedID  CHAR(10)    NOT NULL,
    RevisionID  INT     NOT NULL,
    CONSTRAINT  FK_UserFeedID
    FOREIGN KEY (UserFeedID)
    REFERENCES  UserFeed(UserFeedID)
    CONSTRAINT  FK_RevisionID
    FOREIGN KEY (RevisionID)
    REFERENCES  FactCheck(RevisionID));

CREATE TABLE "Story" (
    StoryID     CHAR(10)    PRIMARY KEY NOT NULL,
    StoryCaption    VARCHAR(100)    NULL,
    Duration    INT     NOT NULL,
    UserFeedID  CHAR(10)    NOT NULL,
    StoryDate   DATE        NULL,
    RevisionID  INT     NOT NULL,
    CONSTRAINT  FK_UserFeedID
    FOREIGN KEY (UserFeedID)
    REFERENCES  UserFeed(UserFeedID)
    CONSTRAINT  FK_RevisionID
    FOREIGN KEY (RevisionID)
    REFERENCES  FactCheck(RevisionID)
    );

CREATE TABLE    "Archive" (
    ArchiveID   CHAR(10)    PRIMARY KEY NOT NULL,
    PostID      CHAR(10)    NOT NULL,
    StoryID     CHAR(10)    NOT NULL,
    CONSTRAINT  FK_PostID
    FOREIGN KEY (PostID)
    REFERENCES  Post(PostID)
    CONSTRAINT  FK_StoryID
    FOREIGN KEY (StoryID)
    REFERENCES  Story(StoryID)
    );

CodePudding user response：

A few things:

You generally don't need quotes around table names, unless you want them
User is a reserved word, so you should choose a different table name (or keep it quoted, and then quote it everywhere)
You need a comma between constraints, this is why you're getting the missing parentheses error
You need separate names for constraints, you can't reuse them

See below example:

CREATE TABLE Users ( --Changed to Users
    UserID  INT NOT NULL PRIMARY KEY ,
    Username VARCHAR(30) NOT NULL,
    FirstName VARCHAR(30) NOT NULL,
    LastName VARCHAR(30) NOT NULL,
    DateOfBirth DATE NOT NULL,
    Email   VARCHAR(50) NOT NULL
);

CREATE TABLE UserFeed (
        UserFeedID      CHAR(10)    NOT NULL PRIMARY KEY,
        PostCount       INT         NOT NULL,
        FollowerCount   INT     NOT NULL,
        FollowingCount  INT     NOT NULL,
        UserID          INT     NOT NULL,
        CONSTRAINT  FK_UserUserFeed
        FOREIGN KEY (UserID)
        REFERENCES Users(UserID) --Changed to Users
    );

CREATE TABLE FactCheck (
    RevisionID  INT     PRIMARY KEY NOT NULL,
    TrueFalse   VARCHAR(1)  NOT NULL,
    UserFeedID  CHAR(10)    NOT NULL,
    CONSTRAINT  FK_UserFeedID
    FOREIGN KEY (UserFeedID)
    REFERENCES  UserFeed(UserFeedID)
    );

CREATE TABLE Post (
    PostID      CHAR(10)    PRIMARY KEY  NOT NULL,
    Caption     VARCHAR(300)    NULL,
    LikeCount   INT     NOT NULL,
    CommentCount    INT     NOT NULL,
    DatePosted  DATE        NULL,
    UserFeedID  CHAR(10)    NOT NULL,
    RevisionID  INT     NOT NULL,
    CONSTRAINT  FK_UserFeedID2 --Added '2' so the name is unique
    FOREIGN KEY (UserFeedID)
    REFERENCES  UserFeed(UserFeedID), --Comma added
    CONSTRAINT  FK_RevisionID
    FOREIGN KEY (RevisionID)
    REFERENCES  FactCheck(RevisionID)
);

CREATE TABLE Story (
    StoryID     CHAR(10)    PRIMARY KEY NOT NULL,
    StoryCaption    VARCHAR(100)    NULL,
    Duration    INT     NOT NULL,
    UserFeedID  CHAR(10)    NOT NULL,
    StoryDate   DATE        NULL,
    RevisionID  INT     NOT NULL,
    CONSTRAINT  FK_UserFeedID3 --Added '3' so the name is unique
    FOREIGN KEY (UserFeedID)
    REFERENCES  UserFeed(UserFeedID), --Added comma
    CONSTRAINT  FK_RevisionID2 --Added '2' so the name is unique
    FOREIGN KEY (RevisionID)
    REFERENCES  FactCheck(RevisionID)
    );

CREATE TABLE    Archive (
    ArchiveID   CHAR(10)    PRIMARY KEY NOT NULL,
    PostID      CHAR(10)    NOT NULL,
    StoryID     CHAR(10)    NOT NULL,
    CONSTRAINT  FK_PostID
    FOREIGN KEY (PostID)
    REFERENCES  Post(PostID),
    CONSTRAINT  FK_StoryID
    FOREIGN KEY (StoryID)
    REFERENCES  Story(StoryID)
    );

CodePudding user response：

you dont need to put table names in ""