CODEWITHSUNDEEP
sqlsql-serversql-server-2005
Shetty's
Shetty's

Reputation: 617

SQL Server 2005 UNIQUE Constraint on Multiple Column while creating table

I am working with a SQL Server 2005 database and I am facing a problem.

I am creating a table like this:

CREATE TABLE CONT_UNIQUE
(
   NUM INT, 
   BRANCH VARCHAR(10),
   PIN INT,
   CONSTRAINT CON UNIQUE(NUM,BRANCH,PIN)
)

means I am adding a unique constraint to all columns present in my table. But while inserting values in table, it is considering only NUM to be as UNIQUE, but allowing duplicate values for branch and PIN.

Below are my two insert queries.

INSERT INTO CONT_UNIQUE VALUES(1, 'MP', 123)    -> Working fine

INSERT INTO CONT_UNIQUE VALUES(2, 'MP', 123)    -> Should throw error since MP, and 123 are present.

Note: 

CREATE TABLE CONT_UNIQUE
(
    NUM INT UNIQUE , 
    BRANCH VARCHAR(10), UNIQUE,
    PIN INT UNIQUE
)

this works perfectly as expected.

Kindly let me know what is the problem with my queries.

Upvotes: 0

Views: 743

Answers (4)

Shetty's
Shetty's

Reputation: 617

From all of your replies i learnt that, 1)Unique key works with Unique combination rather than focusing on individual uniqueness... Ex: unique(Column1, Column2) means column1 and column2 combination should not repeat, but individual values can repeat.

2)If we want unique value on each column then we need to mention the "unique" to each column while creating table. Ex:Num int unique, Branch varchar(10) unique...etc so that each column will have unique values.

Previously i thought Unique(Col1, col2) is same as "col1 int unique, col2 int unique". So i asked the question.

ONCE AGAIN THANKS TO ALL OF YOU FOR YOUR SUPPORT IN SOLVING MY QUERY.. :)

Thanks

Mahesh

Upvotes: 0

paul
paul

Reputation: 22001

You have created a single constraint that ensures no two rows have the same values in all 3 columns.

You want three separate constraints, one on NUM, one on BRANCH and one on PIN.

CREATE TABLE CONT_UNIQUE
(
    NUM INT, 
    BRANCH VARCHAR(10),
    PIN INT,
    CONSTRAINT CON UNIQUE(NUM), 
    CONSTRAINT CON2 UNIQUE(BRANCH), 
    CONSTRAINT CON3 UNIQUE(PIN)
)

Upvotes: 3

gvee
gvee

Reputation: 17161

That won't throw an error because the unique constraint is on all 3 columns.

I think you want this as well / instead:

... CONSTRAINT only_two_columns UNIQUE (branch, pin) ...

Upvotes: 0

Sonam
Sonam

Reputation: 3466

You have created unique constraint on the combination of 3 columns but not 2 columns, what I mean is you can not insert 1,'MP',123 value again into the table, but you can insert 1,'MP',12 or 1,'MP',13 into the table.

Upvotes: 0

Related Questions