CHECK ()?

What is best? What is right?
--This way:
---
CREATE TABLE Districts
(
District char(2)
NOT NULL,
Constraint PK_District
Primary Key(District)
)
-- Items Padro --
INSERT INTO Districts VALUES('AC')
INSERT INTO Districts VALUES('AL')
INSERT INTO Districts VALUES('AM')
INSERT INTO Districts VALUES('BA')
INSERT INTO Districts VALUES('CE')
INSERT INTO Districts VALUES('DF')
INSERT INTO Districts VALUES('ES')
INSERT INTO Districts VALUES('GO')
INSERT INTO Districts VALUES('MA')
INSERT INTO Districts VALUES('MG')
INSERT INTO Districts VALUES('MS')
INSERT INTO Districts VALUES('MT')
INSERT INTO Districts VALUES('PA')
INSERT INTO Districts VALUES('PB')
INSERT INTO Districts VALUES('PE')
INSERT INTO Districts VALUES('PI')
INSERT INTO Districts VALUES('PR')
INSERT INTO Districts VALUES('RJ')
INSERT INTO Districts VALUES('RN')
INSERT INTO Districts VALUES('RO')
INSERT INTO Districts VALUES('RR')
INSERT INTO Districts VALUES('RS')
INSERT INTO Districts VALUES('SC')
INSERT INTO Districts VALUES('SE')
INSERT INTO Districts VALUES('SP')
INSERT INTO Districts VALUES('TO')
CREATE TABLE foo
(
...
District char(2),
...
Constraint FK_District
Foreign Key(District)
References Districts(District),
)
--Or this way:
---
CREATE TABLE foo
(
...
District char(2) CONSTRAINT chk_District CHECK (
UF = 'AC' OR
UF = 'AL' OR
UF = 'AM' OR
UF = 'BA' OR
UF = 'CE' OR
UF = 'DF' OR
UF = 'ES' OR
UF = 'GO' OR
UF = 'MA' OR
UF = 'MG' OR
UF = 'MS' OR
UF = 'MT' OR
UF = 'PA' OR
UF = 'PB' OR
UF = 'PE' OR
UF = 'PI' OR
UF = 'PR' OR
UF = 'RJ' OR
UF = 'RN' OR
UF = 'RO' OR
UF = 'RR' OR
UF = 'RS' OR
UF = 'SC' OR
UF = 'SE' OR
UF = 'SP' OR
UF = 'TO')
...
)
ThanksI would go with a foreign key solution as it is more maintainable and a
naatual way to implement referential integrity.
--
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"ReTF" <re.tf@.newsgroup.nospam> wrote in message
news:uZvY0ugkFHA.2608@.TK2MSFTNGP14.phx.gbl...
> What is best? What is right?
> --This way:
> ---
>
> CREATE TABLE Districts
> (
> District char(2)
> NOT NULL,
> Constraint PK_District
> Primary Key(District)
> )
> -- Items Padro --
> INSERT INTO Districts VALUES('AC')
> INSERT INTO Districts VALUES('AL')
> INSERT INTO Districts VALUES('AM')
> INSERT INTO Districts VALUES('BA')
> INSERT INTO Districts VALUES('CE')
> INSERT INTO Districts VALUES('DF')
> INSERT INTO Districts VALUES('ES')
> INSERT INTO Districts VALUES('GO')
> INSERT INTO Districts VALUES('MA')
> INSERT INTO Districts VALUES('MG')
> INSERT INTO Districts VALUES('MS')
> INSERT INTO Districts VALUES('MT')
> INSERT INTO Districts VALUES('PA')
> INSERT INTO Districts VALUES('PB')
> INSERT INTO Districts VALUES('PE')
> INSERT INTO Districts VALUES('PI')
> INSERT INTO Districts VALUES('PR')
> INSERT INTO Districts VALUES('RJ')
> INSERT INTO Districts VALUES('RN')
> INSERT INTO Districts VALUES('RO')
> INSERT INTO Districts VALUES('RR')
> INSERT INTO Districts VALUES('RS')
> INSERT INTO Districts VALUES('SC')
> INSERT INTO Districts VALUES('SE')
> INSERT INTO Districts VALUES('SP')
> INSERT INTO Districts VALUES('TO')
> CREATE TABLE foo
> (
> ...
> District char(2),
> ...
> Constraint FK_District
> Foreign Key(District)
> References Districts(District),
> )
>
> --Or this way:
> ---
> CREATE TABLE foo
> (
> ...
> District char(2) CONSTRAINT chk_District CHECK (
> UF = 'AC' OR
> UF = 'AL' OR
> UF = 'AM' OR
> UF = 'BA' OR
> UF = 'CE' OR
> UF = 'DF' OR
> UF = 'ES' OR
> UF = 'GO' OR
> UF = 'MA' OR
> UF = 'MG' OR
> UF = 'MS' OR
> UF = 'MT' OR
> UF = 'PA' OR
> UF = 'PB' OR
> UF = 'PE' OR
> UF = 'PI' OR
> UF = 'PR' OR
> UF = 'RJ' OR
> UF = 'RN' OR
> UF = 'RO' OR
> UF = 'RR' OR
> UF = 'RS' OR
> UF = 'SC' OR
> UF = 'SE' OR
> UF = 'SP' OR
> UF = 'TO')
> ...
> )
>
> Thanks
>|||I'd rather store the data in a table. Who wants to go modify a constraint
whenever a district is added or removed?

> What is best? What is right?|||Whatever works best for you. If you think the Districts will change
regularly or if they have to be maintained directly by users then a table is
be the obvious choice. If they'll change less often than you like to make
releases of the database then you can use a CHECK constraint.
David Portas
SQL Server MVP
--