这些表之间是什么关系?

2022-01-20 00:00:00 foreign-keys relationship sql-server relational-database composite-primary-key

我有两个表，它们的外键是彼此的主键.这个数据库是法语的.我将翻译我希望您理解的两个表格.

工作室美食 ==> 厨房
Cuisinier == > 厨师

所以在这张图片中，我们看到在 Kitchen 表中我们有一个由 Cooking chef 表中的 FK 引用的 PK；在 Cooking chef 表中，我们有一个由 Kitchen 表中的 FK 引用的 PK.所以我很困惑.我不明白这些表之间的这种关系.

我希望检查我为创建这两个表所做的查询是否正确

创建表 [ATELIER CUISINE] (NumCuisine INT NOT NULL PRIMARY KEY，TelCuisine VARCHAR(50) 非空)创建表 CUISINIER (NumCuisinier INT NOT NULL PRIMARY KEY，NomCuis VARCHAR(50) 非空，DateEmb 日期不为空，NumCuisine INT NOT NULL 约束 FK_CUISINIER_NumCuisine 外键参考 [ATELIER CUISINE](NumCuisine)

在此处查看图片:
餐厅数据库关系模型

在此处查看图片:
某些表的示例记录

解决方案

表(基础和查询结果)表示应用程序关系.

来自(早期版本)这个答案:

<块引用>

对于每个基表，DBA 都会给出一个谓词——一种由列名参数化的自然语言填充(命名)空白语句模板.

-- id 为 NUMCUISINIER 的厨师名为 NOMCUIS 和 ...CUISINIER(NumCuisinier, NomCuis, ...)

<块引用>

基表包含行，这些行使用其列的值填充(命名的)空白，构成真正的陈述，即 proposition.

CUSINIER数字美食 |名誉 |...----------------------------1 |杜兰德 |... -- id 为 1 的厨师的名字为DURAND"并且 ......-- AND 对于每个缺席的行 (NUMCUSINIER, NOMCUIS, ...),不是(ID 为 NUMCUSINIER 的厨师的名字为 NOMCUIS 和 ...)

<块引用>

每个 SQL 表达式/子句都将旧表值转换为新值，其中包含的行从可以用原始谓词表示的某个新谓词中得出正确的语句.

<块引用>

R JOIN S的谓词是R的谓词AND与S的谓词相加.R ON/WHEREcondition的谓词是R ANDed的谓词条件.

/* rows whereid 为 c.NUMCUISINIER 的厨师名为 c.NOMCUIS 和 ...和带有 id a.NUMCUISINE 的厨房......和 c.NUMCUISINE = a.NUMCUISINE*/CUISINIER c 在 c.NumCuisine = a.NumCuisine 上加入 ATELIER_CUISINE a

因此我们通过编写一个 SQL 表达式进行查询，其关联谓词表征我们想要的行的应用程序关系(也称为关联).

FK(外键)不是关系.

FK 约束被某些人称为关系，但事实并非如此.它们是事实.

表的 FK 是一组列.FK"也用于表示我们拥有 FK 时所拥有的关联约束.就像每个约束一样，在每个数据库状态下都是一个真实的陈述.(等效地，每个应用程序情况.)FK 约束表示某个表中某些列的值也是某个其他表中某些列的值，它们形成 CK(候选键).(等效地，它表示如果某些值/实体满足某个应用程序关系，那么它们中的一些加上一些其他值/实体满足某个其他应用程序关系，其中这些值/实体形成一个 CK.)PK(主键)只是一些你决定称之为PK的CK.)

FK 约束具有一定的关联应用关系，但这不是关系"用于FK(约束)"时的含义.(FK"也用于表示 FK 的列的子行值，或单列 FK 的列的行中的值.)

您需要了解每个表格的含义.

设计者必须提供谓词以及架构.您需要找出这些表格的含义.然后用它们来表达你的查询谓词.然后转换为 SQL.

有时我们通过常识和命名或多或少地成功地猜测谓词.FK 和其他约束有助于猜测.

常识、名称、PK(带下划线?)和 FK(#"?)建议您喜欢的表含义:

-- id 为 NUMCUISINIER 的厨师名为 NOMCUIS，开始日期为 DATEMB，在厨房工作，ID 为 NUMCUISINECUISINIER(NumCuisinier, NomCuis, NateEmb, NumCuisine)-- id 为 NUMCUISINE 的厨房有电话号码 TELCUISINE 和 id 为 NUMCUISNIER 的厨师作为主厨ATELIER_CUISINE(NumCuisine, TelCuisine, NumCuisinier)

查询不需要 FK

在上面的 SQL 查询中，使谓词成为真命题的行总是返回的行.每个 NumCuisiner 或 NumCuisine 值(即它们是否是 PK)或值是否必须出现在另一个表中(即它们是否是FK).或者任何其他约束是什么.

我们需要知道要查询的谓词.当我们了解它们时我们不需要知道任何约束.我们不需要知道 FK.

FK、CK、PK、备用键和 UNIQUE 列集(超级键)与查询无关，除非您知道某事物是超级键，因为它是超级键，那么您可以编写涉及从单行结果中提取值的查询.但是您可以在不提取的情况下表达相同的结果.

I have two tables that have foreign keys to each other's primary key. This DB is in French. I will translate the two tables that I want to you to understand.

Atelier Cuisine ==> Kitchen
Cuisinier == > Cooking chef

So in this picture we see that in the Kitchen table we have a PK referenced by the FK from the Cooking chef table; in the Cooking chef table we have a PK referenced by the FK from the Kitchen table. So I am confused. I don't understand this kind of relationship between these tables.

And I hope to check my query that I did to create these two tables if its correct

CREATE TABLE [ATELIER CUISINE] ( 

NumCuisine INT NOT NULL PRIMARY KEY,
TelCuisine VARCHAR(50) NOT NULL
)

CREATE TABLE CUISINIER (

NumCuisinier  INT NOT NULL PRIMARY KEY,
NomCuis  VARCHAR(50) NOT NULL,
DateEmb DATE NOT NULL,
NumCuisine INT NOT NULL CONSTRAINT FK_CUISINIER_NumCuisine FOREIGN KEY REFERENCES [ATELIER CUISINE](NumCuisine)

See the Image here:
Relationship model of the restaurant database

See the Image here:
Example records for some tables

解决方案

Tables (base and query results) represent application relationships.

From (an earlier version of) this answer:

For every base table, the DBA gives a predicate--a natural language fill-in-the-(named-)blanks statement template parameterized by column names.

-- chef with id NUMCUISINIER has name NOMCUIS and ...
CUISINIER(NumCuisinier, NomCuis, ...)

A base table holds the rows that, using its columns' values to fill in the (named) blanks, make a true statement aka proposition.

CUISINIER
NumCuisinier | NomCuis | ...
----------------------------
1            | DURAND  | ...  -- chef with id 1 has name 'DURAND' and ...
...

-- AND for every absent row (NUMCUISINIER, NOMCUIS, ...),
    NOT (chef with id NUMCUISINIER has name NOMCUIS and ...)

Each SQL expression/clause transforms an old table value to a new value holding the rows that make a true statement from some new predicate that can be expressed in terms of the original's predicate.

The predicate of R JOIN S is the predicate of R ANDed with the predicate of S. The predicate of R ON/WHEREcondition is the predicate of R ANDed with condition.

/* rows where
     chef with id c.NUMCUISINIER has name c.NOMCUIS and ...
AND kitchen with id a.NUMCUISINE ...
AND c.NUMCUISINE = a.NUMCUISINE
*/
CUISINIER c join ATELIER_CUISINE a on c.NumCuisine = a.NumCuisine

So we query by writing an SQL expression whose associated predicate characterizes the application relationship (aka association) whose rows we want.

FKs (foreign keys) are not relationships.

FK constraints are called relationships by some people, but they are not. They are facts.

A FK of a table is a set of columns. "FK" is also used to mean an associated constraint that we have when we have a FK. Which like every constraint is a true statement in every database state. (Equivalently, every application situation.) A FK constraint says that values for certain columns in a certain table are also values for certain columns in a certain other table where they form a CK (candidate key). (Equivalently, it says that if some values/entities satisfy a certain application relationship then some of them plus some other values/entities satisfy a certain other application relationship where the values/entities form a CK.) A PK (primary keys) is just some CK that you decided to call PK.)

A FK constraint has a certain associated application relationship, but that's not what is meant when "relationship" is used for "FK (constraint)". ("FK" is also used to mean a subrow value for the columns of a FK, or a value in a row for the column of a one-column FK.)

You need to know what each table means.

Predicates must be supplied by the designer along with a schema. You need to find out what the tables mean. Then express your query predicate in terms of them. Then convert to SQL.

Sometimes we guess at the predicates more or less successfully via common sense and naming. FKs and other constraints can help with guessing.

Common sense, names, PKs (underlined?) and FKs ("#"?) suggest table meanings for you like:

-- chef with id NUMCUISINIER has name NOMCUIS and start date DATEEMB and works in kitchen with id NUMCUISINE
CUISINIER(NumCuisinier, NomCuis, NateEmb, NumCuisine)
-- kitchen with id NUMCUISINE has phone number TELCUISINE and chef with id NUMCUISINIER as head chef
ATELIER_CUISINE(NumCuisine, TelCuisine, NumCuisinier)

FKs are not needed to query

In the SQL query above the rows that make the predicate into a true proposition are always the rows returned. It doesn't matter how many rows there are per NumCuisiner or NumCuisine value (ie whether they are PKs) or whether a value must appear in another table (ie whether they are FKs). Or what any other constraint is.

We need to know the predicates to query. When we know them we don't need to know any constraints. We don't need to know FKs.

FKs, CKs, PKs, alternate keys and UNIQUE column sets (superkeys) are irrelevant to querying except that if you know something is a superkey because of one then you can write queries involving extracting a value from a one-row result. But you could have expressed the same result without extractions.

相关文章