I have aquestions from you. Does SQL server have any features so we can limit a field to fill with just specific values? For example assume that you have a field named "Name" then we want SQL just let us to fill this field with the following values: "Bella", "Jack", "Rose". is there any featues to do it? please guide me. thanks
is it the right script for my probem?
elton
2010-10-11 07:14:05
ADD CONSTRAINT checkValues CHECK Names in ('Rose', 'Bella', 'Jack');
elton
2010-10-11 07:14:35
@elton, it should works, if it does not works you can use: ADD CONSTRAINT checkValues CHECK (Name = 'Rose' or name ='Bella' or Name = 'Jack'));
Michael Pakhantsov
2010-10-11 07:18:45
yes, thanks, thats right
elton
2010-10-11 07:20:49
+6
A:
You can use a CHECK constraint:
ALTER TABLE dbo.YOUR_TABLE
ADD CONSTRAINT chk_name CHECK (name IN ('Rose', 'Bella', 'Jack'));
...but you might want to use a separate table & foreign key if you need to add identical CHECK constraints to numerous tables:
NAMES
- name_id (primary key)
- name
Foreign Key Constraint:
ALTER TABLE dbo.YOUR_TABLE
ADD CONSTRAINT fk_names FOREIGN KEY (name)
REFERENCES dbo.NAMES (name_id) ;
OMG Ponies
2010-10-11 08:03:43
Good point about using a foreign key but whether it is a good idea to introduce `nameid` into the schema is another matter... or possibly just a typo ;)
onedaywhen
2010-10-11 09:03:54