• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 3188
  • Last Modified:

How to add Foreign key constraint on (1:1) to (0:1) relationship in MS SQL 2005 using T-SQL/GUI?

Hi, Experts,
I am new to MS SQL 2005, attached is a part of my ERD:

I don't know how to add foreign key constraint between tables: Car(1,1) and SalesInvoice (0:1) using T-SQL/GUI, can anyone help?

Also, are there any differences at all with other similar types of foreign key constraints(like, (1:1)  to (1:*) or (1:1) to (0:*)) implementing on MS SQL using T-SQL/GUI.

Thanks,
-Rick
CarDealerShip.gif
0
RHADMIN
Asked:
RHADMIN
  • 2
  • 2
2 Solutions
 
momi_sabagCommented:
foreign keys in sql server are always implemnted in the same way:
a child table key is pointing to a unique / primary key at the parent
which means, that every relationship is a 1:n where n>=0
add it like this:
alter table [Sales Invoices] add constraint MyConstraintName foreign key([car id]) references car([car id])
0
 
orcicCommented:
Actually, you can have 1:1 relation. You only have to make foreign key to be unique.

ALTER TABLE [Sales Invoices]
ADD CONSTRAINT SalesInvoicesUniqueCarID
UNIQUE NONCLUSTERED ([car id])
0
 
momi_sabagCommented:
no
what orcic wrote will add a unique constraint
not a foreign key

you can create a unique constraint on the car id column in your table and that will make it unique
but you can't achieve a 1:1 relationship using foreign keys alone
0
 
orcicCommented:
of course you need to make foreign key first and then to make it unique (that's what I ment with "You only have to make foreign key to be unique.")
0
 
RHADMINAuthor Commented:
Hi, momi_sabag & orcic:

Thanks for your answers, UNIQUE constraint is what I am looking for(which I originally thought it can be done on Foreign key constraint alone somehow).
Based on the question, another question: how to set a constraints on relations like: (1:1) to (1:5)  or  (1:1) to (1:100), which means how to set minimum & maximum number of relationship check?

Many thanks,
-Rick
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now