CODEWITHSUNDEEP
sql-serverconstraints
Pooja
Pooja

Reputation: 33

Constraint Problem

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?

Upvotes: 1

Views: 123

Answers (2)

Damien_The_Unbeliever
Damien_The_Unbeliever

Reputation: 239636

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'))
);

Upvotes: 0

ta.speot.is
ta.speot.is

Reputation: 27214

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

Upvotes: 1

Related Questions