CODEWITHSUNDEEP
mysqlconstraintscreate-table
Alex McMillan
Alex McMillan

Reputation: 17962

MySQL Check Constraints

I have a database table like so:

-- Table KNOWN_AS
create table `known_as`(
id int not null auto_increment,
person_id int not null,
default_name boolean not null,
first_name varchar(100) not null,
middle_name_1 varchar(100),
middle_name_2 varchar(100),
middle_name_3 varchar(100),
last_name varchar(100),
primary key(id),
foreign key(person_id) references `person`(id)
) engine=innodb;

When inserting values, I want to check that each unique "person_id" has exactly one true "default_name".

All googling etc I've done so far has resulted in pages explaining how to keep a value non-negative, or how to make sure a value is unique - not how to check one value is unique amongst multiple entries (but not ALL).

Any help / pointers much appreciated!!

Upvotes: 0

Views: 345

Answers (1)

eggyal
eggyal

Reputation: 126025

  1. I want to check that each unique "person_id" has exactly one true "default_name"

    Why not store default_name as a NOT NULL column in the person table?

  2. how to check one value is unique amongst multiple entries (but not ALL)

    Define a UNIQUE index over the composite (person_id, default_name):

     ALTER TABLE known_as ADD UNIQUE INDEX (person_id, default_name);

Upvotes: 1

Related Questions