I created the following table
create table publisher(name varchar(20), city varchar(20));
I want to put following constraint, 'name 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'))
);
