relationships, primary key sql2000 question.

Hello,

I have 2 tables with a field called userid which is uniqueidentifier and they are both primary keys.

When I add a record to table1 and the userid field is filled, what is the best way to update table 2 with the same record.

Is there an sql function that will update automatically or do I have to write code in vb.net to select the record form table1 and insert into table2.

Thanks

Peter

If you wanted to do it relationship, one of the keys would have to be a foreign key and the other the primary key, probably using a 1 to 1 relationship.

If you want to use two primary keys, you would either need to do it by T-SQL or on the .NET Server end, ex. with VB.NET.

|||

What you are looking for is called a DRI(declarative referential integrity) constraint. You create it with the enable relationship dialog box at the top of Management Studio and look for option Cascade on Update. The two links below one explains DRI and the second is a walkthrough to enable it. Hope this helps.

http://msdn2.microsoft.com/en-us/library/ms177288.aspx

http://msdn2.microsoft.com/en-us/library/ms186973.aspx

|||

Thanks guys, working through links now.

Peter

sql

Relationships, keys, UniqueIDs - why... really?!

I've done my database studies and know about normalizing, relationships, primary keys and so forth. However, when it comes to actual developing of web sites, I've always skipped setting up relationships, and at times, the primary and (especially) foreign keys.

Now I'm designing my first db in Asp.Net 2, and the thought struck me - Why should I do this?

I guess the reason for setting up relationships is that the website should not get any trouble if rules are violated or poorly written, that is, data is input in an Orders table but not the Oredered_products table or the like. The only reason I can think of for using primary keys (when not defining relationships anyway) is to get the Insert, Delete, and Update statements of GridViews etc to work.

Or am I missing something? For many years, I've been told that the keys plus relationships taken together speed up the SQL queries, but, honestly, I can't see why they should. And I guess millions of web developers do like I do - skip the relationship thing altogether.

Please enlighten me, someone! (Or let's have an interesting discussion on this topic - if there is something to discuss, that is!)

Pettrer

pettrer:

Or am I missing something? For many years, I've been told that the keys plus relationships taken together speed up the SQL queries, but, honestly, I can't see why they should. And I guess millions of web developers do like I do - skip the relationship thing altogether.

Actaully indexes speed up SQL queries, keys are only constraints used to enforce the integrity of database (but keep in mind in SQL Server a unique,cluster index is automatically created when a PRIMARY KEY) . And I guess relationships you mentioned should be about data tables in application, or reference integrity between database tables. Here are some links that may help you understand:

Constraints:http://msdn.microsoft.com/library/default.asp?url=/library/en-us/architec/8_ar_da_0777.asp

Indexes:http://msdn.microsoft.com/library/default.asp?url=/library/en-us/createdb/cm_8_des_05_30s5.asp

Creating an Index:http://msdn.microsoft.com/library/default.asp?url=/library/en-us/createdb/cm_8_des_05_8185.asp

|||

Thanks a lot for the links!

P

Relationship problem

Hi All... Two of my tables are:

Users - primary key is UserId, an int with identity turned on.

Messages - has a column named UserId that references the same in Users.

I'm using Visual Studio 2005 against a SQL 2005 database.

Using both the diagram tool and table data, I'm trying to set up the relationship implied above and am getting the following error:

Users table saved successfully.

Messages table

- unable to create relationship 'FK_Messages_UserId'.

The ALTER TABLE statement conflicted with the FOREIGN KEY constraint 'FK_Messages_UserId'. The conflict occurred in database 'XXXX', table 'dbo.Users', column 'UserId'.

I've done several other similar relationships without incident. But this one (and one or two others) refuse to work. I'm a bit of newbie with these rascals, so that doesnt help much... Any ideas what this things trying to tell me? Thanks! -- Curt

You might already have entries in your child table which have no parent entry in the parent table.

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de|||

Hi Jens... Thanks for the reply. You threw me a little at first on your use of "parent" and "child", but yeah you nailed it. To try and tie those terms to the tables in my original post, I had some records in Messages (child) that referenced a primary key that did not exist in Users (parent). Geez, these FKs really help us keep a clean house, dont they... Thanks again!! Curt

relationship inside the same table

i ve got a database that has a table...that table has a relationship between its primary key,and another field,
actuelly i did it for doing menus and sub menus,so each menu has an ID say menuID and it has DEPTH and parentID which is the menuID of the parent...
the problem is that i can not use "Cascade update Related Fields" or "Cascade Delete Related Records" which are really necessary ...for example when deleting parent ,not to have a child lost :)
i hope i ll have an answer soon,and thanks in advanced
PS: i am using MSSQL 2000 evaluation
You will need to write a trigger to meet this need. Unfortunately SQL Server does not handle the situation you describe.
|||Consider using the nested-set approach. SELECT and DELETE queries are a breeze, allowing everything in one simple statement.|||very strange...access did!!!
are u sure?|||i had to write a trigger...this is one
CREAT TRIGGER name
ON table
FOR Delete
AS
BEGIN
IF @.@.ROWCOUNT >0
Delete from table where table.parentID in (select sortID from deleted);
END
then to enable recursive triggers in my database options...otherwise it will do the trigger for one level ;)

Relationship between inserted and deleted tables?

Hi all,

I just ran across an issue on a SQL 2000 sp4 db where RI was being maintained solely with triggers. I am attempting to change the primary key of a parent table and cascade the results to all its children without using the vendor-supplied trigger code (long story...) using an INSTEAD OF trigger.

My question is: does SQL Server create any kind of relationship between the inserted and deleted tables that I could exploit since the key field is unavailable?

I am trying to avoid having to add a surrogate key to each of the children just for this activity (as there are many M rows in each and no other suitable unique column combinations that span all the child tables).

-DC

As far as I understood your question, no. But the deleted and the inserted tables will always have the full structure of the modified table, so the key column should be available for you ?

Jens K. Suessmeyer

http://www.sqlserver2005.de

|||

Hi Jens,

This code should help clear up my question. Case 2 is what I am talking about.

create table tt (pk int primary key,col2 int,col3 int)

create table tg (pk int primary key,col2 int,col3 int)

insert tt values (1,2,3)

insert tg values (1,3,7)

go

create trigger tr1

on tt

instead of update

as

begin

select pk,col2,col3 from inserted

select pk,col2,col3 from deleted

end

go

set nocount on

print 'Case 1. Non-key field updated. Can join on pk'

print ' '

update tt set col2=9 where col2=2

print ' '

print 'Case 2. Key field updated. Cannot join on pk'

print ' '

update tt set pk=3 where pk=1

drop table tt

go

drop table tg

go

|||

OK, got it. YOu will have to join on all non key columns then. for performance reasons, you could check the updated column and use the update on the PK if the OK was not updated and the covering join when then PK was updated.

|||

Much appreciated, Jens. I will try that. Thanks!

sql

Reinitialize subscriptions in transactional replication

If I have one table in one publication in transactional replication
replication between primary and replicate is broken because subscription is marked as inactive.

If there are 3 rows on replicate and 5 rows on primary , out of which 2 are added after replication is broken

If I do

Reinitialize subscriptions
start the snapshot agent
start the distribution agent

Does this mean that it will only transfer the new 2 rows to replicate sites ? or will it drop everything from replicate site and apply all rows from primary site on to replicate.

Any help is appreciatedIt will drop every article the subscriber subscribes and refresh the DDL and data from the publication.

Reindexing

I have a couple tables where I need to change one of the column in the
primary key. On my test database it takes about 2.5 hours.
I am looking for suggestion on speeding that time up.
basically:
BEGIN Trans
ALTER TABLE dbo.tbl DROP CONSTRAINT pk_index1
GO
ALTER TABLE dbo.tbl ALTER COLUMN [Dialed] [char] (25) NOT NULL
GO
ALTER TABLE dbo.tbl ADD CONSTRAINT pk_index1 PRIMARY KEY CLUSTERED (
AreaCode, Number, CallTime DESC, Dialed ) ON [PRIMARY]
GO
COMMIT
Regards,
JohnHave you any other indexes on the table? If so, drop them first and add
them back last.
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"John J. Hughes II" <no@.invalid.com> wrote in message
news:e6UquFkuFHA.1132@.TK2MSFTNGP10.phx.gbl...
I have a couple tables where I need to change one of the column in the
primary key. On my test database it takes about 2.5 hours.
I am looking for suggestion on speeding that time up.
basically:
BEGIN Trans
ALTER TABLE dbo.tbl DROP CONSTRAINT pk_index1
GO
ALTER TABLE dbo.tbl ALTER COLUMN [Dialed] [char] (25) NOT NULL
GO
ALTER TABLE dbo.tbl ADD CONSTRAINT pk_index1 PRIMARY KEY CLUSTERED (
AreaCode, Number, CallTime DESC, Dialed ) ON [PRIMARY]
GO
COMMIT
Regards,
John|||John,
Try dropping any associated nonclustered indexes prior to changing the
PRIMARY KEY.
HTH
Jerry
"John J. Hughes II" <no@.invalid.com> wrote in message
news:e6UquFkuFHA.1132@.TK2MSFTNGP10.phx.gbl...
>I have a couple tables where I need to change one of the column in the
>primary key. On my test database it takes about 2.5 hours.
> I am looking for suggestion on speeding that time up.
> basically:
> BEGIN Trans
> ALTER TABLE dbo.tbl DROP CONSTRAINT pk_index1
> GO
> ALTER TABLE dbo.tbl ALTER COLUMN [Dialed] [char] (25) NOT NULL
> GO
> ALTER TABLE dbo.tbl ADD CONSTRAINT pk_index1 PRIMARY KEY CLUSTERED (
> AreaCode, Number, CallTime DESC, Dialed ) ON [PRIMARY]
> GO
> COMMIT
> Regards,
> John
>|||Hi,
Do the below steps in your test environement
1. Backup the database
2. Take the script of all indexes
3. Drop the indexes
4. Now drop the PK constraint
5. Now create the PK constraint with new columns
6. Create all indexes based on the script generated
Estimate the time taken. This will be the downtime you required to perform
the task in production.
Thanks
hari
SQL Server MVP
"John J. Hughes II" <no@.invalid.com> wrote in message
news:e6UquFkuFHA.1132@.TK2MSFTNGP10.phx.gbl...
>I have a couple tables where I need to change one of the column in the
>primary key. On my test database it takes about 2.5 hours.
> I am looking for suggestion on speeding that time up.
> basically:
> BEGIN Trans
> ALTER TABLE dbo.tbl DROP CONSTRAINT pk_index1
> GO
> ALTER TABLE dbo.tbl ALTER COLUMN [Dialed] [char] (25) NOT NULL
> GO
> ALTER TABLE dbo.tbl ADD CONSTRAINT pk_index1 PRIMARY KEY CLUSTERED (
> AreaCode, Number, CallTime DESC, Dialed ) ON [PRIMARY]
> GO
> COMMIT
> Regards,
> John
>|||Thanks to you and the others, basically I have been doing it backwards,
dropping the PK first and then the other indexes. I was restoring the PK
first.
By "take the script of all indexes" are you saying to basically save what
they are? You would not have a quick way of doing that, currently my code
assume I know what the indexes are which in a least one location was
incorrect.
I am also not dropping the indexes that don't affect the column I am
changing, I assume that helps.
Regards,
John
"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
news:%23KdUMcluFHA.904@.tk2msftngp13.phx.gbl...
> Hi,
> Do the below steps in your test environement
> 1. Backup the database
> 2. Take the script of all indexes
> 3. Drop the indexes
> 4. Now drop the PK constraint
> 5. Now create the PK constraint with new columns
> 6. Create all indexes based on the script generated
> Estimate the time taken. This will be the downtime you required to
> perform the task in production.
> Thanks
> hari
> SQL Server MVP
> "John J. Hughes II" <no@.invalid.com> wrote in message
> news:e6UquFkuFHA.1132@.TK2MSFTNGP10.phx.gbl...
>>I have a couple tables where I need to change one of the column in the
>>primary key. On my test database it takes about 2.5 hours.
>> I am looking for suggestion on speeding that time up.
>> basically:
>> BEGIN Trans
>> ALTER TABLE dbo.tbl DROP CONSTRAINT pk_index1
>> GO
>> ALTER TABLE dbo.tbl ALTER COLUMN [Dialed] [char] (25) NOT NULL
>> GO
>> ALTER TABLE dbo.tbl ADD CONSTRAINT pk_index1 PRIMARY KEY CLUSTERED (
>> AreaCode, Number, CallTime DESC, Dialed ) ON [PRIMARY]
>> GO
>> COMMIT
>> Regards,
>> John
>|||Since your PK is clustered, then all indexes will be affected by changes to
it. Therefore, drop all nonclustered indexes, followed by the clustered
index (primary key, in your case). Do the ALTER, then add the PK, followed
by the nonclustered indexes.
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"John J. Hughes II" <no@.invalid.com> wrote in message
news:%23zjor1uuFHA.3388@.TK2MSFTNGP10.phx.gbl...
Thanks to you and the others, basically I have been doing it backwards,
dropping the PK first and then the other indexes. I was restoring the PK
first.
By "take the script of all indexes" are you saying to basically save what
they are? You would not have a quick way of doing that, currently my code
assume I know what the indexes are which in a least one location was
incorrect.
I am also not dropping the indexes that don't affect the column I am
changing, I assume that helps.
Regards,
John
"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
news:%23KdUMcluFHA.904@.tk2msftngp13.phx.gbl...
> Hi,
> Do the below steps in your test environement
> 1. Backup the database
> 2. Take the script of all indexes
> 3. Drop the indexes
> 4. Now drop the PK constraint
> 5. Now create the PK constraint with new columns
> 6. Create all indexes based on the script generated
> Estimate the time taken. This will be the downtime you required to
> perform the task in production.
> Thanks
> hari
> SQL Server MVP
> "John J. Hughes II" <no@.invalid.com> wrote in message
> news:e6UquFkuFHA.1132@.TK2MSFTNGP10.phx.gbl...
>>I have a couple tables where I need to change one of the column in the
>>primary key. On my test database it takes about 2.5 hours.
>> I am looking for suggestion on speeding that time up.
>> basically:
>> BEGIN Trans
>> ALTER TABLE dbo.tbl DROP CONSTRAINT pk_index1
>> GO
>> ALTER TABLE dbo.tbl ALTER COLUMN [Dialed] [char] (25) NOT NULL
>> GO
>> ALTER TABLE dbo.tbl ADD CONSTRAINT pk_index1 PRIMARY KEY CLUSTERED (
>> AreaCode, Number, CallTime DESC, Dialed ) ON [PRIMARY]
>> GO
>> COMMIT
>> Regards,
>> John
>

regular primary keys vs autonumber primary keys

What is the best way to handle primary key selection in general? It would seem that autonumbered primary keys would result in faster query times, but what if there is another field in the table which is a PK canidate and must be unique? For example lets say I have a product table with an autonumbered PK. This table also has a product ID which must be unique. Would it be best to keep the autonumbered ID as the PK and put a unique constraint on the product ID maybe? OR would it be best to change the PK to the product ID and nix the autonumber all-together? All thoughts on this would be appreciated.Originally posted by Chuckt
What is the best way to handle primary key selection in general? It would seem that autonumbered primary keys would result in faster query times, but what if there is another field in the table which is a PK canidate and must be unique? For example lets say I have a product table with an autonumbered PK. This table also has a product ID which must be unique. Would it be best to keep the autonumbered ID as the PK and put a unique constraint on the product ID maybe? OR would it be best to change the PK to the product ID and nix the autonumber all-together? All thoughts on this would be appreciated.

If table does not have natural key like state abbreviation (KY - Kentucky - I was living there almost three years) you have to create fake primary key. Usually it is integer (SQL2000 does have bigint) field. Should you use IDENTITY? It depends on what kind of table (lookup or not), what kind of database, will you use replication or not, etc. Identity does have some pluses and exactly the same quantity of minuses. If you are going to change data frequently in tables by 'hands' (not from application) - do not use IDENTITY. You can use SET IDENTITY_INSERT for inserting into the identity column of a table.|||Thanks for the reply snail. My dilemma is this... I have a table that relates two other tables(its primary key is the primary keys of the other two tables it is relating). It has over 2 million rows. The primary keys of all three tables are clustered indexes and the data types of the fields are varchar 20. The query times are a little slow and I was trying to figure out ways to increase the speed. Would it be faster to use an autonumber for the three tables? It would seem to me that it would be faster to join in a query based on an index comprised of less characters. How much faster and if it is worth it I am not sure.|||Couple things to think about.

(1) Joins are better utilized on integer fields and are faster.

(2) What type of inserts do you have, the nice thing about having an identity as a PK and clustered index is all inserts fall to the last leaf level thereby reducing page splits. If your data is solely for querying data, this is a waste though.

(3) Are you running certain queries the majority of the time that only consist of a few of the columns? If so, you might think about a composite non-clustered index to have applicable columns in. Having a non-clustered covering index on the columns you need is faster than having to conduct table scans, or even clustered index key locks.

HTH|||"(2) What type of inserts do you have, the nice thing about having an identity as a PK and clustered index is all inserts fall to the last leaf level thereby reducing page splits."

Yes, but unless you reindex won't your query efficiency be reduced because the average number of pages traversed will increase. In effect, the data structure will be lopsided toward the latest inserted values:

/\
../\
.../\
...../\
....../\

instead of:

../\
./\/\
/\/\/\

blindman|||An int as a foreign key will be faster than a wider key because it takes up less space and less page reads.
It can cause the system to be slower though because to obtain any data from the other table you will need to join to it. Using the natural key the data in that key is available on the referred table.
Not so bad with just two tables but

t1 (col1, col2, col3)
t2 (t1_col1, t1_co2,t1_col3)
t3 (t1_col3)

This is quite common. With the natural keys you can join t2 and t3. If you replace with artificial keys you would have to join to t1 as well so causing more page reads and using up more memory.

It's a subject that people get very passionate about. I disagree with those that say that all tables should have an artificial key and this should always be used for the join field but wouldn't disagree that it can sometimes be useful to create one for efficiency or ease of coding.|||well, that's why after going through structural normalization process designers revise the design with DE-normalization steps. it still is an evidence of consistency and is easy to follow. btw, you don't have to join t1 in the example you give while using "artificial keys".|||The B-tree still maintains ranges but yes it is a trade-off, if you are heavy reads, low inserts, you should not use this method, but if you are heavy inserts with medium to heavy reads, this is good for minimizing page splits without having to play with fill factors.

Nigel, I'm one of those that recommends a surrogate key on every table, just seen too many times a company changes their primary key algorithm and it makes things easier in my opinion when you push to a warehouse. But I won't argue that passionately for it, to each his own :)

Originally posted by blindman
"(2) What type of inserts do you have, the nice thing about having an identity as a PK and clustered index is all inserts fall to the last leaf level thereby reducing page splits."

Yes, but unless you reindex won't your query efficiency be reduced because the average number of pages traversed will increase. In effect, the data structure will be lopsided toward the latest inserted values:

/\
../\
.../\
...../\
....../\

instead of:

../\
./\/\
/\/\/\

blindman|||I used to use natural keys a lot, but I found that the lower-level tables in schemas designed with natural keys tended to have multi-column indexes with multiple joins to related tables. I still consider using natural keys for higher-level tables, especially look-up tables.

Consider the needs of your application as well. By standardizing the key you simplify things for developers who always know what type of field to submit for searches, etc. If you use a natural key and then decide you want to change it (length or type) you may force changes in your middle tier.

...and one other cool thing I was able to do with GUIDs. We had developers who wanted to be able to lookup information based on the ID of an employee record, but they also wanted the same functionality by submitting the session-specific security token the employee was using. Since both were GUIDs, I was able to create a single procedure which required only a single parameter @.EmployeeIDorToken, and determine within the procedure which one was submitted. I could count on the fact that the same GUID would never exist in both tables.

blindman

Registered Customers

This problem involves a company that only sells goods to customers that are registered

Two tables are concerned
1. reg_cust cust_id CHAR(6) PRIMARY KEY
2. sale_tran cust_id CHAR(6) REFERENCES reg_cust, sale_date DATE, inv_no INTEGER

A constraint must be applied so that before a record is appended to the sale_tran table a check is made to see if the customer is registered in the reg_cust table. If the customer is not registered then the sale is aborted.

One or more SQL statements need to be written to apply the constraint then to check that it is working

Has anyone got any ideas?? You don't need to do anything for this except create the foreign key constraint. At that point, they will only be able to enter a sale_tran if there is a reg_cust. There is no checking to do. It just works (unless of course you have certain flavors of mySQL).|||1 -- Create the reg_cust table --

CREATE TABLE reg_cust( cust_id CHAR(6) PRIMARY KEY);

2 -- Populate reg_cust --

INSERT INTO reg_cust VALUES('ABC123');
INSERT INTO reg_cust VALUES('DEF456');
INSERT INTO reg_cust VALUES('GHI123');
INSERT INTO reg_cust VALUES('JKL456');

3 -- Display the table --

SELECT * FROM reg_cust;

4 -- So far so good, so create the sale_tran table--

CREATE TABLE sale_tran (cust_id CHAR(6) REFERENCES reg_cust, sale_date DATE, inv_no INTEGER);

5 -- Populate sale_tran --

INSERT INTO sale_tran VALUES('DEF456', DATE('2004-06-15'), 200406123);
INSERT INTO sale_tran VALUES('GHI123', DATE('2004-06-15'), 200406124);
SELECT * FROM sale_tran;

6 -- This is the point where the constraint was requested --
The check could have been included at point 4 but what was needed was an alteration to an existing table

ALTER TABLE sale_tran
ADD CHECK (EXISTS(SELECT cust_id FROM reg_cust WHERE
cust_id = sale_tran.cust_id));

7 -- Alteration was successful - try an invalid entry --

INSERT INTO sale_tran VALUES('XYZ456', DATE('2004-06-15'), 200406125);

8 -- Constraint works, following message is given --

SQLSTATE 23000
[Sybase][ODBC Driver] Integrity constraint violation: Invalid value for column 'cust_id' in table 'sale_tran'

9 -- Try another valid value --

INSERT INTO sale_tran VALUES('JKL456', DATE('2004-06-15'), 200406126);
SELECT * FROM sale_tran;

10 -- Table is displayed as expected Task completed--