CODEWITHSUNDEEP
mysqlsql
user1010399
user1010399

Reputation: 2348

MySql: Composite Unique Key

I want to make composite key of 2 column id & code,the both columns altogether should act like Unique key for the table. while I have browsed and tried to create a table as follows,

Create table test (
  `test_no` int not null AUTO_INCREMENT,
  `code` varchar(5) NOT NULL,  
  `name` varchar(255),
  `UPDBy` varchar(255),
  PRIMARY KEY (`test_no`),
  FOREIGN KEY (code) REFERENCES other_table(code)
 // CONSTRAINT `K_test_1` Unique KEY (`test_no`,`code`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Just a second thought, can i make both the column as PK ? I think it will serve my purpose, RIght?

CONSTRAINT `K_test_1` Primary KEY (`test_no`,`code`) OR  Primary KEY (`test_no`,`code`)

Upvotes: 0

Views: 5518

Answers (2)

Thorsten Kettner
Thorsten Kettner

Reputation: 95062

You seem to be on the wrong track somehow. Your table has an ID which is auto incremented. This is not supposed to be the primary key? Why do you call it ID then?

There are two ways to build a database: Either use the natural values a user is used to, such as an employee number a department number and so on. Or use IDs (which are usually hidden from the user). Than you would have an employee table with primary key "id" or "employee_id" or whatever, and the employee number just as a field. But as it must be unique, you would have an additional unique index on that field.

Having said that; you have a table "other_table" with primary key "code" it seems. So you are not using an ID concept here. Then why do you use it on table test? If this is a detail table on other_table, then I'd expect the composite key to be something like code + test_no (thus showing numbered tests per code) for isntance.

So the essence is: 1. Think about what your table contains. 2. Think about wether to use IDs or natural keys. The answer to these questions should help you find the correct key for your table. (And sometimes a table even doesn't have a primary key and needs none.)

Upvotes: 3

Minoru
Minoru

Reputation: 1730

You sure can make them both as PRIMARY KEY. If you don't want to, just use UNIQUE instead of UNIQUE KEY.

To set both as PRIMARY KEY, do as it follows:

...
PRIMARY KEY (`id`, `code`);
...

To set a UNIQUE CONSTRAINT, do as it follows:

...
CONSTRAINT `K_test_1` UNIQUE (`id`,`code`);
...

Upvotes: 3

Related Questions