|
|
relationship question (conceptual) - beginner |
|
Author |
Message |
Ekul
|
Posted: Thu Dec 07 12:37:36 CST 2006 |
Top |
SQL Server Developer >> relationship question (conceptual) - beginner
Hi All.
I need make one relationship between 3 tables, but I don't know how to make
this.
Think that I have one table:
'clients'
and one cliente can be an 'person' or an 'company'.
How is the correct way to make this?
'persons' 'companys'
| |
\---------------/
|
clientes
I think in same thing like this, but I thig that is wrong:
CREATE TABLE person
(
CONSTRAINT person_id__person__pk
PRIMARY KEY CLUSTERED(person_id),
--------------------------------------------------------------------------
person_id BIGINT IDENTITY(1,1)
NOT NULL
-- ...
)
CREATE TABLE company
(
CONSTRAINT company_id__company__pk
PRIMARY KEY CLUSTERED(company_id),
-------------------------------------------------------------------------
company_id BIGINT IDENTITY(1,1)
NOT NULL
-- ...
)
CREATE TABLE clients
(
CONSTRAINT clients_id__clients__pk
PRIMARY KEY CLUSTERED(clients_id),
--------------------------------------------------------------------------
CONSTRAINT clients_id1__clientes__fk
FOREIGN KEY(clients_id) REFERENCES person(person_id),
CONSTRAINT clients_id2__clientes__fk
FOREIGN KEY(clients_id) REFERENCES company(company_id),
--------------------------------------------------------------------------
clients_id BIGINT IDENTITY(1,1)
NOT NULL
is_person BIT
NOT NULL
-- ...
)
THANKS
SQL Server47
|
|
|
|
|
David
|
Posted: Thu Dec 07 12:37:36 CST 2006 |
Top |
SQL Server Developer >> relationship question (conceptual) - beginner
> Hi All.
> I need make one relationship between 3 tables, but I don't know how to
> make this.
>
> Think that I have one table:
>
> 'clients'
>
> and one cliente can be an 'person' or an 'company'.
>
> How is the correct way to make this?
>
> 'persons' 'companys'
> | |
> \---------------/
> |
> clientes
>
>
> I think in same thing like this, but I thig that is wrong:
>
>
>
>
>
>
> CREATE TABLE person
>
> (
>
> CONSTRAINT person_id__person__pk
>
> PRIMARY KEY CLUSTERED(person_id),
>
> --------------------------------------------------------------------------
>
>
> person_id BIGINT IDENTITY(1,1)
>
> NOT NULL
>
> -- ...
>
> )
>
> CREATE TABLE company
>
> (
>
> CONSTRAINT company_id__company__pk
>
> PRIMARY KEY CLUSTERED(company_id),
>
> -------------------------------------------------------------------------
>
>
> company_id BIGINT IDENTITY(1,1)
>
> NOT NULL
>
> -- ...
>
> )
>
>
>
> CREATE TABLE clients
>
> (
>
> CONSTRAINT clients_id__clients__pk
>
> PRIMARY KEY CLUSTERED(clients_id),
>
> --------------------------------------------------------------------------
>
> CONSTRAINT clients_id1__clientes__fk
>
> FOREIGN KEY(clients_id) REFERENCES person(person_id),
>
> CONSTRAINT clients_id2__clientes__fk
>
> FOREIGN KEY(clients_id) REFERENCES company(company_id),
>
> --------------------------------------------------------------------------
>
> clients_id BIGINT IDENTITY(1,1)
>
> NOT NULL
>
> is_person BIT
>
> NOT NULL
>
> -- ...
>
> )
>
>
>
> THANKS
>
>
CREATE TABLE client
(client_id BIGINT NOT NULL
PRIMARY KEY);
CREATE TABLE person
(client_id BIGINT NOT NULL
PRIMARY KEY REFERENCES client (client_id));
CREATE TABLE company
(client_id BIGINT NOT NULL
PRIMARY KEY REFERENCES client (client_id));
--
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--
|
|
|
|
|
petery
|
Posted: Thu Dec 07 21:18:34 CST 2006 |
Top |
SQL Server Developer >> relationship question (conceptual) - beginner
Hello,
I agree with David's suggestion might be most efficient. However, if this
does not meet your requirement, you may also want to use trigger to keep
the reference integration of the table. For example, you may use code like
following:
create trigger clients_check on clients for insert as
begin
begin
end
else begin
end
begin
RAISERROR ('client does not exist', 16, 1)
ROLLBACK TRANSACTION
end
End
If you have any commments or feedback, please feel free to let's know.
Best Regards,
Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Community Support
==================================================
Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
ications
<http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx>.
Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
<http://msdn.microsoft.com/subscriptions/support/default.aspx>.
==================================================
This posting is provided "AS IS" with no warranties, and confers no rights.
|
|
|
|
|
petery
|
Posted: Tue Dec 12 03:08:45 CST 2006 |
Top |
SQL Server Developer >> relationship question (conceptual) - beginner
Hi,
I'm still interested in this issue. Did the suggestions help in resolving
the problem? If you have any comments or questions, please feel free to
let's know. We look forward to hearing from you.
Best Regards,
Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Partner Support
=====================================================
This posting is provided "AS IS" with no warranties, and confers no rights.
======================================================
|
|
|
|
|
|
|