CODEWITHSUNDEEP
mysql
Bagusflyer
Bagusflyer

Reputation: 12925

MySql multiple columns as primary key doesn't work

I create the MySql table by the following sql statement:

CREATE TABLE IF NOT EXISTS `mytable` (
  `agent` varchar(64) NOT NULL,
  `name` varchar(40) NOT NULL,
  `app` varchar(64) NOT NULL,
  PRIMARY KEY (`app`,`agent`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

As you see, the field 'app' and 'agent' is the primary key. But unfortunately it doesn't work when I insert the following data, it always show the duplicated key in 'app' field:

app     agent       name
-------------------------
MyApp    ios         cde
MyApp    android     abc

Can anybody tell me anything wrong? Thanks

Upvotes: 0

Views: 149

Answers (2)

Andreas Wederbrand
Andreas Wederbrand

Reputation: 40061

In your primary key app and agent are a primary key together, not two individual keys.

You'll be able to add many rows with app = 'MyApp' as long as agent differs. And the other way around.

If you wan't to disallow multiple rows with the same app and multiple rows with the same agent add normal unique indexes.

CREATE TABLE IF NOT EXISTS `mytable` (
  `agent` varchar(64) NOT NULL,
  `name` varchar(40) NOT NULL,
  `app` varchar(64) NOT NULL,
  UNIQUE app_index (`app`),
  UNIQUE agent_index (`agent`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Upvotes: 1

PravinS
PravinS

Reputation: 2594

The set of primary keys in MySQL does not check for individual unique values, it will give duplicate error when you will try to insert same set of values in multiple records, but both the columns will not accept NULL values

Eg.

app     agent       name
-------------------------
MyApp    ios         cde
MyApp    ios         abc        - it will give you error as "Duplicate entry 'MyApp-ios' for key 'PRIMARY'"

may this will help you

Upvotes: 0

Related Questions