CODEWITHSUNDEEP
mysqlsqlhibernate
Leem.fin
Leem.fin

Reputation: 42642

restrict database table to contain unique data

I am using hibernate in my project.

I have a Team entity:

@Entity
@Table(name = "team")
public class Team {
     private int tid; //primary key

     private int size;
     private String name;
     private boolean isNew;
     private String other;

     //Setter & Getter
     ...
}

Then, I created a team table in MySQL database with the following sql statement:

CREATE TABLE team (
    tid int not null auto_increment, 
    size int not null,
    name varchar(128),
    is_new bool default false,
    other varchar(128),
    PRIMARY KEY (tid)
) ENGINE innodb CHARACTER SET utf8;

Each row of data in this table represents a team object.

How can I restrict this table to contain unique team? I mean how to ensure that there are no teams in the table have exactly the save value for every field(column)?

NOTE: What I mean is not to have one column (one field) be unique, but the row of data be unique. That's to ensure there are no multiple teams have every field be the same.

For example the following 2 teams are unique & acceptable since their names are different:

Team 1: size = 3  name = "team1" isNew = true  other="nothing"
Team 2: size = 3  name = "team2" isNew = true  other="nothing"

For another example, the following 2 teams are exactly the same (no field has different value) which my table should NOT accept:

Team 1: size = 3  name = "team" isNew = true  other="nothing"
Team 2: size = 3  name = "team" isNew = true  other="nothing"

I mean in team table two teams could have the same value for multiple fields as long as there is at least one field different. Then, they are distinguishable unique teams.

Upvotes: 0

Views: 113

Answers (2)

M Khalid Junaid
M Khalid Junaid

Reputation: 64486

You can define a unique constraint for the columns you want the group to be unique in your entity

@Entity
@Table(name = "team",
uniqueConstraints= @UniqueConstraint(columnNames = { "size","name","is_new","other"}))

or for each column 

@Entity
@Table(name="team") 
public class Team{
.
.
.
  @Column(name = "size", unique=true)
  private String size;
.
.
.
}

Upvotes: 1

ramaral
ramaral

Reputation: 6179

You can add a UNIQUE constraint:

CREATE TABLE team (
    tid int not null auto_increment, 
    size int not null,
    name varchar(128),
    is_new bool default false,
    other varchar(128),
    PRIMARY KEY (tid),
    CONSTRAINT uc_Name UNIQUE (size,name,is_new,other)
) ENGINE innodb CHARACTER SET utf8;

That ensures each team must have all columns unique

Upvotes: 0

Related Questions