Jump to content


Photo

MySql tables


  • Please log in to reply
No replies to this topic

#1 hosefo81

hosefo81
  • Members
  • Pip
  • Newbie
  • 9 posts

Posted 26 November 2003 - 02:05 AM

Below are a few of my mysql tables. I have problem with the sales_detail table. How can i make the prod_id in the sales_detail table reference the prod_id in the product table? i\'m confused as the sales_id and prod_id in sales details are primary key. Can primary key be reference? I have this problem because i once accidentally enter a prod_id in sales_details that does not exist in the product table and the database accepts it without error. How can i correct this situation? If yes, how can i alter the sales_details table?

CREATE TABLE PRODUCT
(
PROD_ID VARCHAR(5) NOT NULL,
PROD_NAME VARCHAR(20) NOT NULL,
PROD_DESC VARCHAR(20) NOT NULL,
PROD_PRICE DECIMAL NOT NULL,
PROD_CAT INT NOT NULL,
PRIMARY KEY(PROD_ID),
FOREIGN KEY(PROD_CAT) REFERENCES CATEGORY,
);
CREATE TABLE SALES
(
SALES_ID INT NOT NULL,
SALES_DATE DATE NOT NULL,
CUST_ID VARCHAR(5) NOT NULL,
EMP_ID VARCHAR(6) NOT NULL,
PRIMARY KEY(SALES_ID),
FOREIGN KEY (CUST_ID) REFERENCES CUSTOMER,
FOREIGN KEY (EMP_ID) REFERENCES EMPLOYEE,
);

CREATE TABLE SALES_DETAIL
(
SALES_ID INT NOT NULL,
PROD_ID VARCHAR(5) NOT NULL,
QUANTITY INT NOT NULL,
PRIMARY KEY (SALES_ID, PROD_ID),
);




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users