[mysql] 가능한 두 테이블 중 하나에 MySQL 외래 키를 사용할 수 있습니까?

당신이 묘사하는 것을 다형성 협회라고합니다. 즉, “외부 키”열에는 일련의 대상 테이블 중 하나에 존재해야하는 id 값이 포함됩니다. 일반적으로 대상 테이블은 일반적인 수퍼 클래스 데이터 인스턴스와 같은 방식으로 관련됩니다. 외래 키 열 옆에 다른 열이 필요하므로 각 행에서 참조 할 대상 테이블을 지정할 수 있습니다.

CREATE TABLE popular_places (
  user_id INT NOT NULL,
  place_id INT NOT NULL,
  place_type VARCHAR(10) -- either 'states' or 'countries'
  -- foreign key is not possible
);

SQL 제약 조건을 사용하여 다형성 연관을 모델링 할 방법이 없습니다. 외래 키 제약 조건은 항상 하나의 대상 테이블을 참조 합니다.

다형성 연관은 Rails 및 Hibernate와 같은 프레임 워크에 의해 지원됩니다. 그러나이 기능을 사용하려면 SQL 제약 조건을 비활성화해야합니다. 대신, 참조가 만족되도록 응용 프로그램 또는 프레임 워크가 동등한 작업을 수행해야합니다. 즉, 외부 키의 값이 가능한 목표 테이블 중 하나에 존재합니다.

다형성 연관성은 데이터베이스 일관성 적용과 관련하여 약합니다. 데이터 무결성은 동일한 참조 무결성 논리를 적용하여 데이터베이스에 액세스하는 모든 클라이언트에 따라 달라지며, 버그가 없어야합니다.

다음은 데이터베이스 적용 참조 무결성을 이용하는 대체 솔루션입니다.

대상 당 하나의 추가 테이블을 작성하십시오. 예를 들어 popular_states그리고 popular_countries, 어떤 기준 states과 countries각각. 이러한 “인기”테이블 각각은 또한 사용자의 프로필을 참조합니다.

CREATE TABLE popular_states (
  state_id INT NOT NULL,
  user_id  INT NOT NULL,
  PRIMARY KEY(state_id, user_id),
  FOREIGN KEY (state_id) REFERENCES states(state_id),
  FOREIGN KEY (user_id) REFERENCES users(user_id),
);

CREATE TABLE popular_countries (
  country_id INT NOT NULL,
  user_id    INT NOT NULL,
  PRIMARY KEY(country_id, user_id),
  FOREIGN KEY (country_id) REFERENCES countries(country_id),
  FOREIGN KEY (user_id) REFERENCES users(user_id),
);

이것은 사용자가 선호하는 모든 장소를 얻으려면 두 테이블 모두를 쿼리해야 함을 의미합니다. 그러나 일관성을 유지하기 위해 데이터베이스에 의존 할 수 있음을 의미합니다.

places테이블을 수퍼 테이블로 작성하십시오 . 일등 언급으로, 두 번째 대안은 인기있는 장소가 같은 테이블 참조하는 것입니다 places모두에 부모, states와 countries. 즉, 주와 국가 모두 외래 키를 가지고 places있습니다 (이 외래 키를 states및 의 기본 키로 만들 수도 있습니다 countries).

CREATE TABLE popular_areas (
  user_id INT NOT NULL,
  place_id INT NOT NULL,
  PRIMARY KEY (user_id, place_id),
  FOREIGN KEY (place_id) REFERENCES places(place_id)
);

CREATE TABLE states (
  state_id INT NOT NULL PRIMARY KEY,
  FOREIGN KEY (state_id) REFERENCES places(place_id)
);

CREATE TABLE countries (
  country_id INT NOT NULL PRIMARY KEY,
  FOREIGN KEY (country_id) REFERENCES places(place_id)
);

두 개의 열을 사용하십시오. 두 개의 목표 테이블 중 하나를 참조 할 수있는 하나의 컬럼 대신 두 개의 컬럼을 사용하십시오. 이 두 열은 NULL; 실제로 그들 중 하나만이 아닌 것이어야합니다 NULL.

CREATE TABLE popular_areas (
  place_id SERIAL PRIMARY KEY,
  user_id INT NOT NULL,
  state_id INT,
  country_id INT,
  CONSTRAINT UNIQUE (user_id, state_id, country_id), -- UNIQUE permits NULLs
  CONSTRAINT CHECK (state_id IS NOT NULL OR country_id IS NOT NULL),
  FOREIGN KEY (state_id) REFERENCES places(place_id),
  FOREIGN KEY (country_id) REFERENCES places(place_id)
);

관계 이론의 관점에서, 다형성 연관은 사실상 두 가지 의미를 가진 열 이기 때문에 첫 번째 정규형을 위반합니다 popular_place_id. 그것은 국가 또는 국가입니다. 당신은 사람의를 저장하지 않을 age자신의 phone_number하나의 컬럼, 그리고 같은 이유로 당신은 모두를 저장하지 말아야 state_id하고 country_id하나의 열에. 이 두 속성이 호환 가능한 데이터 유형을 가지고 있다는 사실은 우연입니다. 그들은 여전히 ​​다른 논리적 실체를 의미합니다.

열의 의미는 외래 키가 참조하는 테이블의 이름을 지정하는 추가 열에 의존하기 때문에 다형성 연관도 Third Normal Form을 위반 합니다. 세 번째 정규 형식에서 테이블의 속성은 해당 테이블의 기본 키에만 의존해야합니다.

@SavasVedova의 의견 :

테이블 정의 나 예제 쿼리를 보지 않고 설명을 따르지는 않지만 확실하게 여러 Filters테이블이 있으며 각각 중앙 Products테이블 을 참조하는 외래 키가 포함되어있는 것처럼 들립니다 .

CREATE TABLE Products (
  product_id INT PRIMARY KEY
);

CREATE TABLE FiltersType1 (
  filter_id INT PRIMARY KEY,
  product_id INT NOT NULL,
  FOREIGN KEY (product_id) REFERENCES Products(product_id)
);

CREATE TABLE FiltersType2 (
  filter_id INT  PRIMARY KEY,
  product_id INT NOT NULL,
  FOREIGN KEY (product_id) REFERENCES Products(product_id)
);

...and other filter tables...

가입하려는 유형을 알고 있으면 제품을 특정 유형의 필터에 쉽게 가입 할 수 있습니다.

SELECT * FROM Products
INNER JOIN FiltersType2 USING (product_id)

필터 유형을 동적으로하려면 SQL 쿼리를 구성하기 위해 응용 프로그램 코드를 작성해야합니다. SQL에서는 쿼리 작성시 테이블을 지정하고 수정해야합니다. 의 개별 행에서 찾은 값을 기반으로 조인 된 테이블을 동적으로 선택할 수 없습니다 Products.

다른 옵션은 외부 조인을 사용하여 모든 필터 테이블에 조인하는 것입니다. 일치하는 product_id가없는 항목은 단일 행의 널로 리턴됩니다. 그러나 여전히 조인 된 모든 테이블 을 하드 코딩 해야 하며 새 필터 테이블을 추가하는 경우 코드를 업데이트해야합니다.

SELECT * FROM Products
LEFT OUTER JOIN FiltersType1 USING (product_id)
LEFT OUTER JOIN FiltersType2 USING (product_id)
LEFT OUTER JOIN FiltersType3 USING (product_id)
...

모든 필터 테이블에 조인하는 또 다른 방법은 순차적으로 수행하는 것입니다.

SELECT * FROM Product
INNER JOIN FiltersType1 USING (product_id)
UNION ALL
SELECT * FROM Products
INNER JOIN FiltersType2 USING (product_id)
UNION ALL
SELECT * FROM Products
INNER JOIN FiltersType3 USING (product_id)
...

그러나이 형식은 여전히 ​​모든 테이블에 대한 참조를 작성해야합니다. 그 문제를 해결하지 못했습니다.

답변

CREATE TABLE places (
  place_id INT NOT NULL UNIQUE,
  place_type VARCHAR(10) NOT NULL
     CHECK ( place_type = 'state', 'country' ),
  UNIQUE ( place_type, place_id )
);

CREATE TABLE states (
  place_id INT NOT NULL UNIQUE,
  place_type VARCHAR(10) DEFAULT 'state' NOT NULL
     CHECK ( place_type = 'state' ),
  FOREIGN KEY ( place_type, place_id )
     REFERENCES places ( place_type, place_id )
  -- attributes specific to states go here
);

CREATE TABLE countries (
  place_id INT NOT NULL UNIQUE,
  place_type VARCHAR(10) DEFAULT 'country' NOT NULL
     CHECK ( place_type = 'country' ),
  FOREIGN KEY ( place_type, place_id )
     REFERENCES places ( place_type, place_id )
  -- attributes specific to country go here
);

CREATE TABLE popular_areas (
  user_id INT NOT NULL,
  place_id INT NOT NULL,
  UNIQUE ( user_id, place_id ),
  FOREIGN KEY ( place_type, place_id )
     REFERENCES places ( place_type, place_id )
);

CREATE TABLE [geographical_location_type] (
    [geographical_location_type_id] INTEGER NOT NULL,
    [name] VARCHAR(25) NOT NULL,
    CONSTRAINT [PK_geographical_location_type] PRIMARY KEY ([geographical_location_type_id])
)

-- Add 'Region', 'Country' and 'State' instances to the above table


CREATE TABLE [geographical_location] (
   [geographical_location_id] BIGINT IDENTITY(0,1) NOT NULL,
    [name] VARCHAR(1024) NOT NULL,
    [geographical_location_type_id] INTEGER NOT NULL,
    [geographical_location_parent] BIGINT,  -- self referencing; can be null for top-level instances
    CONSTRAINT [PK_geographical_location] PRIMARY KEY ([geographical_location_id])
)

CREATE TABLE [user] (
    [user_id] BIGINT NOT NULL,
    [login_id] VARCHAR(30) NOT NULL,
    [password] VARCHAR(512) NOT NULL,
    CONSTRAINT [PK_user] PRIMARY KEY ([user_id])
)


CREATE TABLE [popular_user_location] (
    [popular_user_location_id] BIGINT NOT NULL,
    [user_id] BIGINT NOT NULL,
    [geographical_location_id] BIGINT NOT NULL,
    CONSTRAINT [PK_popular_user_location] PRIMARY KEY ([popular_user_location_id])
)

ALTER TABLE [geographical_location] ADD CONSTRAINT [geographical_location_type_geographical_location]
    FOREIGN KEY ([geographical_location_type_id]) REFERENCES [geographical_location_type] ([geographical_location_type_id])



ALTER TABLE [geographical_location] ADD CONSTRAINT [geographical_location_geographical_location]
    FOREIGN KEY ([geographical_location_parent]) REFERENCES [geographical_location] ([geographical_location_id])



ALTER TABLE [popular_user_location] ADD CONSTRAINT [user_popular_user_location]
    FOREIGN KEY ([user_id]) REFERENCES [user] ([user_id])



ALTER TABLE [popular_user_location] ADD CONSTRAINT [geographical_location_popular_user_location]
    FOREIGN KEY ([geographical_location_id]) REFERENCES [geographical_location] ([geographical_location_id])

 # 테이블 만들기  노래
    쿼리 . 추가 ( "SET SQL_MODE = NO_AUTO_VALUE_ON_ZERO;" ) 
    쿼리 . append ( "CREATE TABLE songs( NUMBERint (11) NOT NULL, SONG POSITIONint (11) NOT NULL, PLAY SONGtinyint (1) NOT NULL DEFAULT '1', SONG TITLEvarchar (255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL, DESCRIPTIONvarchar (1000) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL, ARTISTVARCHAR (255) CHARACTER SET의 UTF8 부씩의 utf8_general_ci NOT NULL DEFAULT 'Άγνωστος καλλιτέχνης', AUTHORVARCHAR (255) 문자 집합을 utf8 COLLATE의 utf8_general_ci NOT NULL의 DEFAULT 'Άγνωστος στιχουργός', COMPOSERVARCHAR (255) 문자 집합을 utf8 COLLATE의 utf8_general_ci NOT NULL 기본 'Άγνωστος συνθέτης',ALBUMvarchar (255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT 'Άγνωστο άλμπουμ', YEARint (11) NOT NULL DEFAULT '33', RATINGint (11) NOT NULL DEFAULT '5', IMAGEvarchar (600) CHARACTER SET utf8 콜라주 , SONG PATHvarchar (500) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL, SONG REPEATint (11) NOT NULL DEFAULT '0', VOLUMEfloat NOT NULL DEFAULT '1', SPEEDfloat NOT NULL DEFAULT '1') ENGINE = InnoDB DEFAULT CHARSET = utf8; " ) 
    query . append ( "ALTER TABLE songsADD PRIMARY KEY ( NUMBER), ADD UNIQUE KEY POSITION( SONG POSITION), ADD UNIQUE KEY TITLE( SONG TITLE), ADD UNIQUE KEY PATH( SONG PATH);") 
    검색어. 추가( "ALTER TABLE songsMODIFY NUMBERint (11) NOT NULL AUTO_INCREMENT;" )


#create table playlists
queries.append("CREATE TABLE `playlists` (`NUMBER` int(11) NOT NULL,`PLAYLIST POSITION` int(11) NOT NULL,`PLAYLIST TITLE` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,`PLAYLIST PATH` varchar(500) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL) ENGINE=InnoDB DEFAULT CHARSET=utf8;")
queries.append("ALTER TABLE `playlists` ADD PRIMARY KEY (`NUMBER`),ADD UNIQUE KEY `POSITION` (`PLAYLIST POSITION`),ADD UNIQUE KEY `TITLE` (`PLAYLIST TITLE`),ADD UNIQUE KEY `PATH` (`PLAYLIST PATH`);")
queries.append("ALTER TABLE `playlists` MODIFY `NUMBER` int(11) NOT NULL AUTO_INCREMENT;")

#create table for songs to playlist relation
queries.append("CREATE TABLE `songs of playlist` (`PLAYLIST NUMBER` int(11) NOT NULL,`SONG OR PLAYLIST` tinyint(1) NOT NULL DEFAULT '1',`RELATIVE NUMBER` int(11) NOT NULL) ENGINE=InnoDB DEFAULT CHARSET=utf8;")
queries.append("ALTER TABLE `songs of playlist` ADD KEY `PLAYLIST NUMBER` (`PLAYLIST NUMBER`) USING BTREE;")
queries.append("ALTER TABLE `songs of playlist` ADD CONSTRAINT `playlist of playlist_ibfk_1` FOREIGN KEY (`PLAYLIST NUMBER`) REFERENCES `playlists` (`NUMBER`) ON DELETE RESTRICT ON UPDATE RESTRICT")




그게 다야!
playlists_query = "s1. *, s3. *, s4. *에서 곡 선택 s1 INNER JOIN``재생 목록의 노래 ''s2 ON s1.`NUMBER` = s2.`RELATIVE NUMBER` INNER 조인 .`NUMBER` = s2.`PLAYLIST NUMBER` 내부 참여 s4 ON s4.`NUMBER` = s2.`RELATIVE NUMBER` 주문 s3.`PLAYLIST POSITION`,`s1`.`SONG POSITION` "