preventing duplicate entry for a given foreign key in db table

Hi,i am using SQL server 2005 and have a table with 4 columns.Column1 is primary key,col2 is foreign key and col3 and col4 are regular data column.When the user enters the data i want to make sure that for a given foreign key(col2),entries in col3 are not duplicated.Is there a way,i can make sure this at db level,using some kind of constraints or something?Thanks a bunch..

hi nb123

try this

Alter Table YOURTABLENAME
Add Constraint NAMEOFCONSTRAINT UNIQUE (COLUMN NAME)

hope it helps

|||

Are you trying to achieve something like this ?

create table checktest(col1int ,col2int ,col3int ,col4int)GOalter table checktestadd constraint [unq_checktest_col123]unique ( col1 , col2 , col3 )insert checktest ( col1 , col2 , col3 , col4 )select 1 , 1 , 1 , 0insert checktest ( col1 , col2 , col3 , col4 )select 1 , 1 , 1 , 1insert checktest ( col1 , col2 , col3 , col4 )select 1 , 1 , 2 , 0insert checktest ( col1 , col2 , col3 , col4 )select 1 , 1 , 2 , 1

Here the second and the fourth insert query will fail as they violate the unique value constraint.

Hope this will help.

|||

Thanks guys,,yes this is what i am trying to achieve...how do i add the constraints in existing table?

col1 col2 col2 col4
1 12 val1 50
2 12 val1 60 >>this shud not be allowed
3 12 val2 30
4 13 val1 233 >>this is okay,as col2 is changed
5 13 val1 1111 >>this shud not be allowed

|||

I've also written the alter table query in my earlier post.

alter table <tablename>
add constraint [<name of the constraint>]unique ( col1 , col2 , col3 )

After you execute this query, the duplicate values for the col1, col2, col3 combination won't be allowed. 

|||

Thanks dhimant,i tried doing it using sql server management studio,i wasn't able to select 'alter table'..it was coming grayed..i thought of drop and recreate it with the above changes,i was getting the syntax error.I basically scripted the createTable from menu and tried adding 'alter table clause stuff manually to it and it was giving me syntex error.i am sure i wasn't doing something right...can u please help me with this...

USE [texashsfb]
GO
/****** Object: Table [dbo].[SEASON_SUPPORT_ROLE] Script Date: 10/24/2007 10:00:04 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[SEASON_SUPPORT_ROLE](
[SupportRoleID] [int] IDENTITY(1,1) NOT NULL,
[SeasonID] [int] NOT NULL,
[Role] [varchar](5) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Name] [varchar](30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
CONSTRAINT [PK_SEASON_SUPPORT_ROLE] PRIMARY KEY CLUSTERED
(
[SupportRoleID] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

/**** manually added ***/
ALTER TABLE [dbo].[SEASON_SUPPORT_ROLE] add CONSTRAINT [roleConstraint]
unique [SupportRoleID,SeasonID,Role]>>> Incorrect syntax near 'SupportRoleID,SeasonID,Role'.
GO
SET ANSI_PADDING OFF

|||

nb123:

unique [SupportRoleID,SeasonID,Role]>>> Incorrect syntax near 'SupportRoleID,SeasonID,Role'.

use ( ) instead of [ ]. Both of them have different meaning and applied for different purposes. So, your modified alter statement would be

ALTER TABLE [dbo].[SEASON_SUPPORT_ROLE]add CONSTRAINT [roleConstraint]unique ( SupportRoleID,SeasonID,Role )

Hope this will help.

|||

okay,it saved fine after i made the above change but its allowing the duplicate entries in the given column,i am not getting any unique key violation sort of error.Also when i script the table as create,to see the syntax,this is what i see..what iam doing wrong,i was expecing error while entering the duplicate value in role for a given foreign key..

USE [texashsfb]
GO
/****** Object: Table [dbo].[SEASON_SUPPORT_ROLE] Script Date: 10/25/2007 00:54:19 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[SEASON_SUPPORT_ROLE](
[SupportRoleID] [int] IDENTITY(1,1) NOT NULL,
[SeasonID] [int] NOT NULL,
[Role] [varchar](5) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Name] [varchar](30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
CONSTRAINT [PK_SEASON_SUPPORT_ROLE] PRIMARY KEY CLUSTERED
(
[SupportRoleID] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY],
CONSTRAINT [roleConstraint] UNIQUE NONCLUSTERED .>> this 'NOTCLUSTERED stuff got added after i saved
(
[SupportRoleID] ASC,
[SeasonID] ASC,
[Role] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF

|||

I got your problem mate. First you run the following queries, I've explained the solution below the queries:

alter table SEASON_SUPPORT_ROLEdrop constraint roleConstraint GOalter table SEASON_SUPPORT_ROLEadd constraint roleConstraintunique ( [SeasonID] , [Role])GO

Now, what is happening is that you've mentioned the SupportRoleId column in your unique key constraint. That column is an identity one and will be auto-incremented, that means the value of this column will always be different than the values already present in your table.

If you've provided more than one columns to be a part of your unique key constraint, then the combination of all the columns should not be repeated and if that happens then only the constraint is said to be violated.

Actually, you don't need the SupportRoleId column in your constraint as it is always going to be unique, hence will make the entire collection of all 3 columns to be unique and that won't violate the constraint and SQL Server will allow these kind of records.

What I've done in the query is to remove the constraint and then recreated it with excluding the SupportRoleId column.

Hope this will help.

|||

ah..that makes sense...i changed the query and now its working as expected..thanks Dhimant for beings so patient with me and helping me with this..

|||

Well, it was a good problem to solve for me too. You should mark as answer the post which solved your problem, that way the member gets credit for solving the problem and other visitors also come to know which post solved your problem.