在表之间创建关系 [英] Creating relationships between tables

问题描述

我的问题专门关于sql-server，但任何数据库背景的人都可以回答

My question specifically about sql-server, but probably can be answered by anyone with any database background

如果我希望表A在特定列上与表B具有1:1关系，我应该以某种方式修改CREATE TABLE语句以识别这种关系，或者这是根本没有做的事情(而是由逻辑处理)?

If I want table A to have a 1:1 relationship with table B on a certain column, should I somehow modify the CREATE TABLE statement to identify this relationship or is this something that is not done at all (and rather it is handled by logic)?

编辑
我的问题的第二部分是:将其嵌入到代码中有什么意义?为什么不仅仅对选择/更新进行逻辑处理?

EDIT
The second part of my question is: what is the point of embedding this into the code? why not just handle it logically on selects/updates?

推荐答案

您需要做的就是让表A中的列成为表B主键的外键:

All you need to do is have the column in Table A be a foreign key to the primary key of Table B:

create table TableB (
    Id int primary key identity(1,1),
    Name varchar(255))

create table TableA (
    Id int primary key identity(1,1),
    Name varchar(255),
    TableBRelation int unique,
    foreign key (TableBRelation) references TableB (Id))

SQL可能并不完美，但是您应该可以理解.

The SQL may not be perfect but you should be able to get the idea.

关于为什么要在数据库中而不是仅在应用程序逻辑中执行此操作的原因:

As for why you would want to do this in the database rather than just application logic:

• 其他数据库或开发人员可能会尝试访问您的数据库.您是否希望他们能够创建可能破坏您的应用程序的无效数据?否.这是参照完整性的要点之一.

• Other databases or developers may try to access your database. Do you want them to be able to create invalid data that may break your application? No. That's one of the points of referential integrity.

在某些时候，有人将不得不维护您的应用程序.在数据库级别定义密钥将清楚地识别数据之间的关系，而不需要开发人员来挖掘您的应用程序代码.

At some point, somebody is going to have to maintain your application. Defining your keys at the database level will clearly identify relationships between your data rather than requiring the develop to dig through your application code.

这篇关于在表之间创建关系的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！