CODEWITHSUNDEEP
sqlmany-to-many
jason
jason

Reputation: 7174

Many to many SQL

I have an SQL which has user, question , and answer . I want to have a junction table which holds the rate of a question. Many users can rate many questions. Can you tell me what is wrong with this SQL? Here is the SQL :

 CREATE TABLE user
(
user_id int(4) NOT NULL,
user_name varchar(255) NOT NULL,
PRIMARY KEY (user_id)
);

CREATE TABLE question
(
question_id int(4) NOT NULL,
question_text longtext NOT NULL,
user_id int(4) NOT NULL,
PRIMARY KEY (question_id),
FOREIGN KEY (user_id) REFERENCES user(user_id)
);

CREATE TABLE answer
(
answer_id int(4) NOT NULL,
user_id int(4)NOT NULL,
question_id int(4) NOT NULL,
PRIMARY KEY (answer_id),
FOREIGN KEY (user_id) REFERENCES user (user_id),
FOREIGN KEY (question_id) REFERENCES question (question_id)
);

CREATE TABLE rate
(
user_id int(4) REFERENCES user NOT NULL,
question_id int(4) REFERENCES question NOT NULL,
rate int(4) NOT NULL
)

Upvotes: 0

Views: 223

Answers (2)

a1ex07
a1ex07

Reputation: 37382

In addition to syntax mistakes outlined by Dan Bracuk, I'd say some unique constraints are missed from the model.

I believe 1 user can answer 1 question only once, then answer should have unique key on (user_id, question_id). The same for rate , user_id,question_id should form unique constraint / PK . Also, normally username is unique as well.

In case you want to allow the same user to answer or rank the same question or more than once , then date_time attribute is missed in answer and rate .

Upvotes: 1

Dan Bracuk
Dan Bracuk

Reputation: 20804

In your rate table, you have to set up foreign keys on separate lines like you did in the other tables.

Upvotes: 2

Related Questions