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

Table 4.6 Column Descriptions for INVENTORY Table

Column Name Data Type PK or FK Reference Table

InventoryID INT PK

ItemID INT FK ITEM

QOH INT

Price Money

he following code can be used to create the table INVENTORY. In the SQL statement, you
need to add the code for the foreign key constraint.

CREATE TABLE INVENTORY
(

InventoryID INT PRIMARY KEY,

ItemID INT,

QOH INT DEFAULT 0 CHECK(QOH >= 0) NOT NULL,

Price MONEY DEFAULT 0 CHECK (Price >= 0) NOT NULL,

CONSTRAINT Inventory_ItemId_fk

FOREIGN KEY (ItemID)

REFERENCES ITEM (ItemID)

)

he table deinition of ORDERS is given in Table 4.7.
To create the table ORDERS, use the following SQL statement:

CREATE TABLE ORDERS
(

OrderID INT PRIMARY KEY,

CustomerID INT NOT NULL,

OrderDate DATETIME NOT NULL,

CONSTRAINT Orders_CustomerId_fk

FOREIGN KEY (CustomerID)

REFERENCES CUSTOMER (CustomerID)

)

he table deinition for SHIPPING is shown in Table 4.8.
In Table 4.8, there are two foreign key columns. To implement these two foreign keys, you
need to specify two foreign key constraints. he SQL statement used to create the table SHIPPING
is given below:

Table 4.7 Column Descriptions for ORDERS Table

Column Name Data Type PK or FK Reference Table

OrderID INT PK

CustomerID INT FK CUSTOMER

OrderDate DATETIME