Jump to content

Archived

This topic is now archived and is closed to further replies.

hosefo81

MySql tables

Recommended Posts

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

);

Share this post


Link to post
Share on other sites

×

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.