Constraint / primary key question

[DanC]

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

 

[mitrab22]

What DBMS are you using?
You can use something like this: This works for Oracle
It should work on SQL Server too, but never tested it.

ALTER TABLE ChildTable
ADD ( CONSTRAINT R_123
FOREIGN KEY (Key_Name)
REFERENCES ParentTable) ;

 

[DanC]

I'm using SQL Server, but I don't think that your suggestion is going to do it for me. In reality the constraint I want is a combination of fields from the obj_add table and the add_tab table: obj_add.objNo + obj_add.pkNo + add_tab.typeNo + add_tab.addStat

I'm thinking that it's a faulty design in that I need the pkNo to be a foreign key in the add_tab, but that field doesn't exist in the add_tab as it stands.

thanks for your suggestion.

 

[SQLSister]

Your best bet if you cannot change the structure would be to use triggers to enforce your integrity rules. One of the main purposes of a trigger is to create contraints that are too complex for regu;lar constraints.