Jump to content

MySql tables


hosefo81

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

);

Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

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.