whats the problem?

Question

CREATE TABLE customer (customer_id CHAR(9) , name VARCHAR(20) , PRIMARY KEY(customer_id));
INSERT INTO customer VALUES ('123456789' , 'Yahel');
INSERT INTO customer VALUES ('987654321' , 'Gilad');

CREATE TABLE orders (item_id CHAR(4) , customer_id CHAR(9) , PRIMARY KEY(customer_id) , FOREIGN KEY(customer_id) REFERENCES customer(customer_id));
INSERT INTO orders VALUES ('6789' , '123456789');
INSERT INTO orders VALUES ('1234' , '123456789');
INSERT INTO orders VALUES ('4367' , '123456789');
INSERT INTO orders VALUES ('2314' , '987654321');
INSERT INTO orders VALUES ('1986' , '987654321');
INSERT INTO orders VALUES ('5467' , '987654321');

SELECT * FROM orders;

Answer 1

I'm not sure if you can have the same column as both Primary and Foreign key. Try making the item_id as Primary key in table 'orders' and see if it works.

Also share with us the error message you get after executing the query.

Answer 2

Avinesh , Thanks! its working now..
btw , i still dont get what is a foreign key is good for and what does it do and when you use it?

Answer 3

Foreign keys are used to establish relationship between two or more tables. It is somewhat like a parent child relationship. It also prevents you from doing unnecessary modifications because it might affect both the tables.

Answer 4

Avinesh well explained, thanks!

Answer 5

yahel you're welcome.