CODEWITHSUNDEEP
mysqlconstraintsprimary-key
user14151025
user14151025

Reputation:

table constraints - primary keys in mysql

I have a following table :

CREATE TABLE `a` (
  `a1` int(11) NOT NULL,
  `a2` int(11) NOT NULL,
  PRIMARY KEY (`a1`,`a2`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1

Here When I assign values,

insert into a values(1,2);

After that if I give :

insert into a values(1,20);
insert into a values(10,2);

Here I've declared a1 and a2 as primary key that means it's unique and not null by default. But it accepts duplicates (for a1 - (1,2) and (1,20)) as given above. It shouldn't right? It gives error only when I give :

insert into a values(1,2);

ERROR 1062 (23000): Duplicate entry '1-2' for key 'PRIMARY'

Whereas I cannot leave a column (not a combination of columns) as null. It executes primary key like pairs but not null in individual columns. Why that happens?? Can anyone explain me???

Upvotes: 0

Views: 60

Answers (2)

Sanmitha Sadhishkumar
Sanmitha Sadhishkumar

Reputation: 257

Here, you are using composite keys. And it's functionality is that only. When you declared your primary key automatically not null constraint gets added to both the fields and that's why it acts in individual columns and primary key's uniqueness is a combination of both the fields here

Upvotes: 0

Derviş Kayımbaşıoğlu
Derviş Kayımbaşıoğlu

Reputation: 30575

when you declare composite primary keys (a1 and a2 together), they need to be unique together. which means you cannot insert (1,2) more then once.

if you need a1 and a2 will be unique on their own, you need to create unique index on each column.

ALTER TABLE a
  ADD CONSTRAINT constraint_name UNIQUE KEY(a2);


ALTER TABLE a
  ADD CONSTRAINT constraint_name2 UNIQUE KEY(a1);

the role of the primary key is to to define unicity for each table row.

Upvotes: 5

Related Questions