0

Can't create table ctfd.discorduser (errno: 150 "Foreign key constraint is incorrectly formed")

CREATE TABLE discorduser (
        id BIGINT NOT NULL AUTO_INCREMENT,
        username VARCHAR(128),
        discriminator INTEGER,
        avatar_hash VARCHAR(256),
        mfa_enabled BOOL,
        verified BOOL,
        email VARCHAR(256),
        PRIMARY KEY (id),
        UNIQUE (id),
        UNIQUE (id),
        FOREIGN KEY(username) REFERENCES users (name) ON DELETE CASCADE,
        CHECK (mfa_enabled IN (0, 1)),
        CHECK (verified IN (0, 1))
)

The models are defined like this:

class Users(db.Model):
    __tablename__ = "users"
    __table_args__ = (db.UniqueConstraint("id", "oauth_id"), {})
    # Core attributes
    id = db.Column(db.Integer, primary_key=True)
    oauth_id = db.Column(db.Integer, unique=True)
    # User names are not constrained to be unique to allow for official/unofficial teams.
    name = db.Column(db.String(128))
    password = db.Column(db.String(128))
    email = db.Column(db.String(128), unique=True)
    type = db.Column(db.String(80))
    secret = db.Column(db.String(128))

    # Supplementary attributes
    website = db.Column(db.String(128))
    affiliation = db.Column(db.String(128))
    country = db.Column(db.String(32))
    bracket = db.Column(db.String(32))
    hidden = db.Column(db.Boolean, default=False)
    banned = db.Column(db.Boolean, default=False)
    verified = db.Column(db.Boolean, default=False)

    # Relationship for Teams
    team_id = db.Column(db.Integer, db.ForeignKey("teams.id"))

    field_entries = db.relationship(
        "UserFieldEntries", foreign_keys="UserFieldEntries.user_id", lazy="joined"
    )

    created = db.Column(db.DateTime, default=datetime.datetime.utcnow)

    __mapper_args__ = {"polymorphic_identity": "user", "polymorphic_on": type}

    def __init__(self, **kwargs):
        super(Users, self).__init__(**kwargs)


class DiscordUser(db.Model):
    __tablename__ = "discorduser"
    __table_args__ = (db.UniqueConstraint("id"), {})

    # Core variables
    id = db.Column(db.BigInteger, primary_key=True, unique=True)  # Discord ID, int64
    # Discord Username 2-32 characters
    username = db.Column(db.String(128), db.ForeignKey("users.name", ondelete="CASCADE"))

What could be causing this? The username type is the same for both, and I am under the impression that Foreign Keys do not need to be constrained.

forpas
  • 160,666
  • 10
  • 38
  • 76
Peter Stenger
  • 165
  • 7
  • 1
    Provide CREATE TABLE for `users`. The column `name` must be `VARCHAR(128)` and `UNIQUE`. – Akina Nov 09 '21 at 05:47
  • Name is not unique. Can I tie the table to id instead? – Peter Stenger Nov 09 '21 at 05:50
  • 1
    *Can I tie the table to id instead?* You must, not can. And alter `discorduser` datatype accordingly. – Akina Nov 09 '21 at 05:50
  • If I make name unique, can I keep `discorduser` the same? – Peter Stenger Nov 09 '21 at 05:58
  • 1
    The column which you refer on must be uniquely indexed. if not then you will receive an error while trying to create the FK. And FK by the column of string type is not good idea - set the reference by `id` and obtain according name in a query with proper JOIN. – Akina Nov 09 '21 at 06:01
  • It is foreign **key**, so the other end must be a primary key or unique. – Ilja Everilä Nov 09 '21 at 06:03
  • A pretty exhaustive list of things that can cause that error for future reference: https://stackoverflow.com/questions/8434518/mysql-foreign-key-constraint-is-incorrectly-formed-error – Ilja Everilä Nov 09 '21 at 07:05

0 Answers0