CODEWITHSUNDEEP
mysqlsqlphpmyadmin
Trevor Jex
Trevor Jex

Reputation: 275

How to set up two MySQL data fields so one or the other can be null but not both?

I have 5 MySQL data fields for a votes table:

You can vote in a poll or vote for a post. If you vote in a poll, the post/person field will be null. If you vote for a post, the vote field will be null.

I want to set up the table so it will allow you to make either the post id or vote id null, but not both. I'm using phpmyadmin to manage my database.

Any ideas?

Upvotes: 0

Views: 448

Answers (2)

Mr. Radical
Mr. Radical

Reputation: 1838

I have to agree with jmilloy above the best thing to do is to create separate tables. This an example how this would work:

Table structure and sample data:

CREATE TABLE post (
    post_id INT AUTO_INCREMENT PRIMARY KEY,
    vote_id INT
 );

CREATE TABLE poll (
    poll_id INT AUTO_INCREMENT PRIMARY KEY,
    vote_id INT
  );

CREATE TABLE voter(
  vote_id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(30)
  );

INSERT INTO post (vote_id) VALUES(1),(2),(3),(6);

INSERT INTO poll (vote_id) VALUES(3),(5),(4),(7);

INSERT INTO voter(name) VALUES ('bob'),
('Jack'),
('Joe'),
('Shara'), 
('Hillary'), 
('Steven'), 
('Sandra');

To retrieve the voter that has voted for a post you have to use a JOIN. This is an example how this would look like if you want to find the voters for a post number 2. 

SELECT post.post_id, vote.name  
FROM (post 
 JOIN post_vote 
ON post_vote.post_id = post.post_id) 
 JOIN vote 
ON vote.vote_id = post_vote.vote_id
WHERE post.post_id = 2;

SQL FIDDLE DEMO

Some explanation if you have a poll and a vote you have a many to many relationship, i.e. one voter can vote for more than one poll and one poll can have more than one voter. To bridge between the vote and poll table you use a bridge table. This tables contains all the poll numbers and vote combinations. So when you want to know who has voted for a particular poll you need to link the poll_id with the poll_id in the poll_vote table. The result is then matched with the vote table using the vote_id in the poll_vote table and the vote table. Hope this helps. Good luck with your project.

Upvotes: 2

jmilloy
jmilloy

Reputation: 8365

Look at the MySQL CREATE TABLE syntax http://dev.mysql.com/doc/refman/5.1/en/create-table.html

Notice that NOT NULL or NULL are part of a column definition. The default is NULL. This can only be applied to columns, not pairs of columns.

The solution here is to make two separate tables, one for post votes and one for poll votes. Then you can put the relevant fields in each table. This will also save you space, and make your data less error prone.

Upvotes: 0

Related Questions