Inserting values in Mysql table if all the columns have different values

Question

I am working on a blogging site but problem which I am facing is that, I want to allow user to create a blog if and only if his/her combination of email address, Blog Name and Blog Title is different from any other rows in the table. For example let us consider this table:-

Email                          BlogName                                BlogTitle
[email protected]                Coder's world                         What is coding?
[email protected]                Sql world                             What is sql?

Now if a user tries to enter a values('[email protected]','Coder's Worlds' ,'What is coding?') in the table again then he/she will not be allowed to do so. But if the user tries to insert different combination of email, BlogName and BlogTitle then he/she will be allowed to do so.

As I am pretty new to Mysql, I don't know how to do this, so please help me to do this. My Table structure :-

When you create the table make sure whole three(Email, BlogName, BlogTitle) columns selected as primary key. If it is already done, Please put a screen shot of your table structure. — Dil.
– Dil., Commented Dec 23, 2015 at 5:38

Tim Biegeleisen · Accepted Answer · 2015-12-23 05:47:20Z

1

You can add a unique constraint on the combination of the three columns:

ALTER TABLE `blog_table` ADD UNIQUE `unique_index`(`Email`(100), `BlogName`(100), `BlogTitle`(100));

This will also remove any duplicates which might already exist in your blog table.

Note that this constraint uses only the first 100 characters from each of the three columns to avoid the #1071-Specified key was too long error.

edited Dec 23, 2015 at 5:47

answered Dec 23, 2015 at 5:36

Tim Biegeleisen

526k32 gold badges324 silver badges399 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Gunjan Over a year ago

It is giving error:- #1071-Specified key was too long;max key length is 767 bytes

Tim Biegeleisen Over a year ago

@Gunjan You can subset the columns to get around this error.

Amol · Accepted Answer · 2015-12-23 05:39:15Z

0

Make those 3 Columns unique.

Here is a example of Making multiple fields composite ALTER TABLE table ADD UNIQUE ('field_1','field_2', 'field_3');

answered Dec 23, 2015 at 5:39

Amol

3633 silver badges12 bronze badges

Comments

Akshey Bhat · Accepted Answer · 2015-12-23 05:45:50Z

0

IF (SELECT 1 = 1 FROM Table WHERE Email=@email and BlogName=@blogname and BlogTitle=@blogtitle) THEN
BEGIN
    #return error message do not insert
END;
ELSE
BEGIN
   #insert logic here
END;
END IF;

Check if matching record exists in table before inserting using above code

answered Dec 23, 2015 at 5:45

Akshey Bhat

8,6872 gold badges24 silver badges20 bronze badges

Collectives™ on Stack Overflow

Inserting values in Mysql table if all the columns have different values

3 Answers 3

2 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related