Unique column but by id - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

Database Design and Application Architecture

Unique column but by id

Author

Topic

sapator
Constraint Violating Yak Guru

462 Posts

Posted - 2010-07-18 : 11:13:28

Hi.
I can create a column on the table and specify it to be unique with an index.What i like to know is if i can make the column to be unique, when compared by another column in the same table.
P.E. if you have car names and you have a BMW named Model1 and a Opel named model1 then if you specify unique on carname you can only name one as model1.I don't want that.What i want is also somehow, modelid on this table to be checked so if carname AND modelid = carname AND modelid then and only then you get the exception that the model is unique.If the modelid <> modelid then you can have the same carname
Thanks.

slimt_slimt
Aged Yak Warrior

746 Posts

Posted - 2010-07-18 : 14:50:50

can you post some samples and also table definitions of your current table.

sapator
Constraint Violating Yak Guru

462 Posts

Posted - 2010-07-18 : 20:30:11

It's an example, so let's say i have.
PK id int
Carname nvarchar(50)
Carbrand nvarchar(50)

id 1 carname model1 carbrand BMW -allowed
id 2 carname model1 carbrand Opel -allowed
id 3 carname model1 carbrand BMW -not allowed, duplicate

sapator
Constraint Violating Yak Guru

462 Posts

Posted - 2010-07-18 : 20:45:27

I think i worked this out but i need a confirmation.
I added a non clustered index and included the 2 columns(carname,carbrand). It seems to work but can someone confirm it is correct?
Thanks.

sec
Starting Member

4 Posts

Posted - 2010-07-21 : 13:50:31

Seems like you have nailed it.

Let God be true and every man a liar

sapator
Constraint Violating Yak Guru

462 Posts

Posted - 2010-07-21 : 21:26:35

It seems so :)
Thanks.

Celko
Starting Member

23 Posts

Posted - 2010-07-29 : 14:45:06

I think what you want to do is overlap UNIQUE constraints:

CREATE TABLE Automobiles
(..
make VARCHAR (10) NOT NULL,
model VARCHAR (10) NOT NULL,
UNIQUE (make, model),
UNIQUE (model),
etc);

INSERT INTO Automobiles (make, model)
VALUES ('Honda', 'Civic'),
('Ford', 'Thunderbird'),
('Ford', 'Fairlane'),
(Lamborghini', 'Diablo'),
(Lamborghini', 'Civic'); -- opps! duplicate model

Author of
SQL for Smarties
Thinking in Sets
SQL Puzzles and Answers
Analytics and OLAP in SQL
SQL Programming Style
Standards in SQL
Data & Databases
Trees & Hierarchies in SQL

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources