Here are the sql commands for my datbase table. Can you guys help me declare For

Question

Here are the sql commands for my datbase table. Can you guys help me declare Foreign keys and Primary keys?

Thanks!

/***** Procedure: Creates New Stored Procedure[dbo].[CREATE_ALL_TABLES]*****/

CREATE PROCEDURE [dbo].[CREATE_ALL_TABLES]
AS

BEGIN

IF NOT EXISTS(SELECT* FROM sys.Tables WHERE name='customer')

----------------------------------------------------------------------------
CREATE Table customer(
custID int(10) NOT NULL,
fName varchar(20)NOT NULL,
lName varchar(20)NOT NULL,
phone varchar(10)NOT NULL,
email varchar(50)NOT NULL,
DOB   DATE,
gender   varchar(1)NOT NULL,
CONSTRAINT PK_customer PRIMARY KEY(custID)
);

CREATE Table orders(
OID int(12) NOT NULL,
custID int(10) NOT NULL,
orderDt DATE,
shipID int(4)NOT NULL,
shipCost Number(5,2),
CONSTRAINT FK_orders REFERENCES PARENT_customer(custID)
CONSTRAINT FK_orders REFERENCES PARENT_shippingMethods(shipID)
);

CREATE Table orderItems(
OID int(12) NOT NULL,
prodID int(6) NOT NULL,
qty int(3)NOT NULL,
price Number(6,2),

CONSTRAINT PK_orderItems PRIMARY KEY(OID)
CONSTRAINT PK_orderItems PRIMARY KEY(prodID)
);

CREATE Table upSells(
prodID1 int(6) NOT NULL,
prodID2 int(6) NOT NULL,
percentage Number(6,2),

CONSTRAINT PK_upSells PRIMARY KEY(prodID1)
CONSTRAINT PK_upSells PRIMARY KEY(prodID2)
);

CREATE Table shippingAddress(
sID int(10) NOT NULL,
custID int(10) NOT NULL,
address1 varchar(30)NOT NULL,
address2 varchar(30)NOT NULL,
city   varchar(50)NOT NULL,
state   varchar(2)NOT NULL,
zip   varchar(10)NOT NULL,
primaryYN varchar(1 bit) NOT NULL,

CONSTRAINT PK_shippingAddress PRIMARY KEY(sID)
CONSTRAINT FK_shippingAddress REFERENCES PARENT_customer(custID)
);

CREATE Table shippingMethods(
shipID int(4) NOT NULL,
company varchar(30) NOT NULL,
method varchar(25) NOT NULL,
fRate Number(5,2),
vRate Number(5,2),
baseWeight Number(6,2),

CONSTRAINT PK_shippingMethods PRIMARY KEY(shipID)
);

CREATE Table product(
prodID int(6) NOT NULL,
prodName varchar(100) NOT NULL,
description varchar(500) NOT NULL,
rPrice Number(6,2),
catID   int(2) NOT NULL,
keywords varchar(100) NOT NULL,

CONSTRAINT PK_product PRIMARY KEY(prodID)
CONSTRAINT FK_product REFERENCES PARENT_category(catID)
);

CREATE Table crossSells(
prodID1 int(6) NOT NULL,
prodID2 int(6) NOT NULL,
percentage Number(3,2),

CONSTRAINT PK_crossSells PRIMARY KEY(prodID1)
CONSTRAINT PK_crossSells PRIMARY KEY(prodID2)
);

CREATE Table paymentInfo(
custID int(10) NOT NULL,
pName varchar(10) NOT NULL,
ccType   varchar(20) NOT NULL,
ccNum varchar(16) NOT NULL,
ccExpire DATE,
billAddress varchar(30) NOT NULL,
city   varchar(50) NOT NULL,
state   varchar(2) NOT NULL,
zip   varchar(10) NOT NULL,

CONSTRAINT PK_paymentInfo PRIMARY KEY(custID)
CONSTRAINT PK_paymentInfo PRIMARY KEY(pName)
);

CREATE Table department(
deptID int(2) NOT NULL,
name varchar(25) NOT NULL,
description   varchar(100) NOT NULL,

CONSTRAINT PK_department PRIMARY KEY(deptID)
);

CREATE Table category(
catID int(2) NOT NULL,
deptID int(2) NOT NULL,
name varchar(25) NOT NULL,
description   varchar(100) NOT NULL,

CONSTRAINT PK_category PRIMARY KEY(catID)
CONSTRAINT FK_category REFERENCES PARENT_department(deptID)
);

END

GO

Explanation / Answer

The stored procedure already have the primary and forieng keys declared.

Customer table:

Primary key:custID

Orders table:

2Forieng keys:CustID from customer table and

ShipID from shippingmethods table

order items table:

2primary keys:

OID and ProdID

Similarly the keywords PRIMARY KEY describes the primary key column and the REFERENCES keyword tells us the forkiegn key for the given table.

Related Questions

Navigate

• Browse (All)
• Browse H
• Subjects

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