Accessing more than: primary

Showing posts with label primary. Show all posts

Thursday, March 29, 2012

Adding a primary key to a table with two records

This may seem like a stupid question but ...
say if I have a table called Gender with only two records
ID DESC
1 MALE
2 FEMALE
is it worth building an index by making ID the primary key?
Joe,
No, not from a performance standpoint.
HTH
Jerry
"Joe" <Joe@.discussions.microsoft.com> wrote in message
news:69BE0199-5524-4DF0-A562-EAF14EBF1B07@.microsoft.com...
> This may seem like a stupid question but ...
> say if I have a table called Gender with only two records
> ID DESC
> 1 MALE
> 2 FEMALE
> is it worth building an index by making ID the primary key?
|||I would say no, since the selectivity for your index is very low
SQL wouldn't use the index anyway
http://sqlservercode.blogspot.com/
"Joe" wrote:

> This may seem like a stupid question but ...
> say if I have a table called Gender with only two records
> ID DESC
> 1 MALE
> 2 FEMALE
> is it worth building an index by making ID the primary key?
|||SQL,
Actually the optimizer would use the index as the heap structure would now
be a clustered index (default for PK). However, I agree I wouldn't expect
any perfomance increase.
HTH
Jerry
"SQL" <SQL@.discussions.microsoft.com> wrote in message
news:3E835950-F600-4F5E-8EB6-F184F18F2564@.microsoft.com...[vbcol=seagreen]
>I would say no, since the selectivity for your index is very low
> SQL wouldn't use the index anyway
> http://sqlservercode.blogspot.com/
> "Joe" wrote:
|||OK thanks for your advice
Joe
"Jerry Spivey" wrote:

> Joe,
> No, not from a performance standpoint.
> HTH
> Jerry
> "Joe" <Joe@.discussions.microsoft.com> wrote in message
> news:69BE0199-5524-4DF0-A562-EAF14EBF1B07@.microsoft.com...
>
>
|||Why not? Make the PK a clustered index and you don't waste space. It won't improve perf, but OTOH,
it won't hurt anything. And you don't have a table without PK.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Joe" <Joe@.discussions.microsoft.com> wrote in message
news:69BE0199-5524-4DF0-A562-EAF14EBF1B07@.microsoft.com...
> This may seem like a stupid question but ...
> say if I have a table called Gender with only two records
> ID DESC
> 1 MALE
> 2 FEMALE
> is it worth building an index by making ID the primary key?
|||another thing too (contributing an answer to my own question here) I couldn't
create a foreign key constraint on another table without making the ID a
primary key. You never know with gender - you might get an unknown one
slipped in there!
Thank you SQL Server boffins for you input on this.
"Tibor Karaszi" wrote:

> Why not? Make the PK a clustered index and you don't waste space. It won't improve perf, but OTOH,
> it won't hurt anything. And you don't have a table without PK.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Joe" <Joe@.discussions.microsoft.com> wrote in message
> news:69BE0199-5524-4DF0-A562-EAF14EBF1B07@.microsoft.com...
>

Adding a primary key to a table with two records

This may seem like a stupid question but ...
say if I have a table called Gender with only two records
ID DESC
1 MALE
2 FEMALE
is it worth building an index by making ID the primary key?Joe,
No, not from a performance standpoint.
HTH
Jerry
"Joe" <Joe@.discussions.microsoft.com> wrote in message
news:69BE0199-5524-4DF0-A562-EAF14EBF1B07@.microsoft.com...
> This may seem like a stupid question but ...
> say if I have a table called Gender with only two records
> ID DESC
> 1 MALE
> 2 FEMALE
> is it worth building an index by making ID the primary key?|||I would say no, since the selectivity for your index is very low
SQL wouldn't use the index anyway
http://sqlservercode.blogspot.com/
"Joe" wrote:
> This may seem like a stupid question but ...
> say if I have a table called Gender with only two records
> ID DESC
> 1 MALE
> 2 FEMALE
> is it worth building an index by making ID the primary key?|||SQL,
Actually the optimizer would use the index as the heap structure would now
be a clustered index (default for PK). However, I agree I wouldn't expect
any perfomance increase.
HTH
Jerry
"SQL" <SQL@.discussions.microsoft.com> wrote in message
news:3E835950-F600-4F5E-8EB6-F184F18F2564@.microsoft.com...
>I would say no, since the selectivity for your index is very low
> SQL wouldn't use the index anyway
> http://sqlservercode.blogspot.com/
> "Joe" wrote:
>> This may seem like a stupid question but ...
>> say if I have a table called Gender with only two records
>> ID DESC
>> 1 MALE
>> 2 FEMALE
>> is it worth building an index by making ID the primary key?|||OK thanks for your advice
Joe
"Jerry Spivey" wrote:
> Joe,
> No, not from a performance standpoint.
> HTH
> Jerry
> "Joe" <Joe@.discussions.microsoft.com> wrote in message
> news:69BE0199-5524-4DF0-A562-EAF14EBF1B07@.microsoft.com...
> > This may seem like a stupid question but ...
> > say if I have a table called Gender with only two records
> > ID DESC
> > 1 MALE
> > 2 FEMALE
> > is it worth building an index by making ID the primary key?
>
>|||Why not? Make the PK a clustered index and you don't waste space. It won't improve perf, but OTOH,
it won't hurt anything. And you don't have a table without PK.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Joe" <Joe@.discussions.microsoft.com> wrote in message
news:69BE0199-5524-4DF0-A562-EAF14EBF1B07@.microsoft.com...
> This may seem like a stupid question but ...
> say if I have a table called Gender with only two records
> ID DESC
> 1 MALE
> 2 FEMALE
> is it worth building an index by making ID the primary key?|||another thing too (contributing an answer to my own question here) I couldn't
create a foreign key constraint on another table without making the ID a
primary key. You never know with gender - you might get an unknown one
slipped in there!
Thank you SQL Server boffins for you input on this.
"Tibor Karaszi" wrote:
> Why not? Make the PK a clustered index and you don't waste space. It won't improve perf, but OTOH,
> it won't hurt anything. And you don't have a table without PK.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Joe" <Joe@.discussions.microsoft.com> wrote in message
> news:69BE0199-5524-4DF0-A562-EAF14EBF1B07@.microsoft.com...
> > This may seem like a stupid question but ...
> > say if I have a table called Gender with only two records
> > ID DESC
> > 1 MALE
> > 2 FEMALE
> > is it worth building an index by making ID the primary key?
>

Adding a primary key to a table with two records

This may seem like a stupid question but ...
say if I have a table called Gender with only two records
ID DESC
1 MALE
2 FEMALE
is it worth building an index by making ID the primary key?Joe,
No, not from a performance standpoint.
HTH
Jerry
"Joe" <Joe@.discussions.microsoft.com> wrote in message
news:69BE0199-5524-4DF0-A562-EAF14EBF1B07@.microsoft.com...
> This may seem like a stupid question but ...
> say if I have a table called Gender with only two records
> ID DESC
> 1 MALE
> 2 FEMALE
> is it worth building an index by making ID the primary key?|||I would say no, since the selectivity for your index is very low
SQL wouldn't use the index anyway
http://sqlservercode.blogspot.com/
"Joe" wrote:

> This may seem like a stupid question but ...
> say if I have a table called Gender with only two records
> ID DESC
> 1 MALE
> 2 FEMALE
> is it worth building an index by making ID the primary key?|||SQL,
Actually the optimizer would use the index as the heap structure would now
be a clustered index (default for PK). However, I agree I wouldn't expect
any perfomance increase.
HTH
Jerry
"SQL" <SQL@.discussions.microsoft.com> wrote in message
news:3E835950-F600-4F5E-8EB6-F184F18F2564@.microsoft.com...[vbcol=seagreen]
>I would say no, since the selectivity for your index is very low
> SQL wouldn't use the index anyway
> http://sqlservercode.blogspot.com/
> "Joe" wrote:
>|||OK thanks for your advice
Joe
"Jerry Spivey" wrote:

> Joe,
> No, not from a performance standpoint.
> HTH
> Jerry
> "Joe" <Joe@.discussions.microsoft.com> wrote in message
> news:69BE0199-5524-4DF0-A562-EAF14EBF1B07@.microsoft.com...
>
>|||Why not? Make the PK a clustered index and you don't waste space. It won't i
mprove perf, but OTOH,
it won't hurt anything. And you don't have a table without PK.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Joe" <Joe@.discussions.microsoft.com> wrote in message
news:69BE0199-5524-4DF0-A562-EAF14EBF1B07@.microsoft.com...
> This may seem like a stupid question but ...
> say if I have a table called Gender with only two records
> ID DESC
> 1 MALE
> 2 FEMALE
> is it worth building an index by making ID the primary key?|||another thing too (contributing an answer to my own question here) I couldn'
t
create a foreign key constraint on another table without making the ID a
primary key. You never know with gender - you might get an unknown one
slipped in there!
Thank you SQL Server boffins for you input on this.
"Tibor Karaszi" wrote:

> Why not? Make the PK a clustered index and you don't waste space. It won't
improve perf, but OTOH,
> it won't hurt anything. And you don't have a table without PK.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Joe" <Joe@.discussions.microsoft.com> wrote in message
> news:69BE0199-5524-4DF0-A562-EAF14EBF1B07@.microsoft.com...
>

Thursday, March 8, 2012

Add records in a table

Hi:
I need to add some records in a table called location(primary key:
loc_id). What I want to do is for each location in the table, I add
the same record but with a different loc_id, which can be a random
string. All the other column should contain the same value. Can anyone
give me a hint on how to do this in SQL server 2000 enterprise
manager?
thx."Hamilton sucks" <caof@.mcmaster.cawrote in message
news:1189201676.191048.77380@.22g2000hsm.googlegrou ps.com...

Quote:

Originally Posted by

A strange design. If the only key is random then how do you hope to retrieve
the information? If the rest of the data is to be identical then why bother
copying it?

DECLARE @.loc_id VARCHAR(36);
SET @.loc_id = CAST(NEWID() AS VARCHAR(36));

INSERT INTO location (@.loc_id, col1, col2, ...)
SELECT col1, col2, ...
FROM location ;

--
David Portas|||David Portas (REMOVE_BEFORE_REPLYING_dportas@.acm.org) writes:

Quote:

Originally Posted by

"Hamilton sucks" <caof@.mcmaster.cawrote in message
news:1189201676.191048.77380@.22g2000hsm.googlegrou ps.com...

Quote:

Originally Posted by

>I need to add some records in a table called location(primary key:
>loc_id). What I want to do is for each location in the table, I add
>the same record but with a different loc_id, which can be a random
>string. All the other column should contain the same value. Can anyone
>give me a hint on how to do this in SQL server 2000 enterprise
>manager?
>thx.
>>

>
A strange design. If the only key is random then how do you hope to
retrieve the information? If the rest of the data is to be identical
then why bother copying it?

Maybe he is generating test data?

Quote:

Originally Posted by

DECLARE @.loc_id VARCHAR(36);
SET @.loc_id = CAST(NEWID() AS VARCHAR(36));
>
INSERT INTO location (@.loc_id, col1, col2, ...)
SELECT col1, col2, ...
FROM location ;

That does not look like it would work out. :-)

As I understand Hamilton, he wants each copied row to have each own
new id. Using newid() this would be:

INSERT location (loc_id, col1, col2, ...)
SELECT convert(char(36), newid()), col1, col2, ...
FROM location

Obviously, this will not work if loc_id is shorter than 36 characters.
Hamilton could use substring, but obviously the short loc_id is the
bigger the possibility for duplicates.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||"Erland Sommarskog" <esquel@.sommarskog.sewrote in message
news:Xns99A56D13F4F0DYazorman@.127.0.0.1...

Quote:

Originally Posted by

David Portas (REMOVE_BEFORE_REPLYING_dportas@.acm.org) writes:

Quote:

Originally Posted by

>"Hamilton sucks" <caof@.mcmaster.cawrote in message
>news:1189201676.191048.77380@.22g2000hsm.googlegrou ps.com...

Quote:

Originally Posted by

>>I need to add some records in a table called location(primary key:
>>loc_id). What I want to do is for each location in the table, I add
>>the same record but with a different loc_id, which can be a random
>>string. All the other column should contain the same value. Can anyone
>>give me a hint on how to do this in SQL server 2000 enterprise
>>manager?
>>thx.
>>>

>>
>A strange design. If the only key is random then how do you hope to
>retrieve the information? If the rest of the data is to be identical
>then why bother copying it?

>
Maybe he is generating test data?
>

Quote:

Originally Posted by

>DECLARE @.loc_id VARCHAR(36);
>SET @.loc_id = CAST(NEWID() AS VARCHAR(36));
>>
>INSERT INTO location (@.loc_id, col1, col2, ...)
> SELECT col1, col2, ...
> FROM location ;

>
That does not look like it would work out. :-)
>
As I understand Hamilton, he wants each copied row to have each own
new id. Using newid() this would be:
>
INSERT location (loc_id, col1, col2, ...)
SELECT convert(char(36), newid()), col1, col2, ...
FROM location
>
>
Obviously, this will not work if loc_id is shorter than 36 characters.
Hamilton could use substring, but obviously the short loc_id is the
bigger the possibility for duplicates.
>
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
>
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

Thanks Erland. My mistake.

--
David Portas|||On Sep 7, 6:08 pm, "David Portas"
<REMOVE_BEFORE_REPLYING_dpor...@.acm.orgwrote:

Quote:

Originally Posted by

"Hamilton sucks" <c...@.mcmaster.cawrote in message
>
news:1189201676.191048.77380@.22g2000hsm.googlegrou ps.com...
>

Quote:

Originally Posted by

>
A strange design. If the only key is random then how do you hope to retrieve
the information? If the rest of the data is to be identical then why bother
copying it?
>
DECLARE @.loc_id VARCHAR(36);
SET @.loc_id = CAST(NEWID() AS VARCHAR(36));
>
INSERT INTO location (@.loc_id, col1, col2, ...)
SELECT col1, col2, ...
FROM location ;
>
--
David Portas

Thanks, david for your help. The reason for copying records is that I
need to change them to new records, which are exactly the same as the
old records except one column value. The loc_id is the primary key but
it's not really used for searching information. So basically I don't
care about the loc_id as long as they are unique, that 's why i want
it to be random.|||On Sep 8, 4:38 am, Erland Sommarskog <esq...@.sommarskog.sewrote:

Quote:

Originally Posted by

David Portas (REMOVE_BEFORE_REPLYING_dpor...@.acm.org) writes:

Quote:

Originally Posted by

"Hamilton sucks" <c...@.mcmaster.cawrote in message
news:1189201676.191048.77380@.22g2000hsm.googlegrou ps.com...

Quote:

Originally Posted by

I need to add some records in a table called location(primary key:
loc_id). What I want to do is for each location in the table, I add
the same record but with a different loc_id, which can be a random
string. All the other column should contain the same value. Can anyone
give me a hint on how to do this in SQL server 2000 enterprise
manager?
thx.

Quote:

Originally Posted by

A strange design. If the only key is random then how do you hope to
retrieve the information? If the rest of the data is to be identical
then why bother copying it?

>
Maybe he is generating test data?
>

Quote:

Originally Posted by

DECLARE @.loc_id VARCHAR(36);
SET @.loc_id = CAST(NEWID() AS VARCHAR(36));

Quote:

Originally Posted by

INSERT INTO location (@.loc_id, col1, col2, ...)
SELECT col1, col2, ...
FROM location ;

>
That does not look like it would work out. :-)
>
As I understand Hamilton, he wants each copied row to have each own
new id. Using newid() this would be:
>
INSERT location (loc_id, col1, col2, ...)
SELECT convert(char(36), newid()), col1, col2, ...
FROM location
>
Obviously, this will not work if loc_id is shorter than 36 characters.
Hamilton could use substring, but obviously the short loc_id is the
bigger the possibility for duplicates.
>
--
Erland Sommarskog, SQL Server MVP, esq...@.sommarskog.se
>
Books Online for SQL Server 2005 athttp://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books...
Books Online for SQL Server 2000 athttp://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

Maybe I should consult about the design. The table location holds the
location info about the local schools, each of them is linked to a
test group and a set of students. If I want to add a new test group,
which contains the same set of locations except that they are linked
to the new group. Should I create a new table or add new records into
tbl_location? The same problem holds for tbl_students as well since in
the new group, all students' status must be reset.|||"Hamilton sucks" <caof@.mcmaster.cawrote in message
news:1189273920.752746.25660@.o80g2000hse.googlegro ups.com...

Quote:

Originally Posted by

>
The reason for copying records is that I
need to change them to new records, which are exactly the same as the
old records except one column value.

Then they are not exactly the same. Insert the new rows together including
the NEW column value(s). That way you can add any relevent candidate key
constraints to your table - something that wouldn't be possible if the table
had to support transitional "copies" of the old data. Example:

INSERT INTO tbl (col1, col2, col3)
SELECT col1, @.new_col2, col3
FROM tbl
WHERE ... ? ;

Quote:

Originally Posted by

This sounds very like a multi-valued or join-dependency situation. Are you
familiar with the Fourth and Fifth Normal Forms? If not then look up some
examples. You should satisfy yourself about the design based on your own
understanding of the business rules. It's notoriously difficult to give
detailed design advice in an online discussion. (Easy to spot potential
problems but hard to suggest the right solutions).

--
David Portas

Tuesday, March 6, 2012

Add new primary key column for replicated table with pull subscrip

I need to add a new column to a replicated table. But in my case a new column should be a part of a primary key. So, I can't use sp_repladdcolumn procedure since we need to completely repopulate the whole table and there is no default value for a new colu
mn. In addition to that this column should be first in the table's columns list, not the last one what happens when you usually alter the table adding a new column.
More there, I have a pull subscription and it looks like there is no way to drop subscription for a specified article only, like it is possible for a push subscription. I would appreciate any help pointing to an easiest way to add a new primary key column
for a replicated table with pull subscription.
TIA
Libra,
ordinarily you should be able to use sp_droparticle, sp_addarticle and
sp_refreshsubscriptions however to add the new column in a specific position
you'll need to drop the published article then recreate it and resubscribe.
You might use a nosync subscription to make things easier if the snapshot is
particularly large, but this will have restrictions if you later want to add
an article or column, so I'd recommend initializing with the complete
snapshot as per usual.
HTH,
Paul Ibison
(BTW no doubt you have seen other threads on this but placing columns in a
particular order is not generally considered 'good form' as TSQL should
ideally use explicit column names rather than rely on position.)

Saturday, February 25, 2012

Add Identity Increment per user

Hi
I have a Jobs Table: Job_ID,User_ID,Job_Info,...
Job_ID is the Primary Key.
I want to add [Job_ID_PerUser] so I wiil get:
Job_ID Job_ID_PerUser User_ID
1 1 A
2 1 B
3 1 C
4 2 A
5 3 A
6 2 C
Thanks

1) Set the key as primary key...

2) In the coloum properties there in Identity Specification

3) Set it to Yes

4) And set Increment Seed As 1

|||

I have a Primary Key with the Increment Seed=1 named [Job_ID]

I want another ID for a job [Job_ID_PerUser] that will start the count from 1 for every new user

and will increment by 1 every time this new user will add a job

|||

Assuming that you are using SQL Server, you can create the a SQL Server function the will calculate the next value of for the customer and add that as the column for the server.

CREATE FUNCTION dbo.GetCustomerMax (@.userIdint)RETURNSintASBEGINRETURN SELECT MAX(Job_ID_PerUser) + 1WHERE User_Id= @.userIdEND

In SQL Server Management Studio, you modify the table, add the column of type int.

Then create a stored procedure for the insertion of the record and call the GetCustomerMax function.

|||

Thanks it works greate.

do i need to put all of that in one transaction?

Add Foreign Key Constraint in filegroup

Hi,
I'd like to create a foreign key constraint and store it in a different
filegroup than "PRIMARY"
For this, I've created a new filegroup named "INDEX"
This is my script :
ALTER TABLE [dbo].[T_FILE] WITH NOCHECK ADD
CONSTRAINT [FK_T_FILE_IDFOL_T_FOLDER] FOREIGN KEY
(
[FIL_IDFOLDER]
) REFERENCES [dbo].[T_FOLDER] (
[FOL_IDFOLDER]
) ON DELETE CASCADE
ON [INDEX]
GO
And it doesn't work !
But what is very strange, is that I can create a primary key constraint
correctly on this filegroup :
ALTER TABLE [dbo].[T_FILE] WITH NOCHECK ADD
CONSTRAINT [PK_T_FILE] PRIMARY KEY CLUSTERED
(
[FIL_IDFILE]
) ON [INDEX]
GO
And this works !
It would be great if someone can telle me where my error is !
ThanxFilegroups are containers for storing data. Data are either data pages or in
dex pages. The reason
you can specify a filegroup when you define a primary key is that it automat
ically creates an index
for you,. so the file group you specify defines where that index will be sto
red. A foreign key
doesn't come with an index.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
<jerome.avoustin@.gmail.com> wrote in message
news:1137404237.380973.84080@.g43g2000cwa.googlegroups.com...
> Hi,
> I'd like to create a foreign key constraint and store it in a different
> filegroup than "PRIMARY"
> For this, I've created a new filegroup named "INDEX"
> This is my script :
> ALTER TABLE [dbo].[T_FILE] WITH NOCHECK ADD
> CONSTRAINT [FK_T_FILE_IDFOL_T_FOLDER] FOREIGN KEY
> (
> [FIL_IDFOLDER]
> ) REFERENCES [dbo].[T_FOLDER] (
> [FOL_IDFOLDER]
> ) ON DELETE CASCADE
> ON [INDEX]
> GO
> And it doesn't work !
> But what is very strange, is that I can create a primary key constraint
> correctly on this filegroup :
> ALTER TABLE [dbo].[T_FILE] WITH NOCHECK ADD
> CONSTRAINT [PK_T_FILE] PRIMARY KEY CLUSTERED
> (
> [FIL_IDFILE]
> ) ON [INDEX]
> GO
> And this works !
> It would be great if someone can telle me where my error is !
> Thanx
>

Friday, February 24, 2012

Add composite primary key

I'm a bit unclear on the syntax to add a composite (multiple columns)
primary key to a table. The columns already exist. Thanks.ALTER TABLE dbo.TableName ADD CONSTRAINT
PK_ConstraintName PRIMARY KEY NONCLUSTERED
(
Col1,
Col2
) ON [PRIMARY]
When in doubt, you can always perform the action in Enterprise Manager
and then look at the change script to help clear up basic syntax
things.
HTH,
Stu|||Do:
ALTER TABLE tbl ADD CONSTRAINT pk PRIMARY KEY ( col1, col2 )
Anith|||Roughly this at table creation:
CONSTRAINT PK_NameIt PRIMARY KEY CLUSTERED (Column1, Column2, Column3)
Obviously if you need a NONCLUSTERED PK, use that syntax instead.
Mark
"Rick Charnes" <rickxyz--nospam.zyxcharnes@.thehartford.com> wrote in message
news:MPG.1dc2cab9b0d541d8989902@.msnews.microsoft.com...
> I'm a bit unclear on the syntax to add a composite (multiple columns)
> primary key to a table. The columns already exist. Thanks.

Sunday, February 19, 2012

Add Column to an existing table and mention position of the column

Hi all,
Can anybody please help me in this case.

I have a table test(NAME,CITY)
Now i want to add a new column "ID" as primary key to table test and the position of the table should be before column "NAME".

If possible, Please suggest

Thanks
Bob

You can not do that using T-SQL. The way "Enterprise Manager" or "SSMS" does it, is creating a new table with layout you want, copying data, dropping constraint that references the old table, dropping the old table, renaming the new table and recreating all constraints and indexes.

In the relational world, the position of the column is not important.

AMB

|||The only way you can do that is to create a new table, then transfer the data from the old table to the new one, then drop the old table, and finally, rename the new table to the old table's name.|||

Are their any good examples for this action, i thinking it would be easier to add new columns to the aspnet_Profile Table. To expand the user's profile data into single columns instead of one blob of data that is potentially useless. When using a SqlDataProvider for the fact the data in the row comes out in one long string of information.

Unless theirs away to break that information up into useful sections for a FormView or GridView..

add column

I have a table with only about 40 rows. For some reason we accidentally
forgot the primary key. Is it possible to add a column to this table that
adds a number to each row and also adds an increment of 1 when a new item is
added. While we are at it can we make this the primary key column.
I am new at this but I will take a stab. Maybe someone can look at my syntax
and tell me if I am on the right track.
the table name is tblProductSubcategories the column I need to add will be
called intSubcategoryID
Here is my go at it.
Alter Table tblProductSubcategories
Add
intSubcategoryID int identity not null
I know that is not exactly what is needed. Can someone help me with this
syntax. Much appreciated.
Laura K>> I have a table with only about 40 rows. For some reason we
accidentally forgot the primary key. <<
That is not an accident; that is a major design flaw.
each row and also adds an increment of 1 when a new item is added.
While we are at it can we make this the primary key column. <<
BY DEFINITION this is not a primary key!! Start over and do a real
design.|||It's almost done. You just forgot the primary key. Try this
Alter Table tblProductSubcategories
Add
intSubcategoryID int identity not null <B>PRIMARY KEY</B>
--
Lionel
"Laura K" wrote:

> I have a table with only about 40 rows. For some reason we accidentally
> forgot the primary key. Is it possible to add a column to this table that
> adds a number to each row and also adds an increment of 1 when a new item
is
> added. While we are at it can we make this the primary key column.
> I am new at this but I will take a stab. Maybe someone can look at my synt
ax
> and tell me if I am on the right track.
> the table name is tblProductSubcategories the column I need to add will be
> called intSubcategoryID
> Here is my go at it.
> Alter Table tblProductSubcategories
> Add
> intSubcategoryID int identity not null
>
> I know that is not exactly what is needed. Can someone help me with this
> syntax. Much appreciated.
> Laura K
>
>|||YES!! Just add another Column defined as Integer Identity... Say it's
Customers table, and the new PK will be CustomerID...
Alter Table Customers
(Add Column CustomerID Integer Identity Primary Key Not Null)
And there's NOTHING at all wrong with using this type of Primary Key... But
you should also, if at all possible, add another "key" in the form of a
unique constrraint or index, on whichever combination of "real" data columns
uniquely identifies each row... This will prevent the additon of two rows
(with different Idnetity values) from being added that represent the same
real world business object.
"Laura K" wrote:

> I have a table with only about 40 rows. For some reason we accidentally
> forgot the primary key. Is it possible to add a column to this table that
> adds a number to each row and also adds an increment of 1 when a new item
is
> added. While we are at it can we make this the primary key column.
> I am new at this but I will take a stab. Maybe someone can look at my synt
ax
> and tell me if I am on the right track.
> the table name is tblProductSubcategories the column I need to add will be
> called intSubcategoryID
> Here is my go at it.
> Alter Table tblProductSubcategories
> Add
> intSubcategoryID int identity not null
>
> I know that is not exactly what is needed. Can someone help me with this
> syntax. Much appreciated.
> Laura K
>
>|||Laura,
YES, you can!! Just add another Column defined as "Integer Identity
Primary Key"... Say it's Customers table, and the new PK will be CustomerID.
.
Alter Table Customers
(Add Column CustomerID Integer Identity Primary Key Not Null)
And there's NOTHING at all wrong with using this type of Primary Key... But
you should also, if at all possible, add another "key" in the form of a
unique constrraint or index, on whichever combination of "real" data columns
uniquely identifies each row... This will prevent the additon of two rows
(with different Idnetity values) from being added that represent the same
real world business object.
"Laura K" wrote:

> I have a table with only about 40 rows. For some reason we accidentally
> forgot the primary key. Is it possible to add a column to this table that
> adds a number to each row and also adds an increment of 1 when a new item
is
> added. While we are at it can we make this the primary key column.
> I am new at this but I will take a stab. Maybe someone can look at my synt
ax
> and tell me if I am on the right track.
> the table name is tblProductSubcategories the column I need to add will be
> called intSubcategoryID
> Here is my go at it.
> Alter Table tblProductSubcategories
> Add
> intSubcategoryID int identity not null
>
> I know that is not exactly what is needed. Can someone help me with this
> syntax. Much appreciated.
> Laura K
>
>|||Sorry, i forgot to remove the html tags from my answer. It should read:
Alter Table Employee
Add
intSubcategoryID int identity not null PRIMARY KEY
Lionel
"Lionel" wrote:
> It's almost done. You just forgot the primary key. Try this
> --
> Alter Table tblProductSubcategories
> Add
> intSubcategoryID int identity not null <B>PRIMARY KEY</B>
> --
>
> Lionel
>
> "Laura K" wrote:
>|||Thanks all will give it a try this evening. We actually didn't forget the
PK. We decided to use the subcat name but as I designed I got frustrated
with not having a number. Thus the change. Now when I do this will it add
the numbers incrementally to all 40 rows.
Laura
"CBretana" <cbretana@.areteIndNOSPAM.com> wrote in message
news:C8B1B527-538E-438C-A19A-26E665C25645@.microsoft.com...
> Laura,
> YES, you can!! Just add another Column defined as "Integer Identity
> Primary Key"... Say it's Customers table, and the new PK will be
> CustomerID...
> Alter Table Customers
> (Add Column CustomerID Integer Identity Primary Key Not Null)
> And there's NOTHING at all wrong with using this type of Primary Key...
> But
> you should also, if at all possible, add another "key" in the form of a
> unique constrraint or index, on whichever combination of "real" data
> columns
> uniquely identifies each row... This will prevent the additon of two rows
> (with different Idnetity values) from being added that represent the same
> real world business object.
>
> "Laura K" wrote:
>|||WOW you are so very helpful. I wish everyone could be as dramatic as you.
It really helps moral.
Laura K
"--CELKO--" <jcelko212@.earthlink.net> wrote in message
news:1116025782.902845.162170@.f14g2000cwb.googlegroups.com...
> accidentally forgot the primary key. <<
> That is not an accident; that is a major design flaw.
>
> each row and also adds an increment of 1 when a new item is added.
> While we are at it can we make this the primary key column. <<
> BY DEFINITION this is not a primary key!! Start over and do a real
> design.
>|||Yes, Although the order will be arbitrary... If you want the records
"numbered" in a specific Order, then you need to do something a bit
different... RU interested int that?
(Remember, since this is a "Surrogate" key, the actual value should not be
publicly visible or used by end-users, so the value shouldn't be important,
just that it's unique...)
"Laura K" wrote:

> Thanks all will give it a try this evening. We actually didn't forget th
e
> PK. We decided to use the subcat name but as I designed I got frustrated
> with not having a number. Thus the change. Now when I do this will it ad
d
> the numbers incrementally to all 40 rows.
> Laura
>
> "CBretana" <cbretana@.areteIndNOSPAM.com> wrote in message
> news:C8B1B527-538E-438C-A19A-26E665C25645@.microsoft.com...
>
>|||Joe,
Jeez, -- Only "killing people"?
You mean the fate of the entire space-time continumn is not at risk from
the use of Identity columns ? What a relief!! And all this time I thought..
.
THis is really good news !!
<grin>
Charly
"--CELKO--" wrote:

> My purpose is not to make you feel good. One of my purposes on
> newsgroups is to keep incompetent progammers from killing people. But
> all that matters in your world is your feelings, isn't it?
> If you don't know me or understand that statement, then Google prior
> postings about how people who screwed up schema designs by not knowing
> what 1NF was and a medical supply program to Africa I consulted on.
> You will probably kludge it with an IDENTITY column. But it needs a
> total re-design.
>

Thursday, February 16, 2012

add a Primary Key (or something like that) to a View

Hi,
I have a View like this in my SQL Server 2000:
CREATE VIEW vw_oas_linkhead
AS
SELECT *
FROM oas_linkhead
WHERE (cmpcode = SUSER_SNAME())
WITH CHECK OPTION
The problem is: When, inside an Access-applciation, I put a link to this
View, I can't delete records from the View unless I have a Primary Key
defined on the View.
I can define that Primary Key in Access, but when I refresh the Linked Table
(View) the Primary Key disspaears. This doesn't happen when the Table itself
has a Primary Key on the SQL Server. So I wouldl ike to know if there is a
possiblity to put a Primary Key on a View in Sql Server (I thought this
isn't possible?) or to kind of simulate this on another way (with a check
constraint, ... ?)?
Does anybody has any idea?
Thanks a lot!
PieterI would assume that Access would pick up the underlying table's primary key definition. Do you have
such?
You can't define a PK on a view as the view doesn't store any data in itself. You can, in some
cases, make the view with SCHEMABINING and create a unique index on the view. The question is, of
course, whether Access would pick up on that. But that is a question for the Access experts.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"DraguVaso" <pietercoucke@.hotmail.com> wrote in message
news:Opndze35EHA.3828@.TK2MSFTNGP09.phx.gbl...
> Hi,
> I have a View like this in my SQL Server 2000:
> CREATE VIEW vw_oas_linkhead
> AS
> SELECT *
> FROM oas_linkhead
> WHERE (cmpcode = SUSER_SNAME())
> WITH CHECK OPTION
> The problem is: When, inside an Access-applciation, I put a link to this
> View, I can't delete records from the View unless I have a Primary Key
> defined on the View.
> I can define that Primary Key in Access, but when I refresh the Linked Table
> (View) the Primary Key disspaears. This doesn't happen when the Table itself
> has a Primary Key on the SQL Server. So I wouldl ike to know if there is a
> possiblity to put a Primary Key on a View in Sql Server (I thought this
> isn't possible?) or to kind of simulate this on another way (with a check
> constraint, ... ?)?
> Does anybody has any idea?
> Thanks a lot!
> Pieter
>|||Well, the problem is that the underlying table doesn't have a Primary Key,
but in access it should.
I'm not allowed to change the udnerlying table, so I should put something on
theView...
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:uVHfWi35EHA.272@.TK2MSFTNGP10.phx.gbl...
> I would assume that Access would pick up the underlying table's primary
key definition. Do you have
> such?
> You can't define a PK on a view as the view doesn't store any data in
itself. You can, in some
> cases, make the view with SCHEMABINING and create a unique index on the
view. The question is, of
> course, whether Access would pick up on that. But that is a question for
the Access experts.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "DraguVaso" <pietercoucke@.hotmail.com> wrote in message
> news:Opndze35EHA.3828@.TK2MSFTNGP09.phx.gbl...
> > Hi,
> >
> > I have a View like this in my SQL Server 2000:
> > CREATE VIEW vw_oas_linkhead
> > AS
> > SELECT *
> > FROM oas_linkhead
> > WHERE (cmpcode = SUSER_SNAME())
> > WITH CHECK OPTION
> >
> > The problem is: When, inside an Access-applciation, I put a link to this
> > View, I can't delete records from the View unless I have a Primary Key
> > defined on the View.
> >
> > I can define that Primary Key in Access, but when I refresh the Linked
Table
> > (View) the Primary Key disspaears. This doesn't happen when the Table
itself
> > has a Primary Key on the SQL Server. So I wouldl ike to know if there is
a
> > possiblity to put a Primary Key on a View in Sql Server (I thought this
> > isn't possible?) or to kind of simulate this on another way (with a
check
> > constraint, ... ?)?
> >
> > Does anybody has any idea?
> >
> > Thanks a lot!
> >
> > Pieter
> >
> >
>|||The option for "putting something on the view" you find in my prior post. But the bigger question is
why the table doesn't have a PK...
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"DraguVaso" <pietercoucke@.hotmail.com> wrote in message news:OIV9Vr35EHA.828@.TK2MSFTNGP14.phx.gbl...
> Well, the problem is that the underlying table doesn't have a Primary Key,
> but in access it should.
> I'm not allowed to change the udnerlying table, so I should put something on
> theView...
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
> message news:uVHfWi35EHA.272@.TK2MSFTNGP10.phx.gbl...
>> I would assume that Access would pick up the underlying table's primary
> key definition. Do you have
>> such?
>> You can't define a PK on a view as the view doesn't store any data in
> itself. You can, in some
>> cases, make the view with SCHEMABINING and create a unique index on the
> view. The question is, of
>> course, whether Access would pick up on that. But that is a question for
> the Access experts.
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>>
>> "DraguVaso" <pietercoucke@.hotmail.com> wrote in message
>> news:Opndze35EHA.3828@.TK2MSFTNGP09.phx.gbl...
>> > Hi,
>> >
>> > I have a View like this in my SQL Server 2000:
>> > CREATE VIEW vw_oas_linkhead
>> > AS
>> > SELECT *
>> > FROM oas_linkhead
>> > WHERE (cmpcode = SUSER_SNAME())
>> > WITH CHECK OPTION
>> >
>> > The problem is: When, inside an Access-applciation, I put a link to this
>> > View, I can't delete records from the View unless I have a Primary Key
>> > defined on the View.
>> >
>> > I can define that Primary Key in Access, but when I refresh the Linked
> Table
>> > (View) the Primary Key disspaears. This doesn't happen when the Table
> itself
>> > has a Primary Key on the SQL Server. So I wouldl ike to know if there is
> a
>> > possiblity to put a Primary Key on a View in Sql Server (I thought this
>> > isn't possible?) or to kind of simulate this on another way (with a
> check
>> > constraint, ... ?)?
>> >
>> > Does anybody has any idea?
>> >
>> > Thanks a lot!
>> >
>> > Pieter
>> >
>> >
>>
>|||I don't know why it doesn't have a Primary Key: It's a table of a big
Accountant Software, so I can't change anything to the table... :-)
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:OuPd7K45EHA.1300@.TK2MSFTNGP14.phx.gbl...
> The option for "putting something on the view" you find in my prior post.
But the bigger question is
> why the table doesn't have a PK...
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> http://www.sqlug.se/
> "DraguVaso" <pietercoucke@.hotmail.com> wrote in message
news:OIV9Vr35EHA.828@.TK2MSFTNGP14.phx.gbl...
> > Well, the problem is that the underlying table doesn't have a Primary
Key,
> > but in access it should.
> > I'm not allowed to change the udnerlying table, so I should put
something on
> > theView...
> >
> > "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote
in
> > message news:uVHfWi35EHA.272@.TK2MSFTNGP10.phx.gbl...
> >> I would assume that Access would pick up the underlying table's primary
> > key definition. Do you have
> >> such?
> >> You can't define a PK on a view as the view doesn't store any data in
> > itself. You can, in some
> >> cases, make the view with SCHEMABINING and create a unique index on the
> > view. The question is, of
> >> course, whether Access would pick up on that. But that is a question
for
> > the Access experts.
> >>
> >> --
> >> Tibor Karaszi, SQL Server MVP
> >> http://www.karaszi.com/sqlserver/default.asp
> >> http://www.solidqualitylearning.com/
> >>
> >>
> >> "DraguVaso" <pietercoucke@.hotmail.com> wrote in message
> >> news:Opndze35EHA.3828@.TK2MSFTNGP09.phx.gbl...
> >> > Hi,
> >> >
> >> > I have a View like this in my SQL Server 2000:
> >> > CREATE VIEW vw_oas_linkhead
> >> > AS
> >> > SELECT *
> >> > FROM oas_linkhead
> >> > WHERE (cmpcode = SUSER_SNAME())
> >> > WITH CHECK OPTION
> >> >
> >> > The problem is: When, inside an Access-applciation, I put a link to
this
> >> > View, I can't delete records from the View unless I have a Primary
Key
> >> > defined on the View.
> >> >
> >> > I can define that Primary Key in Access, but when I refresh the
Linked
> > Table
> >> > (View) the Primary Key disspaears. This doesn't happen when the Table
> > itself
> >> > has a Primary Key on the SQL Server. So I wouldl ike to know if there
is
> > a
> >> > possiblity to put a Primary Key on a View in Sql Server (I thought
this
> >> > isn't possible?) or to kind of simulate this on another way (with a
> > check
> >> > constraint, ... ?)?
> >> >
> >> > Does anybody has any idea?
> >> >
> >> > Thanks a lot!
> >> >
> >> > Pieter
> >> >
> >> >
> >>
> >>
> >
> >
>|||I see :-(. I suggest you post this to an Access forum to see whether you can define in Access what
column define uniqueness.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"DraguVaso" <pietercoucke@.hotmail.com> wrote in message
news:e9Y2cqA6EHA.4028@.TK2MSFTNGP15.phx.gbl...
>I don't know why it doesn't have a Primary Key: It's a table of a big
> Accountant Software, so I can't change anything to the table... :-)
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
> message news:OuPd7K45EHA.1300@.TK2MSFTNGP14.phx.gbl...
>> The option for "putting something on the view" you find in my prior post.
> But the bigger question is
>> why the table doesn't have a PK...
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>> http://www.sqlug.se/
>> "DraguVaso" <pietercoucke@.hotmail.com> wrote in message
> news:OIV9Vr35EHA.828@.TK2MSFTNGP14.phx.gbl...
>> > Well, the problem is that the underlying table doesn't have a Primary
> Key,
>> > but in access it should.
>> > I'm not allowed to change the udnerlying table, so I should put
> something on
>> > theView...
>> >
>> > "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote
> in
>> > message news:uVHfWi35EHA.272@.TK2MSFTNGP10.phx.gbl...
>> >> I would assume that Access would pick up the underlying table's primary
>> > key definition. Do you have
>> >> such?
>> >> You can't define a PK on a view as the view doesn't store any data in
>> > itself. You can, in some
>> >> cases, make the view with SCHEMABINING and create a unique index on the
>> > view. The question is, of
>> >> course, whether Access would pick up on that. But that is a question
> for
>> > the Access experts.
>> >>
>> >> --
>> >> Tibor Karaszi, SQL Server MVP
>> >> http://www.karaszi.com/sqlserver/default.asp
>> >> http://www.solidqualitylearning.com/
>> >>
>> >>
>> >> "DraguVaso" <pietercoucke@.hotmail.com> wrote in message
>> >> news:Opndze35EHA.3828@.TK2MSFTNGP09.phx.gbl...
>> >> > Hi,
>> >> >
>> >> > I have a View like this in my SQL Server 2000:
>> >> > CREATE VIEW vw_oas_linkhead
>> >> > AS
>> >> > SELECT *
>> >> > FROM oas_linkhead
>> >> > WHERE (cmpcode = SUSER_SNAME())
>> >> > WITH CHECK OPTION
>> >> >
>> >> > The problem is: When, inside an Access-applciation, I put a link to
> this
>> >> > View, I can't delete records from the View unless I have a Primary
> Key
>> >> > defined on the View.
>> >> >
>> >> > I can define that Primary Key in Access, but when I refresh the
> Linked
>> > Table
>> >> > (View) the Primary Key disspaears. This doesn't happen when the Table
>> > itself
>> >> > has a Primary Key on the SQL Server. So I wouldl ike to know if there
> is
>> > a
>> >> > possiblity to put a Primary Key on a View in Sql Server (I thought
> this
>> >> > isn't possible?) or to kind of simulate this on another way (with a
>> > check
>> >> > constraint, ... ?)?
>> >> >
>> >> > Does anybody has any idea?
>> >> >
>> >> > Thanks a lot!
>> >> >
>> >> > Pieter
>> >> >
>> >> >
>> >>
>> >>
>> >
>> >
>>
>|||Well I did, and I just got the answer!
It shoudl have been the nicest oslution if I could implement it on the view,
but now I have some VBA that puts the index on the linked view after I
refreshed everything... It seemsto work fine.
Thanks a lot for the effort!
Pieter
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:uOSsf5A6EHA.4028@.TK2MSFTNGP15.phx.gbl...
> I see :-(. I suggest you post this to an Access forum to see whether you
can define in Access what
> column define uniqueness.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> http://www.sqlug.se/
>
> "DraguVaso" <pietercoucke@.hotmail.com> wrote in message
> news:e9Y2cqA6EHA.4028@.TK2MSFTNGP15.phx.gbl...
> >I don't know why it doesn't have a Primary Key: It's a table of a big
> > Accountant Software, so I can't change anything to the table... :-)
> >
> > "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote
in
> > message news:OuPd7K45EHA.1300@.TK2MSFTNGP14.phx.gbl...
> >> The option for "putting something on the view" you find in my prior
post.
> > But the bigger question is
> >> why the table doesn't have a PK...
> >>
> >> --
> >> Tibor Karaszi, SQL Server MVP
> >> http://www.karaszi.com/sqlserver/default.asp
> >> http://www.solidqualitylearning.com/
> >> http://www.sqlug.se/
> >>
> >> "DraguVaso" <pietercoucke@.hotmail.com> wrote in message
> > news:OIV9Vr35EHA.828@.TK2MSFTNGP14.phx.gbl...
> >> > Well, the problem is that the underlying table doesn't have a Primary
> > Key,
> >> > but in access it should.
> >> > I'm not allowed to change the udnerlying table, so I should put
> > something on
> >> > theView...
> >> >
> >> > "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com>
wrote
> > in
> >> > message news:uVHfWi35EHA.272@.TK2MSFTNGP10.phx.gbl...
> >> >> I would assume that Access would pick up the underlying table's
primary
> >> > key definition. Do you have
> >> >> such?
> >> >> You can't define a PK on a view as the view doesn't store any data
in
> >> > itself. You can, in some
> >> >> cases, make the view with SCHEMABINING and create a unique index on
the
> >> > view. The question is, of
> >> >> course, whether Access would pick up on that. But that is a question
> > for
> >> > the Access experts.
> >> >>
> >> >> --
> >> >> Tibor Karaszi, SQL Server MVP
> >> >> http://www.karaszi.com/sqlserver/default.asp
> >> >> http://www.solidqualitylearning.com/
> >> >>
> >> >>
> >> >> "DraguVaso" <pietercoucke@.hotmail.com> wrote in message
> >> >> news:Opndze35EHA.3828@.TK2MSFTNGP09.phx.gbl...
> >> >> > Hi,
> >> >> >
> >> >> > I have a View like this in my SQL Server 2000:
> >> >> > CREATE VIEW vw_oas_linkhead
> >> >> > AS
> >> >> > SELECT *
> >> >> > FROM oas_linkhead
> >> >> > WHERE (cmpcode = SUSER_SNAME())
> >> >> > WITH CHECK OPTION
> >> >> >
> >> >> > The problem is: When, inside an Access-applciation, I put a link
to
> > this
> >> >> > View, I can't delete records from the View unless I have a Primary
> > Key
> >> >> > defined on the View.
> >> >> >
> >> >> > I can define that Primary Key in Access, but when I refresh the
> > Linked
> >> > Table
> >> >> > (View) the Primary Key disspaears. This doesn't happen when the
Table
> >> > itself
> >> >> > has a Primary Key on the SQL Server. So I wouldl ike to know if
there
> > is
> >> > a
> >> >> > possiblity to put a Primary Key on a View in Sql Server (I thought
> > this
> >> >> > isn't possible?) or to kind of simulate this on another way (with
a
> >> > check
> >> >> > constraint, ... ?)?
> >> >> >
> >> >> > Does anybody has any idea?
> >> >> >
> >> >> > Thanks a lot!
> >> >> >
> >> >> > Pieter
> >> >> >
> >> >> >
> >> >>
> >> >>
> >> >
> >> >
> >>
> >>
> >
> >
>

Thursday, March 29, 2012

Adding a primary key to a table with two records

Adding a primary key to a table with two records

Adding a primary key to a table with two records

Thursday, March 8, 2012

Add records in a table

Tuesday, March 6, 2012

Add new primary key column for replicated table with pull subscrip

Saturday, February 25, 2012

Add Identity Increment per user

Add Foreign Key Constraint in filegroup

Friday, February 24, 2012

Add composite primary key

Sunday, February 19, 2012

Add Column to an existing table and mention position of the column

add column

Thursday, February 16, 2012

add a Primary Key (or something like that) to a View

add a Primary Key (or something like that) to a View

Accessing more than

Blog Archive

About Me