Creating a table syntax errors in sql-CodePudding

I'm trying to create a SQL table for a school assignment. I'm required to name the constraints, and I am receiving a syntax error when creating my table

SELECT * FROM demo;

CREATE TABLE PRODUCT
(
    ProductID NUMBER,
    ProductDescription VARCHAR(200),
    CONSTRAINT ProductPK PRIMARY KEY (ProductID)
);

CREATE TABLE ITEM 
(
    ItemNum NUMBER
    CONSTRAINT Inull NOT NULL
    CONSTRAINT ItemPK PRIMARY KEY(ItemNum),
    ItemDesc VARCHAR(200)
);

The syntax error is as follows:

near "(": syntax error

It is likely something simple, but this is my first time writing SQL.

CodePudding user response：

You have a few errors .. One, you are missing commas after ItemNum NUMBER and CONSTRAINT Inull NOT NULL

Additionally there are other ways to define NOT NULL, you technically don't need to use CONSTRAINT in traditional SQL.

And depending on the flavor of SQL you are using .. I like to use INT or INTEGER instead of NUMBER -- It's basically the same as NUMBER() with a few exceptions, but if you are using whole numbers I would use INT or INTEGER

SELECT * FROM demo;

create TABLE PRODUCT
(
    ProductID INT,
    ProductDescription VARCHAR(200),
    CONSTRAINT ProductPK PRIMARY KEY (ProductID)
);
CREATE TABLE ITEM 
(
    ItemNum INT NOT NULL,
    CONSTRAINT ItemPK PRIMARY KEY(ItemNum),
    ItemDesc VARCHAR(200)
);

UPDATE

And as @Schwern suggests .. Your query can be further simplified, as there really is no need for CONSTRAINTS here .. I would also recommend Auto Incrementing your IDs .. Thus removing the need for NOT NULL on the ID. This also negates the need to supply an ID with the INSERT query ..

SELECT * FROM demo;

create TABLE PRODUCT
(
    ProductID INT PRIMARY KEY AUTO_INCREMENT,
    ProductDescription VARCHAR(200)
);
CREATE TABLE ITEM 
(
    ItemNum INT PRIMARY KEY AUTO_INCREMENT,
    ItemDesc VARCHAR(200)
);