가능한 두 테이블 중 하나에 MySQL 외래 키를 수행 할 수 있습니까?

Programming

가능한 두 테이블 중 하나에 MySQL 외래 키를 수행 할 수 있습니까?

procodes 2020. 5. 26. 21:53

가능한 두 테이블 중 하나에 MySQL 외래 키를 수행 할 수 있습니까?

여기 내 문제가 있는데 세 개의 테이블이 있습니다. 지역, 국가, 주. 국가는 지역 내부에있을 수 있고, 국가는 지역 내부에있을 수 있습니다. 지역은 먹이 사슬의 최상위입니다.

이제 두 개의 열이있는 popular_areas 테이블을 추가하고 있습니다. region_id 및 popular_place_id 그것을 가능하게 popular_place_id 중 국가에 대한 외래 키가 될 수 있나요 또는 상태. 아마도 id가 국가 또는 주를 설명하는지 여부를 결정하기 위해 popular_place_type 열을 추가해야 할 것입니다.

당신이 설명하는 것을 다형성 협회라고합니다. 즉, "외부 키"열에는 일련의 대상 테이블 중 하나에 존재해야하는 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)

If you want the filter type to be dynamic, you must write application code to construct the SQL query. SQL requires that the table be specified and fixed at the time you write the query. You can't make the joined table be chosen dynamically based on the values found in individual rows of Products.

The only other option is to join to all filter tables using outer joins. Those that have no matching product_id will just be returned as a single row of nulls. But you still have to hardcode all the joined tables, and if you add new filter tables, you have to update your code.

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

Another way to join to all filter tables is to do it serially:

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)
...

But this format still requires you to write references to all tables. There's no getting around that.

This isn't the most elegant solution in the world, but you could use concrete table inheritance to make this work.

Conceptually you are proposing a notion of a class of "things that can be popular areas" from which your three types of places inherit. You could represent this as a table called, for example, places where each row has a one-to-one relationship with a row in regions, countries, or states. (Attributes that are shared between regions, countries, or states, if any, could be pushed into this places table.) Your popular_place_id would then be a foreign key reference to a row in the places table which would then lead you to a region, country, or state.

The solution you propose with a second column to describe the type of association happens to be how Rails handles polymorphic associations, but I'm not a fan of that in general. Bill explains in excellent detail why polymorphic associations are not your friends.

Here is a correction to Bill Karwin's "supertable" approach, using a compound key ( place_type, place_id ) to resolve the perceived normal form violations:

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 )
);

What this design cannot ensure that for every row in places there exists a row in states or countries (but not both). This is a limitations of foreign keys in SQL. In a full SQL-92 Standards compliant DBMS you could define deferrable inter-table constraints that would allow you to achieve the same but it is clunky, involves transaction and such a DBMS has yet to make it to market.

I realize that this thread is old, but I saw this and a solution came to mind and I thought I'd throw it out there.

Regions, Countries and States are Geographical Locations that live in a hierarchy.

You could avoid your problem altogether by creating a domain table called geographical_location_type which you would populate with three rows ( Region, Country, State).

Next, instead of the three location tables, create a single geographical_location table that has a foreign key of geographical_location_type_id (so you know if the instance is a Region, Country or State).

Model the hierarchy by making this table self-referencing so that a State instance holds the fKey to its parent Country instance which in turn holds the fKey to its parent Region instance. Region instances would hold NULL in that fKey. This is no different than what you would have done with the three tables (you would have 1 - many relationships between region and country and between country and state) except now it's all in one table.

The popular_user_location table would be a scope resolution table between user and georgraphical_location (so many users could like many places).

Soooo …

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])

Wasn't sure what the target DB was; the above is MS SQL Server.

참고URL : https://stackoverflow.com/questions/441001/possible-to-do-a-mysql-foreign-key-to-one-of-two-possible-tables

저작자표시 (새창열림)

'Programming' 카테고리의 다른 글

화살표 함수 (공개 클래스 필드)를 클래스 메서드로 사용하는 방법은 무엇입니까? (0)	2020.05.27
Webpack.config index.html을 dist 폴더에 복사하는 방법 (0)	2020.05.26
AngularJS는 jQuery보다 나은 점은 무엇입니까? (0)	2020.05.26
Spring MVC : GET @RequestParam과 같은 복잡한 객체 (0)	2020.05.26
curl에 대한 요청 헤더를 어떻게 설정합니까? (0)	2020.05.26

현재글가능한 두 테이블 중 하나에 MySQL 외래 키를 수행 할 수 있습니까?

procodes

가능한 두 테이블 중 하나에 MySQL 외래 키를 수행 할 수 있습니까?

가능한 두 테이블 중 하나에 MySQL 외래 키를 수행 할 수 있습니까?

'Programming' 카테고리의 다른 글

'Programming'의 다른글

티스토리툴바

가능한 두 테이블 중 하나에 MySQL 외래 키를 수행 할 수 있습니까?

가능한 두 테이블 중 하나에 MySQL 외래 키를 수행 할 수 있습니까?

'Programming' 카테고리의 다른 글

'Programming'의 다른글

관련글

티스토리툴바