SQL - Syntax Error (Problems with PK and FK)

Question

I a want to fill a DB with SQL, but I get syntax errors. I think the problem is in PK and FK. Can you please take a look, what am I doing wrong in my approach.

Thank you very much!

SQL Code

CREATE TABLE TheOrder
(
    ordereditem VARCHAR(50) NOT NULL PRIMARY KEY,
    productid INTEGER NOT NULL,
    productwholesaler VARCHAR (50) NOT NULL
);

INSERT INTO TheOrder VALUES
('overwatch', 1, 'blizzard'),
('deathadder', 2, 'razer'),
('xboxcontroller', 3, 'microsoft'),
('kraken', 4, 'razer'),
('diablo3', 5, 'blizzard'),
('warcraft3remastered', 6, 'blizzard'),
('fallout3', 7, 'bethesda'),
('pixel', 8, 'google'),
('aspire5', 9, 'acer'),
('destiny2', 10 ,'activision');




CREATE TABLE TheStock
(
    counteditems INTEGER NOT NULL, 
    isInStock BOOLEAN NOT NULL,
    stockID INTEGER NOT NULL PRIMARY KEY,
    fkordereditem VARCHAR,
    CONSTRAINT fkordereditem FOREIGN KEY (fkordereditem) REFERENCES TheOrder(ordereditem)
);

INSERT INTO TheStock VALUES
('overwatch', 5, true, 1),
('deathadder', 2, true, 2),
('xboxcontroller', 0, false, 3),
('kraken', 0, false, 4),
('diablo3', 5, true, 5),
('warcraft3remastered', 0, false, 6),
('fallout3', 7, true, 7),
('pixel', 8, true, 8),
('aspire5', 0, false, 9),
('destiny2', 10 , true, 10);



CREATE TABLE TheReorder
(
    fkproductid VARCHAR,
    fkproductwholesaler VARCHAR,
    howmanydays INTEGER NOT NULL, 
    mobilenumber INTEGER PRIMARY KEY,
    postcode INTEGER
    CONSTRAINT fkproductid FOREIGN KEY (fkproductid) REFERENCES TheOrder(productid),
    CONSTRAINT fkproductwholesaler FOREIGN KEY (fkproductwholesaler) REFERENCES TheOrder (productwholesaler)
);

INSERT INTO TheReorder VALUES
('overwatch', 'blizzard', 0, 649494, 1020),
('deathadder', 'razer', 0, 498494, 1150),
('xboxcontroller', 'microsoft', 3, 948849, 71000),
('kraken', 'razer', 5, 249489, 32009),
('diablo3', 'blizzard', 0, 194984, 29000),
('warcraft3remastered', 'blizzard', 12, 398484, 11000),
('fallout3', 'bethesda', 0, 694895, 42132),
('pixel', 'google', 0, 873243, 9201),
('aspire5', 'acer', 15, 988564, 10020),
('destiny2', 'activision', 0, 745637, 10090);


CREATE TABLE TheCourier
(
    couriername VARCHAR(50) NOT NULL PRIMARY KEY,
    couriercompany VARCHAR (50) NOT NULL,
    courieravailable BOOLEAN NOT NULL
);

INSERT INTO TheCourier VALUES
('jon snow', 'the wall', false),
('ned stark', 'winterfell', false),
('elon musk', 'space x', true),
('yusaku maezawa', 'zozo', false),
('hokusai katsushika', 'the great wave', false),
('ragnar lothbrok', 'vikings', false),
('jax teller', 'sons of anarchy', true),
('harvey specter', 'post', true),
('michael corleone', 'ups', false),
('tommy shelby', 'fedex', false);


CREATE TABLE ThePenaltyInfo
(
    fkhowmanydays INTEGER,
    fkproductidpen INTEGER,
    feetopay INTEGER NOT NULL,
    currencie VARCHAR(50) NOT NULL,
    penaltyid INTEGER NOT NULL PRIMARY KEY,
    CONSTRAINT fkhowmanydays FOREIGN KEY (fkhowmanydays) REFERENCES TheReorder(howmanydays),
    CONSTRAINT fkproductidpen FOREIGN KEY (fkproductidpen) REFERENCES TheOrder(productid)
);

INSERT INTO ThePenaltyInfo VALUES
(0, 1, 0, 'euro', 1),
(0, 2, 0, 'euro', 2),
(3, 3, 50, 'usd', 3),
(5, 4, 90, 'usd', 4),
(0, 5, 0, 'ron', 5),
(12, 6, 30, 'ron', 6),
(0, 7, 0, 'euro', 7),
(0, 8, 0, 'euro', 8),
(15, 9, 800, 'euro', 9),
(0, 10, 0, 'euro', 10);



CREATE TABLE TheShipementList 
(
    fkcouriercompany VARCHAR,
    fkproductidship VARCHAR,
    shipementid INTEGER NOT NULL PRIMARY KEY,
    shipementaddress VARCHAR(50) NOT NULL,
    customernumber INTEGER NOT NULL,
    CONSTRAINT fkcouriercompany FOREIGN KEY (fkcouriercompany) REFERENCES TheCourier(couriercompany),
    CONSTRAINT fkproductidship FOREIGN KEY (fkproductidship) REFERENCES TheOrder(productid)
);

INSERT INTO TheShipementList VALUES
('space x', 1, 1, 'Palma de Mallorca', 1994007),
('space x', 2, 1, 'Palma de Mallorca', 1994007),
('space x', 3, 1, 'Palma de Mallorca', 1994007),
('space x', 4, 1, 'Palma de Mallorca', 1994007),
('space x', 5, 1, 'Palma de Mallorca', 1994007),
('space x', 6, 1, 'Palma de Mallorca', 1994007),
('space x', 7, 1, 'Palma de Mallorca', 1994007),
('space x', 8, 1, 'Palma de Mallorca', 1994007),
('space x', 9, 1, 'Palma de Mallorca', 1994007),
('space x', 10, 1, 'Palma de Mallorca',1994007);

ERROR MESSAGE

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ' CONSTRAINT FK FOREIGN KEY (fkordereditem) REFERENCES TheOrder(ordereditem) ' at line 6

Answer 1

I found the error. The assigned types have not been the same for the FK and for the referenced attribute.

CREATE TABLE TheOrder
(
    productid INTEGER NOT NULL PRIMARY KEY,
    ordereditem VARCHAR(50) NOT NULL,
    productwholesaler VARCHAR (50) NOT NULL
);

CREATE TABLE TheStock
(
    counteditems INTEGER NOT NULL, 
    isInStock BOOLEAN NOT NULL,
    stockID INTEGER NOT NULL PRIMARY KEY,
    testid INTEGER,
    CONSTRAINT testid FOREIGN KEY (testid) REFERENCES TheOrder(productid)
);