0

I have a table in Mysql with the following fields:

CREATE TABLE table_1(
camp_1 boolean,
tree varchar(50),
name varchar(50),
serv varchar(50),
number varchar(50),
PRIMARY KEY (camp_1, tree, name)
);

then i can have something like that: 

select* from table_1

aa, bb, cc, null, 123
dd, ee, ff, null, 512

The problem is that i need that when serv is null, then my primary key will be like (or i need the three unique values): camp_1, tree, name

And if serv is not null i need can insertd somethin like:

select* from table_1

aa, bb, cc, ddd, 123
aa, bb, cc, ert, 123
dd, ee, ff, null, 512

In this way if serv is not null i can have camp_1 , tree, name two or more times (aa, bb, cc appear 2 times) with diferent values, but if serv is not null i can not have camp_1 , tree, name (dd, ee, ff only 1 time) with more than 1 value.

I try with an index, but it not found because i can not insert null in my primary key.

Something can help me?

Code Geas Coder
  • 1,839
  • 4
  • 23
  • 29
  • `UNIQUE` index is unique and can accept `NULL` values whereas, `PRIMARY` index rejects `NULL` values – Sam Feb 17 '14 at 19:34
  • But if i create for example my index with: camp_1, tree, name, serv i can have repeat values in: camp_1, tree, name when serv is null, and if i have like my primary key: camp_1, tree, name, i can not have diferents values in my 3 fields with serv not null – Code Geas Coder Feb 17 '14 at 19:38
  • add an id column with primary key and unique keys for camp tree and name – Sam Feb 17 '14 at 19:39
  • why is camp_1 there anyway try normalizing instead. by having camp_id referencing camps table – Sam Feb 17 '14 at 19:41

0 Answers0