Can someone tell me what is the error being caused in my SQL code? DROP DATABASE

Question

Can someone tell me what is the error being caused in my SQL code?

DROP DATABASE IF EXISTS WebStore;
CREATE DATABASE WebStore;
USE WebStore;

CREATE TABLE Categories (
CatID INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
Name VARCHAR(20) NOT NULL,
Descr VARCHAR(120) NOT NULL,
IconURL VARCHAR(64) NOT NULL,
PRIMARY KEY (CatID)
);

CREATE TABLE Products (
ProdID INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
Name VARCHAR(20) NOT NULL,
Category INTEGER UNSIGNED,
Descr VARCHAR(120) NOT NULL,
Price FLOAT NOT NULL,
Stock INTEGER UNSIGNED NOT NULL,
IconURL VARCHAR(64) NOT NULL,
PRIMARY KEY (ProdID),
CONSTRAINT FK_Cat FOREIGN KEY FK_Cat (Category)
    REFERENCES Categories (CatID)
    ON DELETE RESTRICT
    ON UPDATE RESTRICT
);

INSERT INTO Categories VALUES(null,"Laptops","Small computers you can carry","http://localhost/images/laptop.jpg");
SET @laptops := LAST_INSERT_ID();
INSERT INTO Categories VALUES(null,"Desktops","Big computers you cannot carry","http://localhost/images/desktop.jpg");
SET @desktops := LAST_INSERT_ID();
INSERT INTO Categories VALUES(null,"Tablets"," Flat things you lose frequently","http://localhost/images/tablet.jpg");
SET @tablets := LAST_INSERT_ID();

INSERT INTO Products VALUES(null,"DULL 1200",@desktops,"A big dull machine",1290.99,23,"http://localhost/images/dull1200.jpg");
INSERT INTO Products VALUES(null,"8P Totalo",@desktops,"Almost as big as the DULL",990.99,2,"http://localhost/images/8ptotalo.jpg");
INSERT INTO Products VALUES(null,"LaNuveau Bingster",@desktops,"Comes in blue and red",690.99,12,"http://localhost/images/lanuveaubingster.jpg");

INSERT INTO Products VALUES(null,"DULL 122",@laptops,"Small, portable and useless",422.99,4,"http://localhost/images/dull122.jpg");
INSERT INTO Products VALUES(null,"8P Tootsie",@laptops,"Sticky and too heavy",559.99,12,"http://localhost/images/8ptootsie.jpg");
INSERT INTO Products VALUES(null,"LaNuveau Shoobie XT",@laptops,"Weighs a ton but looks sharp",1690.99,122,"http://localhost/images/lanuveaushoobiext.jpg");

INSERT INTO Products VALUES(null,"Motor Roller 12",@tablets,"The only one with a oval screen",422.99,4,"http://localhost/images/mr12.jpg");
INSERT INTO Products VALUES(null,"SamSings OffKey",@tablets,"Needs duct tape to run",559.99,2,"http://localhost/images/samsingsOK.jpg");
INSERT INTO Products VALUES(null,"jPet 12",@tablets,"The first that kinda sorta works",16290.99,722,"http://localhost/images/jpet12.jpg");



CREATE TABLE Customers (
CustID INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
FirstName VARCHAR(40) NOT NULL,
LastName VARCHAR(40) NOT NULL,
Address VARCHAR(60) NOT NULL,
City VARCHAR(40) NOT NULL,
State VARCHAR(20) NOT NULL,
Zip VARCHAR(5) NOT NULL,
PRIMARY KEY (CustID)
);

INSERT INTO Customers VALUES(null,"Gerald","Bostock","1234 TAAB Drive","St. Cleve","FL","12345");
INSERT INTO Customers VALUES(null,"Suzy","Creamcheese","8722 Zappa Road","Paris","TX","75460");


CREATE TABLE Shopping_Cart (
CustomerID VARCHAR (150) NOT NULL,
ProductID VARCHAR(100) NOT NULL,
Quantity INTEGER UNSIGNED,
PRIMARY KEY (CustID, ProdID),
CONSTRAINT FK_Cust FOREIGN KEY (CustomerID)
REFERENCES Customers (CustID)
CONSTRAINT FK_Prod FOREIGN KEY FK_Prod (ProductID)
REFERENCES Products (ProdID)
ON DELETE RESTRICT
ON UPDATE RESTRICT
);

INSERT INTO Shopping_Cart VALUES ("Gerald Bostock", DULL122, 2);
INSERT INTO Shopping_Cart VALUES ("Gerald Bostock", jPet12, 1);

Explanation / Answer

The following are the errors in the given SQL code:

The NOTNULL datatype defines that the NULL value cannot be stored in a column. When the NULL value is inserted into the table the insertion fails.

1. The primary key constraint cannot be NULL. For the Categories table the CatID is primary key and NULL value is inserted as the primary key. So, the insertion fails.

2. Similarly, for the Products table the primary key is ProdID and the NULL value is inserted as the primary key. So, the insertion fails.

3. In Products Table, the CatID is referenced as foreign key. But, the CatID value in the Category table is inserted as NULL.

4. Similarly, for the Customers table the primary key is CustID and the NULL value is inserted as the primary key. So, the insertion fails.

5. In the shopping cart table the primary key CustID and ProdID are used as primary keys. A table should bave only one primary key.

6. The Customers and Products table cannot be referenced because the primary key values for both the tables are NULL.

Related Questions

Navigate

• Browse (All)
• Browse C
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.