Primary/Foreign/Identity keys & Encryption

Hi all!

I'm just getting my feet wet with how encryption works in SQL 2005. With regards to the encryption of primary / foreign keys, I'm not entirely clear on the best approach. Below are three examples of typical table structures I currently have:

== Customers table ==
CustomerID (PK, int, Identity)
CustomerName (varchar)

== Orders table ==
OrderID (PK, int, Identity)
CustomerID (int, foreign key)
CreditCardNumber (varchar)

== OrderDetails table (1 to Many) ==
OrderID (PK/FK, int)
ItemNumber (PK, int)
ItemDescription (varchar)

The Customers and Orders tables use identity values as their primary keys. From what I can tell, CustomerID in the Customers table cannot be encrypted and OrderID in the Orders table cannot be encrypted because they are identity values. In these cases, would it be safer (in terms of security) to create a separate, meaningless identity key column in the Customers table and then remove the identity attribute from CustomerID so I can encrypt CustomerID?

Similarily in the OrderDetails table, OrderID and ItemNumber form a composite key. These values are important in that I don't want them to be tampered with. Am I better off creating a separate identity key column which becomes the table's primary key ... then encrypt both the OrderID and ItemNumber columns in this table?

Any ideas are appreciated.

Thank you,
Ben

Hey Ben,

You have the principle behind encryption correct.

The tricky thing is when you use encryption on keys, a lot of the value of having these keys goes away. For eample, because encryption is non-deterministic, you won't be able to use OrderID as a FK in the OrderDetails table (unless if you encrypt once and then insert into both tables, but this leaks information. Then again, this might be acceptable in your application). You can still use PK, but they will behave differently. For example, because encryption is non-deterministic, just having the primary keys no longer guarantees that the columns will be unique. If you try to insert Encrypted("id1") and then Encrypted("id1") again, you actually end up with two different cipher values so the the table will allow both inserts.

Security basically destroys information (well encrypted data is indistinguishable from random data) while the point of using keys is to preserve information for reference. Consider, for example, the difficulties you will encounter attempting to do searches or joins on CustomerID and OrderID if they are encrypted (Laurentiu has a good blog entry on this here: http://blogs.msdn.com/lcris/archive/2005/12/22/506931.aspx).

You can also check Laurentiu's blog for an example of creating an application using encryption: http://blogs.msdn.com/lcris/archive/2005/12/16/504692.aspx this doesn't completely solve your problems, but it might be useful in seeing one way to apply encryption.

Please let us know if you would like more information or have further questions.

Sung

sql

Primary, Indexes and Foreign Key - Best Place for them

Hello
I have a database with two data files PRIMARY and INDEXES.
To beef up performance I would like to move as much as I
can out of PRIMARY into Index so I would like to know the
best place to keep my Primary, Foreign and Indexes.
For instance, is it better to keep my Primary Keys in the
PRIMARY filegroup or move it to the INDEXES filegroup ?
Thanks
JWhat makes you think that you would get much if any benefit out of doing
this?
Keeping data in different filegroups doesn't necessarily do anything for
performance unless those filegroups are on differnet spindles. (ie physical
disks). Even then... most databases rarely have a need for different
filegroups. Instead, it's normally just as good for performance to simply
create multiple files within a single filegroup. Generally, I don't use
seperate filegroups unless I want a different backup strategy for difference
data sets.
--
Brian Moran
Principal Mentor
Solid Quality Learning
SQL Server MVP
http://www.solidqualitylearning.com
"Julie" <anonymous@.discussions.microsoft.com> wrote in message
news:0a0e01c3a206$20fe0e10$a501280a@.phx.gbl...
> Hello
> I have a database with two data files PRIMARY and INDEXES.
> To beef up performance I would like to move as much as I
> can out of PRIMARY into Index so I would like to know the
> best place to keep my Primary, Foreign and Indexes.
> For instance, is it better to keep my Primary Keys in the
> PRIMARY filegroup or move it to the INDEXES filegroup ?
> Thanks
> J|||Thankyou for your post.
As I understand it, it is due to the read write heads of
SQL server only one head is allowed at one time per data
file.
Having more than one increases performance, though having
too many slows it.
According to the MCP course it is recommended that you
take your indexes out, and put them in a separate data
file, as then you will be able to ge immediatly from one
file to another.
Thanks
J
>--Original Message--
>What makes you think that you would get much if any
benefit out of doing
>this?
>Keeping data in different filegroups doesn't necessarily
do anything for
>performance unless those filegroups are on differnet
spindles. (ie physical
>disks). Even then... most databases rarely have a need
for different
>filegroups. Instead, it's normally just as good for
performance to simply
>create multiple files within a single filegroup.
Generally, I don't use
>seperate filegroups unless I want a different backup
strategy for difference
>data sets.
>--
>Brian Moran
>Principal Mentor
>Solid Quality Learning
>SQL Server MVP
>http://www.solidqualitylearning.com
>
>"Julie" <anonymous@.discussions.microsoft.com> wrote in
message
>news:0a0e01c3a206$20fe0e10$a501280a@.phx.gbl...
>> Hello
>> I have a database with two data files PRIMARY and
INDEXES.
>> To beef up performance I would like to move as much as I
>> can out of PRIMARY into Index so I would like to know
the
>> best place to keep my Primary, Foreign and Indexes.
>> For instance, is it better to keep my Primary Keys in
the
>> PRIMARY filegroup or move it to the INDEXES filegroup ?
>> Thanks
>> J
>
>.
>|||Julie
Seems to be some confusion here. You say data files, but
it sounds like you are talking about file groups. I agree
with Brian, in that do not create multiple file groups
unless you know you need them.
If you are using multiple physical disks, SQL Server
usually does a good job of striping the tables across the
disks. If you do have one of more large tables that are
very active it can be a benefit to put the non-clustered
indexes in a seperate filegroup. Providing that filegroup
is on different physical drives. I would advise against
doing it as a matter of course, only do it if you can
prove it is an issue.
Hope this helps
John|||Thankyou both for your responses, it looks as if I have my
wires crossed somewhere.
J
>--Original Message--
>Julie
>Seems to be some confusion here. You say data files, but
>it sounds like you are talking about file groups. I agree
>with Brian, in that do not create multiple file groups
>unless you know you need them.
>If you are using multiple physical disks, SQL Server
>usually does a good job of striping the tables across the
>disks. If you do have one of more large tables that are
>very active it can be a benefit to put the non-clustered
>indexes in a seperate filegroup. Providing that filegroup
>is on different physical drives. I would advise against
>doing it as a matter of course, only do it if you can
>prove it is an issue.
>Hope this helps
>John
>.
>

primary key/foreign key relationship

I'm using MS SQL Server Studio Express 9.0. When selecting SQL Server Compact Edition, I'm able to create tables and primary keys with no problem. But how do I create a relationship with another table (foreign key)?

Bob K.

There is not designer support for doing this with SQL CE, so you must use:

Code Snippet

ALTER TABLE Orders ADD FK_Customer_Order FOREIGN KEY (CustomerId) REFERENCES Customers(CustomerId)

Designer support for this is promised for the next version of SQL CE (3.5) - in the "Orcas" timeframe.

|||thanks Eric.
|||Eric,

is there a way to enter the above sql statement from with Studio Express?
|||Connect to the SDF file, and select File, New, New query, type the text in the query editor and press F5 to execute...|||once again, thank you.
|||uhhh... and the beat goes on...

i've entered the following sql statement:

ALTER TABLE Camera ADD FK_Format_Camera FOREIGN KEY (FormatID) REFERENCES Format(FormatID)

and received the following error:
Major Error 0x80040E14, Minor Error 25501
> ALTER TABLE Camera ADD FK_Format_Camera FOREIGN KEY (FormatID) REFERENCES Format(FormatID)
There was an error parsing the query. [ Token line number = 1,Token line offset = 41,Token in error = FOREIGN ]

i've attached a link that shows the tables and columns i'm attempting to update. i just can't see what i'm doing wrong. could someone help?

bob k.

|||the link:

http://www.rkamarowski.com/errors/error.jpg

|||

Sorry, the correct syntax is (using the sample Northwind.sdf):

Code Snippet

ALTER TABLE Orders ADD CONSTRAINT Reference FOREIGN KEY ([Customer ID]) REFERENCES Customers ([Customer ID])

|||perfect! thanks eric.
|||

Good Afternoon Erik,

Can you help me out with a project of my?! I programming for the first time in the .net cf and i create my database all manualy thought SqlCeEngine..

But i want to know if there is a better approach to do this by the "next, next theory" (visuali) . if i create the database using a GUI of sql serv mobile edition, how can i choose where this database will be created and how can i indicate to the datasource in the connection string of an sqlConnection where is my database?! can you help me? i have a checkpoint day 17 to present my work and i would like to finish the hole project will that date to get some time to another project that is coming up, so its kind of urgent subject.

Thank you anyway!

Primary Key Foreign Key Tables

Hi All,
i do have two table a Master and a Detail which has a primary
key on the Master Table and Foreign key on the Detail Table
How do i insist that when ever there is a entry in the Master table
there should atleast exist one record in the Detail Table. Any sort of
condition can be made
Thanks in advance
thomsonHave a look at BOL "Constraints" and "Foreign key constraints"
HTH
Gerard|||Circular references are complex in SQL. An easier way is to write a trigger
to check the existance of rows in the referencing table.
Anith|||This is typically handled best in a client application (e.g., an n-tier
object) as a business rule.
see the following link for some more thoughts on this:
http://expertanswercenter.techtarge...i976558,00.html
thomson wrote:

>Hi All,
> i do have two table a Master and a Detail which has a primary
>key on the Master Table and Foreign key on the Detail Table
>
>How do i insist that when ever there is a entry in the Master table
>there should atleast exist one record in the Detail Table. Any sort of
>condition can be made
>
>Thanks in advance
>thomson
>
>

Primary key error Uppercase/lowercase

Hello,
I've got a problem with 2 tables, the first one has not constraint and is used as a data source, the second one as primary and foreign key constraints and is the destination.

In the first one the columns are :
TABLE PROMOTION :
PROMOTION_ID; PROMOTION_DESC;
16a;-20% discount;
16A;-30% discount;
AbC;no discount;
aBc; -90% discount;

The second table, TD_PROMOTION, has the same structure, but the column PROMOTION_ID column has a primary key constraint.
When i want to insert data from PROMOTION to TD_PROMOTION, i've got a primary_key constraint error, because it seems that the database makes no difference between the codes 16a and 16A and between AbC and aBc.
Is there a way to fix this problem ?

Thank you in advance,
Eric.

You can specify if SQL Server should be case sensitive or not using collation. In this instance the column must have a case sensitive collation in order for you to be able to specify any type of unique constraint on it. For example, the first example will fail whereas the second will work, notice the CI and CS for case insensitive and sensitive.

CREATE TABLE test1 (
col1 varchar(20) COLLATE Latin1_General_CI_AS PRIMARY KEY
)

INSERT INTO test1 VALUES ('ASD')
INSERT INTO test1 VALUES ('asd')

CREATE TABLE test2 (
col1 varchar(20) COLLATE Latin1_General_CS_AS PRIMARY KEY
)

INSERT INTO test2 VALUES ('ASD')
INSERT INTO test2 VALUES ('asd')

DROP TABLE test1
DROP TABLE test2

Collation can be set at the column or database level. If set at database level then all character columns without a collation specified adopt the database collation.

There are many collations available to you, search Books Online for a list of these.

Nick
DBA http://www.comoni.co.uk

|||Thank You, i exported my databases scripts, changed the collation CI to CS, and then recreated the databases, now it works fine !
Thank you !
sql

Primary key

Is it possible to define a primary key in a view? My View is a 5 tables.
Table 1 holds a primary key that is used as foreign key in the other 4.
It is a view i have made on an SQL 2000 server
best regards
Trond>> Is it possible to define a primary key in a view? <<
No. A VIEW is defined as a virtual table with a SELECT statement. You
can make the rows in the VIEW unique by proper coding. You can modify
it with an INSTEAD OF trigger that changes the underlying base tables.

PRIMARY KEY

As modelling of data when a table possesses two foreign keys, these two work
as primary key avoiding duplications...
As I make those FOREIGN KEY in SQL for them to work like PRIMARY KEY...
Here is the Example
CREATE TABLE <TABLE_NAME>
(
KET_1 REFERENCES <>,
KEY_2 REFERENCES <>,
PRIMARY KEY (KEY_1,KEY_2)
)
thanks and regards
Chandra
"Frank Dulk" wrote:

> As modelling of data when a table possesses two foreign keys, these two work
> as primary key avoiding duplications...
> As I make those FOREIGN KEY in SQL for them to work like PRIMARY KEY...
>
>

PRIMARY KEY

As modelling of data when a table possesses two foreign keys, these two work
as primary key avoiding duplications...
As I make those FOREIGN KEY in SQL for them to work like PRIMARY KEY...Here is the Example
CREATE TABLE <TABLE_NAME>
(
KET_1 REFERENCES <>,
KEY_2 REFERENCES <>,
PRIMARY KEY (KEY_1,KEY_2)
)
thanks and regards
Chandra
"Frank Dulk" wrote:
> As modelling of data when a table possesses two foreign keys, these two work
> as primary key avoiding duplications...
> As I make those FOREIGN KEY in SQL for them to work like PRIMARY KEY...
>
>

PRIMARY KEY

As modelling of data when a table possesses two foreign keys, these two work
as primary key avoiding duplications...
As I make those FOREIGN KEY in SQL for them to work like PRIMARY KEY...Here is the Example
CREATE TABLE <TABLE_NAME>
(
KET_1 REFERENCES <>,
KEY_2 REFERENCES <>,
PRIMARY KEY (KEY_1,KEY_2)
)
thanks and regards
Chandra
"Frank Dulk" wrote:

> As modelling of data when a table possesses two foreign keys, these two wo
rk
> as primary key avoiding duplications...
> As I make those FOREIGN KEY in SQL for them to work like PRIMARY KEY...
>
>

Primary and Foreign Keys

Here is a off the wall question. Whare are either the pros or cons of having
the same column in a table with both a primary key and foreign key?
I know that I can do this, because I tested it. But, I don't know if there
are good or bad affects from doing so.
thanks
No problem at all. In the Northwind database, check out the [Order Details]
table. Both ProductID and OrderID are foreign keys and both form the
primary key.
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com
..
"BillyDees" <BillyDees@.discussions.microsoft.com> wrote in message
news:2D2E6340-71BF-4FF0-B496-50D514C9D6B5@.microsoft.com...
Here is a off the wall question. Whare are either the pros or cons of having
the same column in a table with both a primary key and foreign key?
I know that I can do this, because I tested it. But, I don't know if there
are good or bad affects from doing so.
thanks
|||Thank you Tom for the feedback.
Joe
"Tom Moreau" wrote:

> No problem at all. In the Northwind database, check out the [Order Details]
> table. Both ProductID and OrderID are foreign keys and both form the
> primary key.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinnaclepublishing.com
> ..
> "BillyDees" <BillyDees@.discussions.microsoft.com> wrote in message
> news:2D2E6340-71BF-4FF0-B496-50D514C9D6B5@.microsoft.com...
> Here is a off the wall question. Whare are either the pros or cons of having
> the same column in a table with both a primary key and foreign key?
> I know that I can do this, because I tested it. But, I don't know if there
> are good or bad affects from doing so.
> thanks
>

Primary and Foreign Keys

Here is a off the wall question. Whare are either the pros or cons of having
the same column in a table with both a primary key and foreign key?
I know that I can do this, because I tested it. But, I don't know if there
are good or bad affects from doing so.
thanksNo problem at all. In the Northwind database, check out the [Order Deta
ils]
table. Both ProductID and OrderID are foreign keys and both form the
primary key.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com
.
"BillyDees" <BillyDees@.discussions.microsoft.com> wrote in message
news:2D2E6340-71BF-4FF0-B496-50D514C9D6B5@.microsoft.com...
Here is a off the wall question. Whare are either the pros or cons of having
the same column in a table with both a primary key and foreign key?
I know that I can do this, because I tested it. But, I don't know if there
are good or bad affects from doing so.
thanks|||Thank you Tom for the feedback.
Joe
"Tom Moreau" wrote:

> No problem at all. In the Northwind database, check out the [Order De
tails]
> table. Both ProductID and OrderID are foreign keys and both form the
> primary key.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinnaclepublishing.com
> ..
> "BillyDees" <BillyDees@.discussions.microsoft.com> wrote in message
> news:2D2E6340-71BF-4FF0-B496-50D514C9D6B5@.microsoft.com...
> Here is a off the wall question. Whare are either the pros or cons of havi
ng
> the same column in a table with both a primary key and foreign key?
> I know that I can do this, because I tested it. But, I don't know if there
> are good or bad affects from doing so.
> thanks
>

Primary and Foreign Keys

Hey all,

I'm sure this is elementary but, does anyone have a basic SQL that will list primary keys and foreign keys for a set of tables? I've been asked to use SQL*Plus to sort through several hundred tables. I can write and run simple queries but I've never had so many tables to deal with. Thanks for any direction you can offer.

DeeI hope I understand this right. You want to look at your primary and foreign keys? In oracle there's a table user_constraints.

SQL> desc user_constraints
Naam Null? Type
------------- --- -------
OWNER NOT NULL VARCHAR2(30)
CONSTRAINT_NAME NOT NULL VARCHAR2(30)
CONSTRAINT_TYPE VARCHAR2(1)
TABLE_NAME NOT NULL VARCHAR2(30)
SEARCH_CONDITION LONG
R_OWNER VARCHAR2(30)
R_CONSTRAINT_NAME VARCHAR2(30)
DELETE_RULE VARCHAR2(9)
STATUS VARCHAR2(8)
DEFERRABLE VARCHAR2(14)
DEFERRED VARCHAR2(9)
VALIDATED VARCHAR2(13)
GENERATED VARCHAR2(14)
BAD VARCHAR2(3)
RELY VARCHAR2(4)
LAST_CHANGE DATE

This table contains all the primary keys, foreign keys, check's you've put on a table. To view those constraints do:

SELECT * FROM user_constraints;

Hope I helped you out.

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.