I'm creating a table that looks something like this.
CREATE TABLE packages
(
productCode char(2)
, name nvarchar(100)
, ...
)
I want to make sure the productCode is always one of two values: XJ or XD. How do I do that?
views:
129answers:
4
+10
A:
ALTER TABLE packages
ADD CONSTRAINT constraintname CHECK (productCode in ('XJ', 'XD'))
David B
2008-10-23 18:17:22
I would suggest using a lookup table for future expansion reasons. Inserting another row into a table is a lot easier than figuring out how to redo the constraint.
Jon Grant
2008-10-23 18:23:16
Yes, I'm invoking YAGNI in this case. :-)
Patrick McElhaney
2008-10-23 18:33:26
Does this work in Oracle, MySQL, etc? I'm using MS SQL Server 2000, but if the solution is pretty standard, it would make sense to note that and remove the sqlserver tag.
Patrick McElhaney
2008-10-23 18:34:58
Oracle, yes. Mysql - I think not. Mysql seems to want to place a constraint on the column instead of checking the validity of a row.
David B
2008-10-23 18:49:00
MySQL does not support CHECK constraints. They can do something similar with their proprietary ENUM data type.
Bill Karwin
2008-10-23 19:32:10
+8
A:
Either make it a foreign key to a lookup table, or add a check constraint to enforce it.
Jon Grant
2008-10-23 18:18:32
+3
A:
CREATE TABLE packages
(
productCode char(2)
, name nvarchar(100)
, ...
,CONSTRAINT productCode CHECK (productCode in ('XJ','XD') )
)
TrickyNixon
2008-10-23 18:20:26
Shouldn't the first "productCode" be the name of the constraint instead of the name of the column?
Patrick McElhaney
2008-10-23 18:35:51
It is -- of course you can name it anything but if I place a check constraint on a single column, I usually just use the column name.
TrickyNixon
2008-10-23 18:46:25
Cool. I had always assumed that a constraint can't have the same name as a column in the same table.
Patrick McElhaney
2008-10-23 18:48:53
+1
A:
In this case it sounds like the valueset for ProductCode is pretty limited and that you don't expects it to grow in a foreseeable future, so I tend to agree with checkconstraint answers. However in most cases I would implement the foreign key solution as suggested by Mr. Grant as my customers has a nasty habit of changing their mind ( and the requirements as well ) about once a day. In that situation it is my expirence that the FK version is easier to maintain.
Kasper
2008-10-23 19:27:03