469,086 Members | 1,287 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,086 developers. It's quick & easy.

Disabling primary key



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!
Jul 20 '05 #1
5 3404

"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
Jul 20 '05 #2

"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

Jul 20 '05 #3
>> 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),
..);
Jul 20 '05 #4
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!
Jul 20 '05 #5
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
Jul 20 '05 #6

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

reply views Thread by Sharon Cowling | last post: by
3 posts views Thread by PB | last post: by
1 post views Thread by Matt Van Mater | last post: by
3 posts views Thread by David Parker | last post: by
9 posts views Thread by Paul Keegstra | last post: by
7 posts views Thread by Varangian | last post: by
5 posts views Thread by jehugaleahsa | last post: by
reply views Thread by kglaser89 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.