NULL이 있는 고유 키

NULL이 있는 고유 키

이 질문에는 약간의 가정적인 배경이 필요하다. 그럼 한 번 .employee이 name,date_of_birth,title,salary 인물의 두 의 놀라운 이기 때문에 MySQL RDBMS에 고유한 키를 어떤 사람이 다른 사람과 이름과 생년월일을 같다면, 정의상 동일한 사람(Abraham Lincoln이라는 이름의 두 사람이 1809년 2월 12일에 태어난 놀라운 우연의 일치 제외)이기 때문에, 우리는 고유한 키를 둘 것입니다.name ★★★★★★★★★★★★★★★★★」date_of_birth이긴 하지만''이긴 해요.이제 다음 데이터를 고려하겠습니다.

id name        date_of_birth title          salary
 1 John Smith  1960-10-02    President      500,000
 2 Jane Doe    1982-05-05    Accountant      80,000
 3 Jim Johnson NULL          Office Manager  40,000
 4 Tim Smith   1899-04-11    Janitor         95,000

다음 문을 실행하려고 하면 실패합니다.

INSERT INTO employee (name, date_of_birth, title, salary)
VALUES ('Tim Smith', '1899-04-11', 'Janitor', '95,000')

이것을 시도하면 성공합니다.

INSERT INTO employee (name, title, salary)
VALUES ('Jim Johnson', 'Office Manager', '40,000')

이제 데이터는 다음과 같습니다.

id name        date_of_birth title          salary
 1 John Smith  1960-10-02    President      500,000
 2 Jane Doe    1982-05-05    Accountant      80,000
 3 Jim Johnson NULL          Office Manager  40,000
 4 Tim Smith   1899-04-11    Janitor         95,000
 5 Jim Johnson NULL          Office Manager  40,000

이것은 내가 원하는 것은 아니지만 일어난 일에 전적으로 반대한다고 말할 수는 없다.수학 집합으로 말하면

{'Tim Smith', '1899-04-11'} = {'Tim Smith', '1899-04-11'} <-- TRUE
{'Tim Smith', '1899-04-11'} = {'Jane Doe', '1982-05-05'} <-- FALSE
{'Tim Smith', '1899-04-11'} = {'Jim Johnson', NULL} <-- UNKNOWN
{'Jim Johnson', NULL} = {'Jim Johnson', NULL} <-- UNKNOWN

내 추측으로는 MySQL이 이렇게 말하는 것 같아. "Jim Johnson이 이 비디오에NULL이렇게 하다' ★★★★★★★★★★★★★★★★★」

질문입니다.항상 알 수 있는 것은 아니지만 중복을 방지하려면 어떻게 해야 합니까?내가 지금까지 생각해낸 것 중 가장 좋은 것은 이사하는 것이다.date_of_birth하다하지만 문제는 이름, 직함, 급여가 같고 생년월일이 다르며 중복되지 않고 둘 다 보관할 방법이 없다는 것입니다.

고유 키의 기본 속성은 고유해야 한다는 것입니다.이 키의 일부를 Nullable로 하면 이 속성이 삭제됩니다.

문제 해결 방법에는 다음 두 가지가 있습니다.

• 한 가지 잘못된 방법은 알려지지 않은 것을 나타내기 위해 마법의 날짜를 사용하는 것입니다.이것으로 DBMS의 "문제"를 해결할 수 있을 뿐이지 논리적인 의미에서는 문제를 해결할 수 없습니다.생년월일을 알 수 없는 두 개의 "존 스미스" 엔트리에 문제가 있을 것으로 예상합니다.이 사람들은 같은 사람들인가요 아니면 독특한 사람들인가요?서로 다른 것을 알게 되면 이전과 같은 문제로 돌아가게 됩니다. 즉, 고유 키는 고유하지 않습니다."알 수 없는" 것을 나타내기 위해 모든 범위의 마법 날짜를 할당할 생각조차 하지 마세요.- 이것은 실로 지옥으로 가는 길입니다.

• 보다 나은 방법은 EmployeeId 속성을 대리 키로 작성하는 것입니다.이는 사용자가 고유하다고 알고 있는 개인에게 할당하는 임의의 식별자입니다.이 식별자는 보통 단순한 정수 값입니다.그런 다음 EmployeeId(고유한 Null 불가 키)를 종속 속성 사용자(이 경우 이름 및 생년월일)와 관련짓는 Employee 테이블을 만듭니다(이 경우 모두 Null일 수 있습니다.이전에 이름/생년월일을 사용한 모든 위치에서 EmployeeId 대리 키를 사용합니다.이렇게 하면 시스템에 새로운 테이블이 추가되지만 알 수 없는 값의 문제는 견고한 방법으로 해결됩니다.

MySQL이 바로 여기서 하는 것 같아요.일부 다른 데이터베이스(Microsoft SQL Server 등)에서는 NULL을 UNIQURE 열에 한 번만 삽입할 수 있는 값으로 취급하지만 개인적으로는 이상하고 예기치 않은 동작입니다.

단, 이것은 원하는 것이므로, NULL 대신 일부 "매직" 값을 사용할 수 있습니다(예: 과거 날짜).

컬럼을 추가로 .checksum에는 md5 됩니다.name ★★★★★★★★★★★★★★★★★」date_of_birth.(name, date_of_birth)체크섬에.체크섬에 하나의 고유 키를 만듭니다.

ALTER TABLE employee 
    ADD COLUMN checksum CHAR(32) NOT NULL;

UPDATE employee 
SET checksum = MD5(CONCAT(name, IFNULL(date_of_birth, '')));

ALTER TABLE employee 
    ADD UNIQUE (checksum);

이 솔루션은 삽입된 모든 쌍에 대해 작은 기술적 오버헤드를 발생시킵니다(모든 검색 쿼리에 대해 동일).추가 개선을 위해 삽입할 때마다 해시를 생성하는 트리거를 추가할 수 있습니다.

CREATE TRIGGER before_insert_employee 
BEFORE INSERT ON employee
FOR EACH ROW
    IF new.checksum IS NULL THEN
      SET new.checksum = MD5(CONCAT(new.name, IFNULL(new.date_of_birth, '')));
    END IF;

이름에 따른 중복이 없는 문제는 자연 키가 없기 때문에 해결할 수 없습니다.생년월일을 알 수 없는 사람을 위해 가짜 날짜를 넣는 것은 당신의 문제를 해결하지 못할 것이다.1900/01/01년생 존 스미스는 여전히 1960/03/09년생 존 스미스와는 다른 사람이다.

저는 매일 크고 작은 조직의 이름 데이터를 가지고 일합니다. 그리고 항상 같은 이름을 가진 두 명의 다른 사람이 있다는 것을 확신합니다.때로는 같은 직함을 가지고 있기도 합니다.생년월일 또한 독특함을 보장하지는 않는다. 같은 날짜에 태어난 많은 존 스미스가 있다.의사 사무실 데이터를 취급할 때 이름, 주소, 전화번호가 같은 의사가 2명 있는 경우가 많습니다(아버지와 아들의 조합).

각 직원을 고유하게 식별하기 위해 직원 데이터를 삽입하는 경우 직원 ID를 사용하는 것이 가장 좋습니다.그런 다음 사용자 인터페이스에서 고유 이름을 확인하고 일치하는 항목이 하나 이상 있으면 사용자에게 해당 이름을 의미하는지 물어보고 아니라고 대답하면 레코드를 삽입합니다.그런 다음 실수로 두 개의 ID가 할당되었을 경우 문제를 해결하기 위한 디듀핑 프로세스를 구축합니다.

그것을 하는 다른 방법이 있다.date_of_birth 열의 String 값을 나타내는 열(null 불가)을 추가합니다.date_of_birth가 null인 경우 새 열 값은 "(빈 문자열)이 됩니다.

열 이름을 date_of_birth_str로 지정하고 고유한 제약조건 직원(name, date_of_birth_str)을 만듭니다.따라서 두 개의 레코드가 동일한 이름과 null date_of_birth 값을 갖는 경우에도 고유한 제약 조건은 계속 작동합니다.

그러나 두 개의 동일한 의미를 가진 기둥에 대한 유지 보수 노력과 새 기둥의 성능 저하를 신중하게 고려해야 합니다.

할 수 .NULL값은 사용되지 않는 상수(예: 0)로 대체됩니다.그런 다음 이 열에 고유한 제약 조건을 적용할 수 있습니다.

CREATE TABLE employee ( 
  name VARCHAR(50) NOT NULL, 
  date_of_birth DATE, 
  uq_date_of_birth DATE AS (IFNULL(date_of_birth, '0000-00-00')) UNIQUE
);

완벽한 솔루션은 영국의 기능 기반 지원이지만, mySQL은 기능 기반 인덱스도 지원해야 하기 때문에 더 복잡해집니다.이를 통해 NULL 대신 "짝퉁" 값을 사용할 필요가 없어지는 동시에 개발자가 영국의 NULL 값을 어떻게 처리할지를 결정할 수 있게 됩니다.안타깝게도 현재 mySQL은 제가 알고 있는 기능을 지원하지 않기 때문에 해결 방법이 남아 있습니다.

CREATE TABLE employee( 
 name CHAR(50) NOT NULL, 
 date_of_birth DATE, 
 title CHAR(50), 
 UNIQUE KEY idx_name_dob (name, IFNULL(date_of_birth,'0000-00-00 00:00:00'))
);

(고유 키 정의에서 IFNULL() 함수의 사용에 주의해 주십시오).

이것과 비슷한 문제가 있었지만, 반전이 있었다.당신의 경우 알 수 없지만 모든 직원이 생일을 가지고 있습니다.이 경우, 생일이 불분명하지만 정보가 동일한 직원에게 두 가지 값을 할당하는 것이 논리적으로 타당합니다.NealB의 대답은 매우 정확하다.

그러나 데이터 필드에 반드시 값이 없는 문제가 발생했습니다.예를 들어 'name_of_spouse' 필드를 테이블에 추가한 경우 테이블의 각 행에 반드시 값이 있을 필요는 없습니다.이 경우 NealB의 첫 번째 항목('잘못된 방법')이 실제로 타당합니다.이 경우 알려진 배우자가 없는 각 행의 name_of_spouse 열에 문자열 'None'을 삽입해야 합니다.

이 문제가 발생한 것은 IP 트래픽을 분류하기 위한 데이터베이스로 프로그램을 작성하는 경우입니다.목적은 프라이빗 네트워크상의 IP 트래픽 그래프를 작성하는 것이었습니다.각 패킷은 ip source 및 dest, port source 및 dest, transport protocol 및 application protocol에 기반한 고유한 연결 인덱스를 사용하여 데이터베이스 테이블에 배치되었습니다.그러나 많은 패킷에는 애플리케이션 프로토콜이 없습니다.예를 들어, 애플리케이션 프로토콜이 없는 모든 TCP 패킷은 함께 분류되어야 하며 연결 인덱스에서 하나의 고유한 항목을 차지해야 합니다.이는 이러한 패킷이 그래프의 단일 엣지를 형성하도록 하기 때문입니다.이 경우, 저는 위에서 직접 조언을 받아 Application Protocol 필드에 문자열 'None'을 저장하여 이러한 패킷이 고유한 그룹을 형성하도록 했습니다.

저는 하나의 해결책을 찾고 있었는데 Alexander Yancharuk이 제안한 것이 저에게 좋은 아이디어였습니다.단, 제 경우 열은 외부 키이며 employee_id는 null일 수 있습니다.

다음과 같은 구조를 가지고 있습니다.

+----+---------+-------------+
| id | room_id | employee_id |
+----+---------+-------------+
|  1 |       1 | NULL        |
|  2 |       2 | 1           |
+----+---------+-------------+

또한 employee_id가 NULL인 room_id는 복제할 수 없습니다.

삽입하기 전에 다음과 같이 트리거 추가를 해결했습니다.

DELIMITER $$
USE `db`$$
CREATE DEFINER=`root`@`%` TRIGGER `db`.`room_employee` BEFORE INSERT ON `room_employee` FOR EACH ROW
BEGIN
    IF EXISTS (
            SELECT room_id, employee_id
            FROM room_employee
            WHERE (NEW.room_id = room_employee.room_id AND NEW.employee_id IS NULL AND room_employee.employee_id IS NULL)
        ) THEN
        CALL `The room Can not be duplicated on room employee table`;
    END IF;
END$$
DELIMITER ;

room_id와 employee_id에 대한 고유한 제약 조건도 추가했습니다.

여기서의 근본적인 질문은 당신이 실제로 의미하는 것이 무엇이냐는 것이라고 생각합니다.

직원(이름, 직함, 급여)의 값('Jim Johnson', 'Office Manager', '40,000')에 삽입

사람에 대한 당신의 정의는 이름과 생년월일인데, 이 문장은 그런 맥락에서 무엇을 의미할까요?당신의 문제에 대한 해결책은 이름과 date_of_birth 열에 NOT NULL을 추가하여 위와 같은 반쪽 아이덴티티를 삽입하는 것을 금지하는 것이라고 생각합니다.이렇게 하면 문이 실패하고 완전한 ID를 입력하도록 강제되며, 고유 키가 작동하여 동일한 사용자를 두 번 입력하는 것을 방지합니다.

간단히 말해, 고유 제약의 역할은 필드나 열을 만드는 것입니다.null은 데이터베이스가 null을 알 수 없는 것으로 처리하므로 이 속성을 삭제합니다.

중복을 방지하고 늘을 허용하려면:

고유 키를 기본 키로 만들기

언급URL : https://stackoverflow.com/questions/4081783/unique-key-with-nulls