|
Subject:
|
MySql tables
|
|
Posted By:
|
godhsf80
|
Post Date:
|
11/25/2003 9:05:14 PM
|
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),
);
|
|
Reply By:
|
nikolai
|
Reply Date:
|
11/26/2003 12:27:37 PM
|
In MySQL you need to be using the InnoDB table type. The default type is MyISAM, if I remember correctly. As a developer, the best you can do with MySQL and the default table type is to write your code to ONLY insert valid values into your tables. Don't insert something into a table that shouldn't be there.
If you'd still like the database engine to enforce these constraints, read the manual:
http://www.mysql.com/doc/en/InnoDB_foreign_key_constraints.html
Take care,
Nik
http://www.bigaction.org/
|
|
