make"列顯然不是候選鍵.從這個數據樣本中你可以看出，模型"看起來就像一個候選鍵.實際上，幾年前我不得不研究這個問題，我發現只有幾個模型是由多個制造商制造的，而且沒有一個是最新的.但這并不重要.

無論這里的候選鍵是{make, model}還是{model}，這個表都是6NF.¹如果我們假設唯一的候選鍵是{make, model}，我可能在標準 SQL 中像這樣實現它.

創建表 car_models (使 varchar(15) 不為空，模型 varchar(15) 不為空，主鍵(品牌、型號));

<塊引用>

File2 包含汽車制造商、型號、年份.

所以感興趣的數據看起來像這樣.

按照上表中鍵的假設，這張表只有一個候選鍵，并且只有一個附加屬性.它也屬于 6NF.SQL 版本可能如下所示.

創建表 car_model_years (使 varchar(15) 不為空，模型 varchar(15) 不為空，model_year 整數不為空檢查(1886 年和 2099 年之間的模型年份)，主鍵(品牌、型號、型號年份)，外鍵 (make, model) 引用 car_models (make, model));

這些表沒有冗余數據.您不能在不破壞語義或損害數據完整性的情況下刪除任何列.外鍵在car_model_years"的行中重復，但這不是多余的——這正是外鍵用于.

為什么我不應該使用 Make、Model 復合主鍵?

作為一個理論(關系)問題，不，沒有.如果您從 6NF 開始，添加代理 ID 號會非規范化該表.(6NF 需要一個單個候選鍵.)即使您確實添加了代理 ID 號，您仍然必須聲明 {make, model}作為 not null unique.未能聲明該約束會使表格最終看起來像這樣.

作為一個實際問題，而不是理論(關系)問題，這些 6NF 表可能比使用代理 ID 號對它們進行非規范化表現更好.例如，基于品牌和型號的car_model_years"查詢通常使用僅索引掃描——他們根本不需要讀取基表.

作為另一個實際問題，一些應用程序框架處理除 id 號之外的任何鍵都很糟糕.恕我直言，這證明使用更好的框架是合理的，但不會影響數據庫的結構.

<小時>1. ……一個‘常規’相關變量在 6NF 中當且僅當它由一個鍵組成，最多加上一個附加屬性."Date，CJ，深入數據庫:從業者的關系理論，第 147 頁.常規 relvar 是非時間 relvar.

I know it's been asked many times, but I wanted to explain my scenario, and see if there are any benefits of using Identity column as primary key instead of using a composite primary key.

I'm currently reading two text files: File1 has Make & Model of car, while File2 has Make, Model, Year of car. Make, Model combination in File2 will always be in File1.

So I created table [Car] composed of columns of MakeId (identity), Make and Model. Data for table [Car] looks like this. The data in [Car] is an exact replica of File1:

[MakeId]       [Make]      [Model]
1              HONDA       ACCORD
2              HONDA       CIVIC
3              FORD        FOCUS
4              FORD        ESCORT

For File2, I created table [CarYear] with columns CarYearId (identity), Make, Model, Year. The data in [CarYear] is an exact replica of File2:

[CarYearId] [Make]      [Model]     [Year]
1           HONDA       ACCORD      2002
2           HONDA       ACCORD      2001
3           HONDA       ACCORD      2004
4           HONDA       CIVIC       1998
5           FORD        FOCUS       1998
6           FORD        ESCORT      2001
7           FORD        ESCORT      2002

Is there any reason why I shouldn't use Make, Model a composite primary key? Given my case, since I have Make & Model in both tables, I can easily just search the 2nd table directly instead of having to do inner joins.

File1 has Make & Model of car . . .

So the data of interest looks like this.

make        model
--
HONDA       ACCORD
HONDA       CIVIC
FORD        FOCUS
FORD        ESCORT

The column "make" is clearly not a candidate key. As far as you can tell from this sample of data, "model" looks like a candidate key. I actually had to research this issue several years ago, and I found only a couple of models that were built by more than one manufacturer, and none of those were current. But that doesn't really matter.

Whether the candidate key here is {make, model} or {model}, this table is in 6NF.¹ If we assume that the only candidate key is {make, model}, I might implement it like this in standard SQL.

create table car_models (
  make varchar(15) not null,
  model varchar(15) not null,
  primary key (make, model)
);

File2 has Make, Model, Year of car.

So the data of interest looks like this.

make        model       year
--
HONDA       ACCORD      2002
HONDA       ACCORD      2001
HONDA       ACCORD      2004
HONDA       CIVIC       1998
FORD        FOCUS       1998
FORD        ESCORT      2001
FORD        ESCORT      2002

Following the assumptions about the key in the previous table, this table has only one candidate key, and it has only one additional attribute. It, too, is in 6NF. A SQL version might look like this.

create table car_model_years (
  make varchar(15) not null,
  model varchar(15) not null,
  model_year integer not null
    check (model_year between 1886 and 2099),
  primary key (make, model, model_year),
  foreign key (make, model) references car_models (make, model)
);

These tables have no redundant data. You can't remove any columns without breaking the semantics or compromising the integrity of the data. Foreign keys are repeated down the rows of "car_model_years", but that's not redundant--that's exactly what foreign keys are for.

Is there any reason why I shouldn't use Make, Model a composite primary key?

As a theoretical (relational) matter, no, there isn't. If you start in 6NF, adding a surrogate ID number denormalizes that table. (6NF requires a single candidate key.) Even if you do add a surrogate ID number, you still have to declare {make, model} as not null unique. Failure to declare that constraint makes a table liable to end up looking like this.

model_id  make   model
--
1         Honda  Accord
2         Honda  Accord
3         Honda  Accord

As a practical matter, not a theoretical (relational) matter, these 6NF tables will probably perform better than denormalizations of them using surrogate ID numbers. For example, queries on "car_model_years" that are based on make and model will generally use an index-only scan--they won't have to read the base table at all.

As another practical matter, some application frameworks deal poorly with any key besides an id number. IMHO, this justifies using a better framework, though, not compromising the structure of your database.

這篇關于具有以下場景的復合主鍵或主代理鍵?的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，也希望大家多多支持html5模板網！