Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

Hi there I am fairly new to sql development. I am current following the head first into mysql book and is on chapter 6. On chapter 6 there is a table in third normal form but it does not contains the code for the schema so I am trying to do it for practice. I have most of the table down but when I try to add the constrain to the bridge table contact_interest, i get error. I checked the code and the type matched so I am not sure what I am doing wrong. Thank you for your help.

CREATE TABLE interest
(
    interest_id INT AUTO_INCREMENT PRIMARY KEY,
    interest VARCHAR(50)
);

CREATE TABLE my_contacts
(
    contact_id INT AUTO_INCREMENT PRIMARY KEY,
    last_name VARCHAR(50),
    first_name VARCHAR(50),
    phone CHAR(10),
    email VARCHAR(255),
    gender CHAR(1),
    birthday DATE,
    prof_id INT,
    zip_code VARCHAR(5),
    status_id INT,
    CONSTRAINT FOREIGN KEY profession_my_contacts_fk (prof_id) REFERENCES profession(prof_id),
    CONSTRAINT FOREIGN KEY zip_code_my_contacts_fk (zip_code) REFERENCES zip_code(zip_code),
    CONSTRAINT FOREIGN KEY status_my_contacts_fk (status_id) REFERENCES status(status_id) 
);

CREATE TABLE contact_interest
(
    contact_id INT,
    interest_id INT,
    PRIMARY KEY (contact_id, interest_id)
);

ALTER TABLE contact_interest
ADD CONSTRAINT FOREIGN KEY my_contacts_contact_interest_fk (contact_id) REFERENCES my_contacts(contact_id) ON DELETE CASCADE,
ADD CONSTRAINT FOREIGN KEY interests_contact_interest_fk (interest_id) REFERENCES interests(interest_id) ON DELETE CASCADE;

enter image description here

share|improve this question
    
Is the engine the same?Size?Is there data in some referenced table? –  Mihai Dec 9 '13 at 22:19
    
I am unfamiliar with engine. I did check that the primary key and the foreign key is both integer(11). There is currently no data in all three tables. –  user3084408 Dec 9 '13 at 23:04
    
Just run SHOW CREATE TABLE yourTableName; for each table,it will output a statement and at the end will be ENGINE=InnoDB or something similar. –  Mihai Dec 9 '13 at 23:11
    
You have table interest , but you are trying to reference interests in your add constraint ... Names must be the same. –  a1ex07 Dec 9 '13 at 23:18
    
awesome, a1ex07 found the problem. It runs fine after I removed the s. Thanks everyone for being so helpful. –  user3084408 Dec 9 '13 at 23:55
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Browse other questions tagged or ask your own question.