Constraint

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"Olav" <Olav@.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|||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
"Rick Sawtell" wrote:

> "Olav" <Olav@.discussions.microsoft.com> wrote in message
> news:B72A4068-27AB-4C99-83CC-982ABB0DC79A@.microsoft.com...
> 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
>
>|||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 henc
e
> 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
--|||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
"David Portas" wrote:

> Olav wrote:
> 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
> --
>