Creating Schema

Hello!
In a doctor-patient table relationship, I need the patient to only have one
doctor but the doctor can have as many patients as he wants.
What is the easiest way to set this up?
Thank you!Never mind. Got it!
Thank you!
"Bahman" wrote:

> Hello!
> In a doctor-patient table relationship, I need the patient to only have on
e
> doctor but the doctor can have as many patients as he wants.
> What is the easiest way to set this up?
> Thank you!
>|||Never mind. God it!
Thank you!
"Bahman" wrote:

> Hello!
> In a doctor-patient table relationship, I need the patient to only have on
e
> doctor but the doctor can have as many patients as he wants.
> What is the easiest way to set this up?
> Thank you!
>

Creating relationships between tables in different databases

Hello guys,
Does anybody know how can i create a relationship between tables in differen
t databases? Does SQL Server supports this kind of operation?
The main problem is that i have some data that is shared between different d
atabases and i'd like to have some kind of referencial integrity in the data
that is stored on the separated databases by using the same shared tables.
I could use triggers for do
ing that but this will decrease the performance of my databases dramatically
.
Thanks for the help,
Paulo R.Cross database constraints are not supported. You would need
to go the route along the lines of triggers to achieve
something like this.
-Sue
On Mon, 29 Mar 2004 13:41:08 -0800, "paulo"
<anonymous@.discussions.microsoft.com> wrote:

>Hello guys,
>Does anybody know how can i create a relationship between tables in differe
nt databases? Does SQL Server supports this kind of operation?
>The main problem is that i have some data that is shared between different database
s and i'd like to have some kind of referencial integrity in the data that is stored
on the separated databases by using the same shared tables. I could use triggers fo
r d
oing that but this will decrease the performance of my databases dramatically.d">
>Thanks for the help,
>Paulo R.

Creating relationships between different databases

Hello guys,
Does anybody know how can i create a relationship between tables in different databases? Does SQL Server supports this kind of operation?
The main problem is that i have some data that is shared between different databases and i'd like to have some kind of referencial integrity in the data that is stored on the separated databases by using the same shared tables. I could use triggers for do
ing that but this will decrease the performance of my databases dramatically.
Thanks for the help,
Paulo R.
Even if SQL Server did support it (which I don't believe it does) it would
be a very poor design. What if the databases were installed on separate
servers? That would be a real mess. If the tables are that important for a
database then some sort of replication from one database to the other would
be a better solution.
Jim
"Paulo" <paulo@.specforeclosure.com> wrote in message
news:4B7733D7-AFCE-4992-9734-59C7FA38194A@.microsoft.com...
> Hello guys,
> Does anybody know how can i create a relationship between tables in
different databases? Does SQL Server supports this kind of operation?
> The main problem is that i have some data that is shared between different
databases and i'd like to have some kind of referencial integrity in the
data that is stored on the separated databases by using the same shared
tables. I could use triggers for doing that but this will decrease the
performance of my databases dramatically.
> Thanks for the help,
> Paulo R.
|||>>Does SQL Server supports this kind of operation?<<
Cross database ref. integrity is not supported in SQL Server.
BOL: "FOREIGN KEY constraints can reference only tables within the same database on the same
server. Cross-database referential integrity must be implemented through triggers. For more
information, see CREATE TRIGGER. "
So this can be accomplished through triggers.
See following topic in bol.
"FOREIGN KEY Constraints"
Vishal Parkar
vgparkar@.yahoo.co.in

Creating relationships between different databases

Hello guys,
Does anybody know how can i create a relationship between tables in different databases? Does SQL Server supports this kind of operation?
The main problem is that i have some data that is shared between different databases and i'd like to have some kind of referencial integrity in the data that is stored on the separated databases by using the same shared tables. I could use triggers for do
ing that but this will decrease the performance of my databases dramatically.
Thanks for the help,
Paulo R.
Triggers work, but as you point out there are performance problems.
You can't do cross database constraints or you will get a message like:
Server: Msg 1763, Level 16, State 1, Line 1
Cross-database foreign key references are not supported. Foreign key
'master.dbo.jobs'.
I'm not sure what to suggest other than triggers.
"Paulo" <paulo@.specforeclosure.com> wrote in message
news:A4BC16DB-C2BB-4EC9-88C4-284F0BA4E957@.microsoft.com...
> Hello guys,
> Does anybody know how can i create a relationship between tables in
different databases? Does SQL Server supports this kind of operation?
> The main problem is that i have some data that is shared between different
databases and i'd like to have some kind of referencial integrity in the
data that is stored on the separated databases by using the same shared
tables. I could use triggers for doing that but this will decrease the
performance of my databases dramatically.
> Thanks for the help,
> Paulo R.
|||Paulo,
if you really want this functionality, then you could enforce the constraints in your stored procedures, assuming database access is via sps, or use triggers, or use filegroups. This last option is unconventional and in my opinion not ideal, but it is pos
sible to put all your tables into one database and declare constraints there. What were databases now become filegroups. You can back up individual filegroups like you back up databases. I know one financial services institution that uses this methodology
. However, this is limiting - there is no posibility of a point-in-time restore and you need to be able to get hold of the last transaction log if you want to do a full restore. However, if you only use database backups, then this is an option to consider

Creating relationship between column and replacement column

Hello,
I have a table (tblManchineParts) that is the list of all machine parts.
The second table (tblReplacementParts) is a list of all the machine parts
and the machine part that replaced it. I tried to create two foreign key
relationships to enforce Referential Integrity:
tblMachineParts.MachinePart_ID and tblReplacementParts.MachinePart_ID
and
tblMachineParts.MachinePart_ID and tblReplacementParts.ReplacementPart_ID
When I did this, I got the error: Introducing second foreign key may cause
cycles or multiple cascade paths. My question is, how do I enforce
referential integrity on both the fields in the table tblReplacementParts
since their values have to be in the table tblMachineParts?
--
Thanks in advance,
Steven
tblMachineParts
--
MachinePart_ID (varchar 255)
tblReplacementParts
--
MachinePart_ID (varchar 255)
ReplacementPart_ID (varchar 255)
examples
(tblMachineParts)
MachinePart_ID
--
Part01
part02
Part03
(tblReplacementParts)
MachinePart_ID ReplacementPart_ID
-- --
Part02 Part03Hi, Steven
You can enforce referential integrity, you just can't use cascade
updates. For more informations, see this thread:
http://groups.google.com/group/microsoft.public.sqlserver.programming/browse_frm/thread/a802401b5d653f00
Razvan

Creating relationship between column and replacement column

Hello,
I have a table (tblManchineParts) that is the list of all machine parts.
The second table (tblReplacementParts) is a list of all the machine parts
and the machine part that replaced it. I tried to create two foreign key
relationships to enforce Referential Integrity:
tblMachineParts.MachinePart_ID and tblReplacementParts.MachinePart_ID
and
tblMachineParts.MachinePart_ID and tblReplacementParts.ReplacementPart_ID
When I did this, I got the error: Introducing second foreign key may cause
cycles or multiple cascade paths. My question is, how do I enforce
referential integrity on both the fields in the table tblReplacementParts
since their values have to be in the table tblMachineParts?
Thanks in advance,
Steven
tblMachineParts
--
MachinePart_ID (varchar 255)
tblReplacementParts
--
MachinePart_ID (varchar 255)
ReplacementPart_ID (varchar 255)
examples
(tblMachineParts)
MachinePart_ID
--
Part01
part02
Part03
(tblReplacementParts)
MachinePart_ID ReplacementPart_ID
-- --
Part02 Part03Hi, Steven
You can enforce referential integrity, you just can't use cascade
updates. For more informations, see this thread:
http://groups.google.com/group/micr...802401b5d653f00
Razvan

Creating relationship between column and replacement column

Hello,
I have a table (tblManchineParts) that is the list of all machine parts.
The second table (tblReplacementParts) is a list of all the machine parts
and the machine part that replaced it. I tried to create two foreign key
relationships to enforce Referential Integrity:
tblMachineParts.MachinePart_ID and tblReplacementParts.MachinePart_ID
and
tblMachineParts.MachinePart_ID and tblReplacementParts.ReplacementPart_ID
When I did this, I got the error: Introducing second foreign key may cause
cycles or multiple cascade paths. My question is, how do I enforce
referential integrity on both the fields in the table tblReplacementParts
since their values have to be in the table tblMachineParts?
Thanks in advance,
Steven
tblMachineParts
MachinePart_ID (varchar 255)
tblReplacementParts
MachinePart_ID (varchar 255)
ReplacementPart_ID (varchar 255)
examples
(tblMachineParts)
MachinePart_ID
Part01
part02
Part03
(tblReplacementParts)
MachinePart_ID ReplacementPart_ID
-- --
Part02 Part03
Hi, Steven
You can enforce referential integrity, you just can't use cascade
updates. For more informations, see this thread:
http://groups.google.com/group/micro...02401b5d653f00
Razvan

Creating PK/FK Relationship

Ok, if I have a table (Title_Author) that has the ISBN and Au_ID (author id) and I want to make sure that the Titles table ISBN column has a PK/FK relationship with the Title_Author table, I should be able to create a relationship to (just) the ISBN column. No? Well, the CONSTRAINT x FOREIGN KEY clause says it's an invalid relationship.

Ideas?

is the ISBN column the primary key (or part of a composite primary key) on the Titles table?

-Darren

|||Part of a composite key.

Creating Foreign Key constraint

I have two tables; each has multi-column primary keys. I need to create a foreign key relationship between the two tables.

Sales:

DKey, int, PK

OKey,int, PK

RKey,int, PK

...

Rep:

Bkey, int, PK, identity

Rkey, int, PK

...

When I try to like Rkey from both tables as a foreign key relationship, I get an error that the columns in one table "do not match an existing primary key or UNIQUE constraint. (I've tried it both ways and get the same error.)

How can I link these two tables?

Thanks.

Make an unique index on Rkey from Rep or Sales depends on the table you can delegate "Primary Key" (use Database Diagram for usability)

|||

Since you did not include rationale for the key selection, this is going to be just a guess.

The purpose of a Primary Key is to uniquely identify a row of data. It looks like each Rep is uniquely identified with the IDENTITY field BKey. I'm not sure why you have RKey also involved in the Primary Key for Rep.

You could recreate the Primary key for Rep using only the Bkey, and then add the Bkey field to Sales to relate Sales to Rep.

OR, if RKey is unique, use it as a Primary Key.

However, if for some silly reason you HAVE to have a combination of Bkey and Rkey as the Primary key in Rep, you will have to add both columns to Sales in order to create the relationship.

As a side note, the use of Bkey, Rkey, etc. for column names is very 'odd', and not only adds confusion, but will be difficult to maintain the pattern for all tables. A 'best practice' naming convention for an IDENTITY field (such as Bkey) is to use the table name and the suffix [ID]. So a preferred name for Bkey would be RepID. The name instantly communicates where the field comes from and what it is, expecially useful when it is added to another table.

|||

I was trying to simplify the question....I probably should have included more details. I'm sorry, I never know how much detail to go into.

What I'm actually trying to do is create a many to many link between:

Sales:

DateKey, int, PK

OrganizationKey, int, PK

CustomerKey, int, PK

RepBridgeKey, int, PK

....

RepBridge:

RepBridgeKey, int, PK, identity

RepKey, int, PK

Rep:

RepKey, int, PK, identity

.....

I can link RepBridge and Rep via RepKey but not RepBridge with Sales via RepBridgeKey.

From your answer, it looks like I would have to include RepKey in my Sales table and do the FK link on both fields?

Thanks.

|||

No, I think you can only make an unique index on RepBridgeKey from RepBridge (is possible because is identity)

You can make the following links vis-a-vis your vision of table schema:

Rep <RepKey-->RepBridge

RepBridge <RepBridgeKey>Sales

|||

John,

In RepBridge, I'm still not sure why you need RepKey as part of the Primary Key. It seems like BridgeRepKey is a unique identifier and would adequately serve as the PK (and the FK in Sales).

RepBridgeKey 'should' be all you need.

However, if, as said earlier, there is a business reason to complicate the Primary key in BridgeRep by adding RepKey -then you will have to add RepKey to Sales -at which point, the RepBridge table seems unnecessary. You would have to duplicate the rows in Sales for each Rep if there are multiple Reps involved with a single Sale.

Your sales table probably needs a better defined primary key. Typically a unique sales identifier is created -something like an INVOICE number, SalesOrder number, etc. In your Sales table above, only one sale would be allowed per day per Rep. That may be reasonable, but is it realistic? I would prefer a [SalesID int IDENTITY Primary Key] field

Typically, a 'bridging' table will have an IDENTITY field as PK, and the PK's from BOTH others tables as FK.

RepBridge

RepBridgeKey, int IDENTITY PK

RepKey FK to Rep

SalesKey Fk to Sales

|||

I see what you're saying....the bridge table does not need the RepKey as part of its primary key. I was thinking that each new sales rep would have to trigger a new bridge record but this can be done with only one PK identity field.

There is a reason behind the key fields in the sales table, but perhaps I shall also revisit that side issue.

Thanks again.