Column constraint

Is it possible to limit a column's value to 0 or 1 and to allow zero or one
row to contain a value
of 1 for that column?
TIA, LarsNot 100% sure of your question but I think you want to look at CHECK
constraints and Unique Indexes in BooksOnLine. You can also enforce data
with triggers.
Andrew J. Kelly SQL MVP
"larzeb" <larzeb@.community.nospam> wrote in message
news:po5f811s7pvik05doqb0roide0g2uvla32@.
4ax.com...
> Is it possible to limit a column's value to 0 or 1 and to allow zero or
> one row to contain a value
> of 1 for that column?
> TIA, Lars|||> Is it possible to limit a column's value to 0 or 1
Yes, using either a Bit field or a Check constraint on a TinyInt, SmallInt,
or
Integer column.

> and to allow zero or one row to contain a value
> of 1 for that column?
This part is a bit trickier. Based purely on the information given, you coul
d
solve this in a couple of ways:
1. A trigger that raises an error when on an Insert or Update, a value alrea
dy
exists in the table with a value of 1.
2. Create a View that selects all rows that have a value of 1 and put a uniq
ue
index on that view.
There are other ways to solve this problem if you give us more information a
bout
the schema and problem you are trying to solve. For example, in building a
system for an insurance company, each insurance quote could be associated wi
th
multiple insurance brokers. However, each quote also had one and only one
"primary" broker. Thus, we solved this problem by putting a PrimaryBrokerId
column on the quote itself as well as had a table of "associated" brokers. T
his
made it easy to ensure that each quote had a primary broker and that there w
as
one and only one.
Granted, this sort of problem is much easier to solve in an object model tha
n it
is with database schema but with more infomration about the actual problem,
there might be a solution in the database design.
Thomas|||CREATE TABLE Foobar
( ..
silly_col INTEGER DEFAULT 0 NOT NULL
CONSTRAINT silly_col_range
CHECK (silly_col IN (0,1)),
CONSTRAINT silly_col_single flag
CHECK (1 - (SELECT SUM(silly_col) FROMbar)),
.);
But in SQL Server, you cannot write that second constraint like you can
in other products with better SQL-92 conformance. You would never use
bit data types because they are proprietary.
if you want to flag one row for some purpose, I would recomment that
you use a unique numeric column and select the minimum value:
CREATE TABLE Foobar
( ..
silly_col INTEGER DEFAULT 0 NOT NULL UNIQUE
CONSTRAINT silly_col_range
CHECK (silly_col >= 0),
.);
CREATE VIEW NewFoobar
AS
SELECT .. CASE WHEN silly_col
= (SELECT MIN(silly_col) FROM Foobar)
THEN 1 ELSE 0 END AS silly_flag,
FROM Foobar;|||If you just want to set the state (col =1) of exactly one row in a table
then maybe you could use a second table to do that:
CREATE TABLE YourTable (key_col INTEGER PRIMARY KEY, ... /* other cols */)
CREATE TABLE foo (key_col INTEGER NOT NULL REFERENCES YourTable (key_col), x
INTEGER DEFAULT 1 NOT NULL CHECK (x=1) UNIQUE /* Permit only one row */)
David Portas
SQL Server MVP
--