one to one relationship

[rjl444]

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

[DA Morgan]

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)

[rjl444]

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.

[Terri]

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.

[rjl444]

that makes sense. one more question, why have 1:1 if it is not very
pratical?

[Erland Sommarskog]

(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

[rjl444]

thanks.

[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