Database Development and Management with SQL - Cloud Database Development and Management - page 121

he SQL statement for creating the table PRODUCT is listed below:

CREATE TABLE PRODUCT
(

ProductID INT PRIMARY KEY,

ProductName VARCHAR(50),

)

For the tables ITEM, INVENTORY, SHIPPING, and ORDERS, the table deinition con-
tains foreign keys. To implement a foreign key, you need to specify a foreign key constraint in the
SQL statement.
Starting with the table ITEM, we will deine a foreign key. he ITEM table deinition is given
in Table 4.5.
Using the following SQL statement, you can create the table ITEM:

CREATE TABLE ITEM
(

ItemID INT PRIMARY KEY,

ProductID INT,

CPU VARCHAR(50),

RAM VARCHAR(50),

HardDrive VARCHAR(50),

OpticalDrive VARCHAR(50),

Monitor VARCHAR(50),

CONSTRAINT Item_ProductId_fk

FOREIGN KEY (ProductID)

REFERENCES PRODUCT (ProductID)

)

As indicated in the above SQL statement, the foreign key constraint name is Item_ProductId_
fk, which should be unique. he name of the foreign key column is ProductID. he entry after the
REFERENCES is the name of the parent table. he parent table is PRODUCT, and the primary
key in the parent table is ProductID.
After the table ITEM is created, you can create the table INVENTORY that has a foreign key
column ItemID. he table deinition is given in Table 4.6.

Table 4.5 Column Descriptions for ITEM Table

Column Name Data Type PK or FK Reference Table

ItemID INT PK

ProductID INT FK PRODUCT

CPU VARCHAR(50)

RAM VARCHAR(50)

HardDrive VARCHAR(50)

OpticalDrive VARCHAR(50)

Monitor VARCHAR(50)