Creating relationships between tables

Question

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

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?

Answer 1

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

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.