Constraint

13 Jan 2006 3:48 PM

Olav

Hi,
we want to check the paystatusid in the table Treatement
CREATE TABLE [Treatement] (
    [treatementid] int IDENTITY(1,1) NOT NULL ,
    [customerid] int NOT NULL ,
    [paystatusid] int NOT NULL , .....
and
CREATE TABLE [Paystatus] (
    [paystatusid] int IDENTITY(1,1) NOT NULL ,
    [customerid] int NOT NULL ,...

so that the Paystatus it refers to has the same customerid.
It is also accepted that paystatusid of the Treatement table is 0 and hence
dont refer to a Paystatus.

Olav

13 Jan 2006 4:26 PM

Rick Sawtell

Show quote

"Olav" <O***@discussions.microsoft.com> wrote in message
news:B72A4068-27AB-4C99-83CC-982ABB0DC79A@microsoft.com...
> Hi,
> we want to check the paystatusid in the table Treatement
> CREATE TABLE [Treatement] (
> [treatementid] int IDENTITY(1,1) NOT NULL ,
> [customerid] int NOT NULL ,
> [paystatusid] int NOT NULL , .....
> and
> CREATE TABLE [Paystatus] (
> [paystatusid] int IDENTITY(1,1) NOT NULL ,
> [customerid] int NOT NULL ,...
>
> so that the Paystatus it refers to has the same customerid.
> It is also accepted that paystatusid of the Treatement table is 0 and
> hence
> dont refer to a Paystatus.
>
> Olav
>

You have a few choices here (listed in my order of preference)

1. Set up your FK constraint and include a 0 paystatusid in the PayStatus
table.

2. Use a trigger to handle your special case of a 0 paystatusid.

3. Use a stored procedure to perform these validations.

Rick Sawtell
MCT, MCSD, MCDBA

13 Jan 2006 8:21 PM

Olav

Thanks Rick,
i am temped by the FK alternative:
But there is an foreign key on the customerid referencing the customer
table. So in the Treatement table customerid will alway have a value but
paystatusid can be 0 meaning it is not yet connected to an Paystatus object.
Still possible? If still so please describe it.

Regards,
Olav

Show quote

"Rick Sawtell" wrote:

>
> "Olav" <O***@discussions.microsoft.com> wrote in message
> news:B72A4068-27AB-4C99-83CC-982ABB0DC79A@microsoft.com...
> > Hi,
> > we want to check the paystatusid in the table Treatement
> > CREATE TABLE [Treatement] (
> > [treatementid] int IDENTITY(1,1) NOT NULL ,
> > [customerid] int NOT NULL ,
> > [paystatusid] int NOT NULL , .....
> > and
> > CREATE TABLE [Paystatus] (
> > [paystatusid] int IDENTITY(1,1) NOT NULL ,
> > [customerid] int NOT NULL ,...
> >
> > so that the Paystatus it refers to has the same customerid.
> > It is also accepted that paystatusid of the Treatement table is 0 and
> > hence
> > dont refer to a Paystatus.
> >
> > Olav
> >
>
> You have a few choices here (listed in my order of preference)
>
> 1. Set up your FK constraint and include a 0 paystatusid in the PayStatus
> table.
>
> 2. Use a trigger to handle your special case of a 0 paystatusid.
>
> 3. Use a stored procedure to perform these validations.
>
> Rick Sawtell
> MCT, MCSD, MCDBA
>
>
>
>

13 Jan 2006 8:35 PM

David Portas

Olav wrote:
Show quote

> Hi,
> we want to check the paystatusid in the table Treatement
> CREATE TABLE [Treatement] (
>     [treatementid] int IDENTITY(1,1) NOT NULL ,
>     [customerid] int NOT NULL ,
>     [paystatusid] int NOT NULL , .....
> and
> CREATE TABLE [Paystatus] (
>     [paystatusid] int IDENTITY(1,1) NOT NULL ,
>     [customerid] int NOT NULL ,...
>
> so that the Paystatus it refers to has the same customerid.
> It is also accepted that paystatusid of the Treatement table is 0 and hence
> dont refer to a Paystatus.
>
> Olav

There's very little information to go on here. As a minimum, please
include primary/unique key declarations with your DDL.

Why do paystatusid and customerid both appear in both tables? If you do
that you surely ought to have a foreign key in place.

--
David Portas
SQL Server MVP
--

13 Jan 2006 8:52 PM

Olav

Okay here it comes, i will drop the nonerelevalnt columns:
CREATE TABLE [Treatement] (
    [treatementid] int IDENTITY(1,1) NOT NULL ,
    [customerid] int NOT NULL ,
    [appbookid] int NOT NULL ,
    [paystatusid] int NOT NULL ,
    CONSTRAINT [PK_Treatement_treatementid] PRIMARY KEY NONCLUSTERED
([treatementid]),
    CONSTRAINT [FK_Treatement_Appbook] FOREIGN KEY ([appbookid]) REFERENCES
[dbo].[Appbook] ([appbookid]),
    CONSTRAINT [FK_Treatement_Customer] FOREIGN KEY ([customerid]) REFERENCES
[dbo].[Customer] ([customerid]))

CREATE TABLE [Paystatus] (
    [paystatusid] int IDENTITY(1,1) NOT NULL ,
    [customerid] int NOT NULL ,
    [appbookid] int NOT NULL ,
    CONSTRAINT [PK_Paystatus] PRIMARY KEY CLUSTERED ([paystatusid]),
    CONSTRAINT [FK_Paystatus_Customer] FOREIGN KEY ([customerid]) REFERENCES
[dbo].[Customer] ([customerid]))

Olav

Show quote

"David Portas" wrote:

> Olav wrote:
> > Hi,
> > we want to check the paystatusid in the table Treatement
> > CREATE TABLE [Treatement] (
> >     [treatementid] int IDENTITY(1,1) NOT NULL ,
> >     [customerid] int NOT NULL ,
> >     [paystatusid] int NOT NULL , .....
> > and
> > CREATE TABLE [Paystatus] (
> >     [paystatusid] int IDENTITY(1,1) NOT NULL ,
> >     [customerid] int NOT NULL ,...
> >
> > so that the Paystatus it refers to has the same customerid.
> > It is also accepted that paystatusid of the Treatement table is 0 and hence
> > dont refer to a Paystatus.
> >
> > Olav
>
> There's very little information to go on here. As a minimum, please
> include primary/unique key declarations with your DDL.
>
> Why do paystatusid and customerid both appear in both tables? If you do
> that you surely ought to have a foreign key in place.
>
> --
> David Portas
> SQL Server MVP
> --
>
>