Reputation: 97
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
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`);
Upvotes: 0
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
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
Reputation: 1271231
Create a unique index:
create unique index idx_table_ticket_code on table(ticket_number, code)
Upvotes: 5