CODEWITHSUNDEEP
phpmysql
Lina Gom
Lina Gom

Reputation: 97

Combination of two columns as unique Key in mysql

Need to set a unique key by combining two column in mysql. For example

id Ticket_number Code     Name
1    5              123   a
2    5              89    b
3    2              89    a
4    8              123   c

Here I want to set the Ticket_number and Code as unique by combining both. i.e error should be occur only if the same combination occurs again. Ticket Number and code can be duplicated but the combination should not be duplicated. How is it possible. Please help me.

Here is the create statement:

CREATE TABLE arlog ( id int(11) NOT NULL AUTO_INCREMENT, Ticket_Number varchar(40) NOT NULL, Code varchar(10) NOT NULL, Name varchar(100) NOT NULL, PRIMARY KEY (id), UNIQUE KEY Ticket_Number (Ticket_Number) ) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=latin1;

Upvotes: 2

Views: 10041

Answers (4)

Safaetul Ahasan Piyas
Safaetul Ahasan Piyas

Reputation: 1783

traffic_hit_reports is a table name,
ALTER TABLE change the table schema,
ADD UNIQUE add the unique constraint and
content_id,hit_date is a field name.

Now,

ALTER TABLE `traffic_hit_reports` ADD UNIQUE `unique_index`(`content_id`,`hit_date`);

Result: enter image description here

Upvotes: 0

Arun
Arun

Reputation: 3741

use this code to create table

CREATE TABLE ticket(
    id INT,
    ticket_number INT,
    code INT,
    PRIMARY KEY (id)
) 

CREATE UNIQUE INDEX index_name ON ticket(id, ticket_number, code);

which will act as a composite key.

Upvotes: 3

Pankaj katiyar
Pankaj katiyar

Reputation: 464

follow as given 

CREATE TABLE tablename (
 id INT,   
 user_id INT,
 setting_id INT,
 NAME  VARCHAR(255)
);

CREATE UNIQUE INDEX index_name ON tablename(user_id, setting_id);

INSERT INTO tablename (NAME,user_id,setting_id) VALUES ('1',34,15)
  ON DUPLICATE KEY UPDATE NAME = '1', user_id = 34, setting_id = 15;

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1271231

Create a unique index:

create unique index idx_table_ticket_code on table(ticket_number, code)

Upvotes: 5

Related Questions