Constraint Problem

I created the following table

create table publisher(name varchar(20), city varchar(20));

I want to put following constraint, 'nam开发者_运维知识库e is having unique with city taken only from 'mumbai', 'pune', 'nasik', 'panji''. I know about unique constraints with name. I searched many about constraint with city, but i don't get it till now. Will you please help me about constraint. I used 'BETWEEN' constraint, but it failed. What should I do?

You can use IN instead of BETWEEN

CREATE TABLE publisher
  (
     name   VARCHAR( 20 ) UNIQUE
     , city VARCHAR( 20 ) CHECK ( city IN ('a', 'b') )
  );

INSERT INTO publisher
     VALUES ('hi','a'); -- Succeeds

INSERT INTO publisher
     VALUES ('hi','b'); -- Fails, already have 'hi'

INSERT INTO publisher
     VALUES ('hj','c'); -- Fails, 'c' is not a city

I think you're asking for names to be unique within their city, and for the cities to come from a fixed list of possibilities?

If so, the following would work:

create table publisher(
   name varchar(20),
   city varchar(20),
   constraint UQ_NamesWithinCities UNIQUE (name,city),
   constraint CK_CityNames CHECK (city in ('mumbai', 'pune', 'nasik', 'panji'))
);