471,075 Members | 1,184 Online
Bytes | Software Development & Data Engineering Community
Post +

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 471,075 software developers and data experts.

one to one relationship

I created 2 tables with one to one relationship. if I add a record in
table A, how does table B record get created? Does SQL do this
automatically because it is one to one relationship? or do I need to
create a trigger? if i need a trigger, how do I get the ID of new
record to create the same ID in table B?
thanks for any help.
Joe Klein

Jul 23 '05 #1
7 10810
rj****@hotmail.com wrote:
I created 2 tables with one to one relationship. if I add a record in
table A, how does table B record get created? Does SQL do this
automatically because it is one to one relationship? or do I need to
create a trigger? if i need a trigger, how do I get the ID of new
record to create the same ID in table B?
thanks for any help.
Joe Klein


From the standpoint of pure design: What justification is there for
two tables with a 1:1 relationship?
--
Daniel A. Morgan
http://www.psoug.org
da******@x.washington.edu
(replace x with u to respond)
Jul 23 '05 #2
I understand what you are getting at- I can keep the data all in a
single table, but the 2nd table will have fields related to one aspect
of the application, which will be easier to maintain.

Jul 23 '05 #3
If you change the design to a single table you could create a view
containing the fields related to the one aspect of the application.

<rj****@hotmail.com> wrote in message
news:11**********************@g14g2000cwa.googlegr oups.com...
I understand what you are getting at- I can keep the data all in a
single table, but the 2nd table will have fields related to one aspect
of the application, which will be easier to maintain.

Jul 23 '05 #4
that makes sense. one more question, why have 1:1 if it is not very
pratical?

Jul 23 '05 #5
(rj****@hotmail.com) writes:
I created 2 tables with one to one relationship. if I add a record in
table A, how does table B record get created?
You insert it, one way or another.
Does SQL do this automatically because it is one to one relationship?
No. SQL Server can't really see what you are doing. It can tell if the
relation is one-to-one, or one-to-optional-one.
or do I need to create a trigger? if i need a trigger, how do I get the
ID of new record to create the same ID in table B?


Trigger is one way. That presumes that you have information enough in
the trigger to insert that other row. Copying the ids of the inserted
rows in A to table B is a trivial matter:

CREATE TRIGGER A_tri ON A FOR INSERT AS
INSERT B (keycol)
SELECT keycol FROM inserted

Note that a trigger fires once per statement, and must be able to
handle multi-row operatons. But this only possible if all rows in B
beside the key colunm are nullable.

(If you were silly enough to attribute the key column in B as IDENTITY,
you need to change that.)

You can also issue to separate INSERT statments, one for each table. That
would be necessary if you have data for both tables ar this point.
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
Jul 23 '05 #6
thanks.

Jul 23 '05 #7
Stu
Actually, it's more practical than theoretical. While a good design
usually reduces the need for 1:1, there may be situations where the
seperation of data is warranted. For example, how many of you have
seen this:

Warning: The table 'blah' has been created but its maximum row size
(10662) exceeds the maximum number of bytes per row (8060). INSERT or
UPDATE of a row in this table will fail if the resulting row length
exceeds 8060 bytes.
From the Books OnLine (Create Table):


SQL Server can have as many as two billion tables per database and
1,024 columns per table. The number of rows and total size of the table
are limited only by the available storage. The maximum number of bytes
per row is 8,060. If you create tables with varchar, nvarchar, or
varbinary columns in which the total defined width exceeds 8,060 bytes,
the table is created, but a warning message appears. Trying to insert
more than 8,060 bytes into such a row or to update a row so that its
total row size exceeds 8,060 produces an error message and the
statement fails.

Granted, most designs won't have huge data columns, but you might. A
good design will eliminate 80% of your problems, while a great one will
eliminate 90%. You will still always have to deal with an exception to
your design at some point; a 1:1 join is a method of managing some of
those exceptions.

HTH,
Stu

Jul 23 '05 #8

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

reply views Thread by Alcyone Oliveira | last post: by
reply views Thread by Mike Sutton | last post: by
8 posts views Thread by Andante.in.Blue | last post: by
7 posts views Thread by francophone77 | last post: by
4 posts views Thread by Phil Stanton | last post: by
reply views Thread by leo001 | last post: by

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.