views:

36

answers:

3

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?

+5  A: 

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.

Justin Niessner
doesn't the foreign key also need to be unique itself for 1:1 ? Otherwise TableA may have multiple references i.e say two rows referring to same id in TableB
Misnomer
@misnomer, thank u very much, please see edit
i am a girl
@justin please see edit thank u
i am a girl
@jenny @misnomer - Updated.
Justin Niessner
@justin very very good points thank u very much i didnt consider this
i am a girl
+1  A: 

To create a 1:1 relationship just make the B table column a foreign key or unique. This will ensure that there can be only one column in table B that matches the PK field in table A and that way you effectively get a 1:1 relationship...

kzen
@kzen, thank u very much, please see edit
i am a girl
+1  A: 

You can setup a foreign key and add a constraint for it to be unique. This would setup a 1:1 relationship between your tables.

Misnomer