Constraint question

I need to put a constraint on a table but am unsure how to proceed. Consider
the following fields:
AcctNo - varchar(10), indexed, duplicates OK
Status - varchar(2)
The logical rules I need to enforce are such that if any records exist with
duplicate values in the AcctNo field, only one record can have an 'active'
status, where active status is defined as any status other than 'X'.
My question is whether a check constraint is the correct tool to enforce
this, and if so how would I create one that can enforce rules based on
multiple fields and multiple records?
Sample data would look something like this:
AcctNo Status
-- --
123456 RO
123456 X
987654 C
135790 RO
135790 X
777777 C
999999 RO
888888 RO
888888 Xcreate table t(i int identity(1,1),
AcctNo varchar(10),
Status varchar(2),
i_when_inactive as (case when status='X' then i else null end))
create unique index unique_active_acctno on t(AcctNo, i_when_inactive)
insert into t(acctno, status) values('ABC', 'C')
-- duplicate inactive rows
insert into t(acctno, status) values('ABC', 'X')
insert into t(acctno, status) values('ABC', 'X')
insert into t(acctno, status) values('ABC', 'X')
-- can't insert active duplicates
insert into t(acctno, status) values('ABC', 'C')
select * from t
drop table t|||Thanks AK. Works great.
"AK" wrote:

> create table t(i int identity(1,1),
> AcctNo varchar(10),
> Status varchar(2),
> i_when_inactive as (case when status='X' then i else null end))
> create unique index unique_active_acctno on t(AcctNo, i_when_inactive)
> insert into t(acctno, status) values('ABC', 'C')
> -- duplicate inactive rows
> insert into t(acctno, status) values('ABC', 'X')
> insert into t(acctno, status) values('ABC', 'X')
> insert into t(acctno, status) values('ABC', 'X')
> -- can't insert active duplicates
> insert into t(acctno, status) values('ABC', 'C')
> select * from t
> drop table t
>|||>> My question is whether a check constraint is the correct tool to enforce this, and
if so how would I create one that can enforce rules based on multiple fields [sic] and
multiple records [sic]? <<
Let's get back to the basics of an RDBMS. Rows are not records; fields
are not columns; tables are not files. Also, account numbers and most
codes are fixed length.
If you want to get the current row in a history, then write it like
this:
CREATE TABLE AccountHistory
(acct_nbr CHAR(10) NOT NULL,
acct_status CHAR(2) NOT NULL,
start_date DATETIME DEFAULT CURRENT_TIMESTAMP NOT NULL,
end_date DATETIME, -- null means current
CHECK (start_date < end_date),
PRIMARY KEY (acct_nbr, start_date),
etc.);
Now create a VIEW with the current row, something like this;
CREATE VIEW Accounts (acct_nbr, acct_status, start_date, ..)
AS
SELECT acct_nbr, acct_status, start_date, ..
FROM AccountHistory
WHERE end_date IS NULL;
To be sure that you have only one currently active account status, you
can use a trigger or some very horrible proprietary code. However, if
you had SQL-92, you could use:
CHECK(1 = ALL (SELECT COUNT(*)
FROM AccountHistory
WHERE end_date IS NULL
GROUP BY acct_nbr))|||Thanks for the DBMS lesson perfessor. The other guy's real world solution
works great.
"--CELKO--" wrote:

> Let's get back to the basics of an RDBMS. Rows are not records; fields
> are not columns; tables are not files. Also, account numbers and most
> codes are fixed length.
> If you want to get the current row in a history, then write it like
> this:
> CREATE TABLE AccountHistory
> (acct_nbr CHAR(10) NOT NULL,
> acct_status CHAR(2) NOT NULL,
> start_date DATETIME DEFAULT CURRENT_TIMESTAMP NOT NULL,
> end_date DATETIME, -- null means current
> CHECK (start_date < end_date),
> PRIMARY KEY (acct_nbr, start_date),
> etc.);
> Now create a VIEW with the current row, something like this;
> CREATE VIEW Accounts (acct_nbr, acct_status, start_date, ..)
> AS
> SELECT acct_nbr, acct_status, start_date, ..
> FROM AccountHistory
> WHERE end_date IS NULL;
> To be sure that you have only one currently active account status, you
> can use a trigger or some very horrible proprietary code. However, if
> you had SQL-92, you could use:
> CHECK(1 = ALL (SELECT COUNT(*)
> FROM AccountHistory
> WHERE end_date IS NULL
> GROUP BY acct_nbr))
>