MySql tables

[hosefo81]

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),

);