Hopefully I can explain this clearly. I've come in on the tail-end of a development cycle of a database and I'm trying to figure out how to enforce integrity across some of the tables. The tables I'm dealing with right now are:
CREATE TABLE [ppl_tab] (
[pplNo] [int] NOT NULL ,
[objNo] [int] NULL ,
[typeNo] [int] NULL ,
[pplIDNo] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[pplSSN] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[pplPre] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[pplFirst] [varchar] (40) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[pplMid] [varchar] (40) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[pplLast] [varchar] (40) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[pplSuf] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[pplNick] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[pplTitle] [varchar] (75) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[pplGen] [varchar] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
CONSTRAINT [PK__legislator__0425A276] PRIMARY KEY CLUSTERED
(
[pplNo]
) ON [PRIMARY]
) ON [PRIMARY]
GO
----------------------------------------
CREATE TABLE [obj_add] (
[objNo] [int] NOT NULL ,
[pkNo] [int] NOT NULL ,
[addNo] [int] NOT NULL ,
[oaDefault] [bit] NOT NULL CONSTRAINT [DF_obj_add_oaDefault] DEFAULT (0),
CONSTRAINT [PK_obj_add] PRIMARY KEY CLUSTERED
(
[objNo],
[pkNo],
[addNo]
) ON [PRIMARY]
) ON [PRIMARY]
GO
---------------------------------------
CREATE TABLE [add_tab] (
[addNo] [int] NOT NULL ,
[typeNo] [int] NOT NULL ,
[addDesc] [varchar] (200) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[add1] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[add2] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[addCity] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[addState] [varchar] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[addZip] [varchar] (11) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[addStat] [int] NULL CONSTRAINT [DF_add_tab_addStat] DEFAULT (1),
CONSTRAINT [PK__address_tab__023D5A04] PRIMARY KEY CLUSTERED
(
[addNo]
) ON [PRIMARY]
) ON [PRIMARY]
GO
------------------------------------
the linking is as follows:
select p.*, a.*
from ppl_tab p
inner join obj_add oa
on p.pplNo = oa.pkNo and oa.objNo = 700
inner join add_tab a
on oa.addNo = a.addNo and a.typeNo = 600 --or 601 or 602 or 603 (types of addresses-home, work, etc)
where a.addStat = 1
I'm trying to put in some kind of check constraint that only allows one addresses record per type with an addStat of 1 per obj_add.pkNo.
Meaning in essence I want to only have one active address per individual per type (home work etc)
Any help on how to go about this would be appreciated. I didn't design the structure, I just have to work with it, so if the design is flawed, please feel free to say so, it won't hurt my feelings.
thanks
-dan
CREATE TABLE [ppl_tab] (
[pplNo] [int] NOT NULL ,
[objNo] [int] NULL ,
[typeNo] [int] NULL ,
[pplIDNo] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[pplSSN] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[pplPre] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[pplFirst] [varchar] (40) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[pplMid] [varchar] (40) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[pplLast] [varchar] (40) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[pplSuf] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[pplNick] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[pplTitle] [varchar] (75) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[pplGen] [varchar] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
CONSTRAINT [PK__legislator__0425A276] PRIMARY KEY CLUSTERED
(
[pplNo]
) ON [PRIMARY]
) ON [PRIMARY]
GO
----------------------------------------
CREATE TABLE [obj_add] (
[objNo] [int] NOT NULL ,
[pkNo] [int] NOT NULL ,
[addNo] [int] NOT NULL ,
[oaDefault] [bit] NOT NULL CONSTRAINT [DF_obj_add_oaDefault] DEFAULT (0),
CONSTRAINT [PK_obj_add] PRIMARY KEY CLUSTERED
(
[objNo],
[pkNo],
[addNo]
) ON [PRIMARY]
) ON [PRIMARY]
GO
---------------------------------------
CREATE TABLE [add_tab] (
[addNo] [int] NOT NULL ,
[typeNo] [int] NOT NULL ,
[addDesc] [varchar] (200) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[add1] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[add2] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[addCity] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[addState] [varchar] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[addZip] [varchar] (11) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[addStat] [int] NULL CONSTRAINT [DF_add_tab_addStat] DEFAULT (1),
CONSTRAINT [PK__address_tab__023D5A04] PRIMARY KEY CLUSTERED
(
[addNo]
) ON [PRIMARY]
) ON [PRIMARY]
GO
------------------------------------
the linking is as follows:
select p.*, a.*
from ppl_tab p
inner join obj_add oa
on p.pplNo = oa.pkNo and oa.objNo = 700
inner join add_tab a
on oa.addNo = a.addNo and a.typeNo = 600 --or 601 or 602 or 603 (types of addresses-home, work, etc)
where a.addStat = 1
I'm trying to put in some kind of check constraint that only allows one addresses record per type with an addStat of 1 per obj_add.pkNo.
Meaning in essence I want to only have one active address per individual per type (home work etc)
Any help on how to go about this would be appreciated. I didn't design the structure, I just have to work with it, so if the design is flawed, please feel free to say so, it won't hurt my feelings.
thanks
-dan