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.

Who is Participating?
momi_sabagConnect With a Mentor Commented:
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])
orcicConnect With a Mentor Commented:
Actually, you can have 1:1 relation. You only have to make foreign key to be unique.

ALTER TABLE [Sales Invoices]
ADD CONSTRAINT SalesInvoicesUniqueCarID
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
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.")
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,
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.