Disabling primary key

[Ghulam Farid]

Hi
i have a table with primary key defined on col1 and col2. now i want to
have col3 also included in primary key. when i alter the table it gives
me error for duplicate rows. there is an option for 'with nocheck' but
it only works with check or foreign key constraint. is there any option
in sql server like in oracle 'no validate' which doesnt validate the
existing data and force the data validation from new records.
thanx
Farid

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

[Simon Hayes]

"Ghulam Farid" <gf****@yahoo.com> wrote in message
news:40*********************@news.frii.net...

Hi
i have a table with primary key defined on col1 and col2. now i want to
have col3 also included in primary key. when i alter the table it gives
me error for duplicate rows. there is an option for 'with nocheck' but
it only works with check or foreign key constraint. is there any option
in sql server like in oracle 'no validate' which doesnt validate the
existing data and force the data validation from new records.
thanx
Farid

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

No - CHECK/NOCHECK is for foreign keys and check constraints only. I'm not
entirely sure I understand your post - are you saying that you want to allow
duplicate values in a primary key? If so, then it's not possible, and
shouldn't be. Perhaps if you can post some more details (the table DDL and
sample data), someone may be able to suggest an alternative approach.

Simon

[Greg D. Moore \(Strider\)]

"Simon Hayes" <sq*@hayes.ch> wrote in message
news:40********@news.bluewin.ch...

"Ghulam Farid" <gf****@yahoo.com> wrote in message
news:40*********************@news.frii.net...

Hi
i have a table with primary key defined on col1 and col2. now i want to
have col3 also included in primary key. when i alter the table it gives
me error for duplicate rows. there is an option for 'with nocheck' but
it only works with check or foreign key constraint. is there any option
in sql server like in oracle 'no validate' which doesnt validate the
existing data and force the data validation from new records.
thanx
Farid

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!
No - CHECK/NOCHECK is for foreign keys and check constraints only. I'm not
entirely sure I understand your post - are you saying that you want to

allow duplicate values in a primary key? If so, then it's not possible, and
shouldn't be. Perhaps if you can post some more details (the table DDL and
sample data), someone may be able to suggest an alternative approach.

I think he wants: SET IDENTITY_INSERT.

Simon

[--CELKO--]

>> I have a table with primary key defined on col1 and col2. Now I
want to
have col3 also included in primary key. When I alter the table it
gives me error for duplicate rows. <<

That does not make sense to me. Given this

CREATE TABLE Foobar
(col1 INTEGER NOT NULL,
col2 INTEGER NOT NULL,
col3 INTEGER NOT NULL,
PRIMARY KEY (col1, col2),
..);

Then a superkey is still unique:

CREATE TABLE Foobar
(col1 INTEGER NOT NULL,
col2 INTEGER NOT NULL,
col3 INTEGER NOT NULL,
PRIMARY KEY (col1, col2, col3),
..);

[Ghulam Farid]

i think u people didn't understand the problem.
i have created table
create table test(col1 int, col2 int, col3 int,col4....)
Primary key(col1,col2)
as there is primary key on col1 and col2 no duolicate data can exist in
them now the scenario changed i have to change the primary key on the
table. now when i alter the table
alter table test primary key (col1, col3, col4) it gives me error
duplicate rows exist.
but in my scenario i want the existing duplication to remain in the
table. and the primary key enforcement starts from new data.
in oracle there is an option of 'no validate' which doesnt check the
existing data in the table but enforce the uniqueness of data from new
records. i want to know is there any option available in sql server
which doesnt check the existing data but enforces the uniqueness of
records from new records.


*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

[Simon Hayes]

Ghulam Farid <gf****@yahoo.com> wrote in message news:<40*********************@news.frii.net>...

i think u people didn't understand the problem.
i have created table
create table test(col1 int, col2 int, col3 int,col4....)
Primary key(col1,col2)
as there is primary key on col1 and col2 no duolicate data can exist in
them now the scenario changed i have to change the primary key on the
table. now when i alter the table
alter table test primary key (col1, col3, col4) it gives me error
duplicate rows exist.
but in my scenario i want the existing duplication to remain in the
table. and the primary key enforcement starts from new data.
in oracle there is an option of 'no validate' which doesnt check the
existing data in the table but enforce the uniqueness of data from new
records. i want to know is there any option available in sql server
which doesnt check the existing data but enforces the uniqueness of
records from new records.


*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

It sounds like you want to place a primary key on columns which
contain duplicates, but you want to ignore those duplicates and only
enforce the primary key for new values? If so, then it's not possible
- all values in a primary key must always be unique, otherwise it
couldn't be a primary key.

Simon
create table dbo.Test (
col1 int not null,
col2 int not null,
col3 int not null,
col4 int not null,
constraint PK_Test primary key (col1, col2)
)
go

insert into dbo.Test
select 1,1,1,1
union all
select 1,2,1,1
union all
select 3,1,1,1
go

alter table dbo.Test
drop constraint PK_Test

-- This will always fail because of duplicate data
alter table dbo.Test
add constraint PK_Test primary key (col1, col3, col4)
go

drop table dbo.Test
go