Showing posts with label key. Show all posts
Showing posts with label key. Show all posts

Tuesday, March 27, 2012

delete data

Hi,

I have two tables.

CREATE TABLE [one] (
[roleno] [int] NOT NULL ,
[schno] [int] NULL ,
CONSTRAINT [PK_one] PRIMARY KEY CLUSTERED
(
[roleno]
) ON [PRIMARY] ,
CONSTRAINT [FK_one_two] FOREIGN KEY
(
[schno]
) REFERENCES [two] (
[schno]
)
) ON [PRIMARY]
GO

CREATE TABLE [two] (
[roleno] [int] NULL ,
[schno] [int] NOT NULL ,
CONSTRAINT [PK_two] PRIMARY KEY CLUSTERED
(
[schno]
) ON [PRIMARY] ,
CONSTRAINT [FK_two_one] FOREIGN KEY
(
[roleno]
) REFERENCES [one] (
[roleno]
)
) ON [PRIMARY]
GO

(I fact i created Primary & Foreign keys after inserting data in both of these tables.)

I want to delete data from these two tables.
How do i do that...Any Ideas?Originally posted by naveen_mehta
Hi,

I have two tables.

CREATE TABLE [one] (
[roleno] [int] NOT NULL ,
[schno] [int] NULL ,
CONSTRAINT [PK_one] PRIMARY KEY CLUSTERED
(
[roleno]
) ON [PRIMARY] ,
CONSTRAINT [FK_one_two] FOREIGN KEY
(
[schno]
) REFERENCES [two] (
[schno]
)
) ON [PRIMARY]
GO

CREATE TABLE [two] (
[roleno] [int] NULL ,
[schno] [int] NOT NULL ,
CONSTRAINT [PK_two] PRIMARY KEY CLUSTERED
(
[schno]
) ON [PRIMARY] ,
CONSTRAINT [FK_two_one] FOREIGN KEY
(
[roleno]
) REFERENCES [one] (
[roleno]
)
) ON [PRIMARY]
GO

(I fact i created Primary & Foreign keys after inserting data in both of these tables.)

I want to delete data from these two tables.
How do i do that...Any Ideas?

ALTER TABLE ONE NOCHECK CONSTRAINT FK_one_two
ALTER TABLE TWO NOCHECK CONSTRAINT FK_two_one
DELETE ONE
DELETE TWO
ALTER TABLE ONE CHECK CONSTRAINT FK_one_two
ALTER TABLE TWO CHECK CONSTRAINT FK_two_one|||That really works...Thanks a ton...|||You could similarly use the alter statements while inserting data if you do not want to check for constraints

delete constraint

how can i implement delete constraint? i mean i don't want the rows of the primary key table to be deleted if they are used as foreign key in some other table. so i want to check if that PK is used as foreign key in other tables before deleting.INSTEAD OF DELETE triggers|||so u mean using triggers instead of delete cascade? i can check if that record exists in other tables using triggers and take necessary action but i want to return relevant message to user if it couldn't be deleted. how can i do that?|||Have a look at RAISERROR|||i was able to manage the following code but couldn't get the message displayed when user tries to delete the record. if i run the stored proc from query analyzer, i get the following error message but not in my application. i have tried catching any exceptions using ex.Message but can't get the error. so how can i get the error displayed when user violates the delete constraint? i have used ExecuteNonQuery command.


IF EXISTS (SELECT id FROM A WHERE ID = @.ID)
BEGIN
RAISERROR ('Cannot delete this record. Make sure that this record is not used in other tables',9,1)
RETURN
END
|||Try ON DELETE {NO ACTION and ON UPDATE {NO ACTION, this will not allow Deletes and Updates. Hope this helps.

Kind regards,
Gift Peddie|||but i want the message to be returned if it couldn't be deleted so that relevant message can be displayed to the user.|||CREATE TABLE order_part
(order_nmbr int,
part_nmbr int
FOREIGN KEY REFERENCES part_sample(part_nmbr)
ON DELETE NO ACTION,
qty_ordered int)
GO

CREATE TABLE order_part
(order_nmbr int,
part_nmbr int
FOREIGN KEY REFERENCES part_sample(part_nmbr)
ON UPDATE NO ACTION,
qty_ordered int)
GO

Run a search in the BOL(books online) for Cascade Delete, the following code is from the BOL it means deletes or updates will fail with an error message. Hope this helps.

Kind regards,
Gift Peddie|||but how can we be sure about the source of error this way? with raiserror as i have done, i can get the message if there are other errors as well. else i don't get any error message. isn't that suupposed to return error message?|||The error from NO ACTION is ANSI SQL from DRI(Declarative Referential Integrity) rules but if you prefer Raise Error you can use it run a search for raise error in the BOL(books online). Hope this helps.

Kind regards,
Gift Peddie|||

I do it this way, for now. Probably better way would be to make Delete function that returns an error message rather then raising new exception. Maybe even make a some kind of class that interprets these error numbers and returns standard error message.

In SqlDataProvider for module

PublicOverridesSub DeleteClientDepartmentsItem(ByVal itemIDAsInteger)

Try

SqlHelper.ExecuteNonQuery(_connectionString, _databaseOwner & _objectQualifier & _

"esr_ClientDepartments_Delete", itemID)

Catch exAs SqlException

If ex.Number = 547Then

ThrowNew Exception("This record cannot be deleted due to its association with other records.")

EndIf

EndTry

EndSub

'in the page:

Try

Dim cdcAsNew ClientDepartmentsController

Dim departmentIdAsInteger =CType(dgDepartments.DataKeys(e.Item.ItemIndex),Integer)

cdc.Delete(departmentId)

BinddgDepartments()

Catch exAs Exception

DotNetNuke.UI.Skins.Skin.AddModuleMessage(Me, ex.Message, Skins.Controls.ModuleMessage.ModuleMessageType.YellowWarning)

EndTry

Delete Cluster Index With PK and FK Constraints

I have a SQL Server 2000 database that I would like to delete the Cluster
Index with a primary key. This primary key has a foreign key constraint.
Please help me create the syntax to delete the Cluster Index on Table A.
Thank You,
Table A
A1 PK (PK Constraint Name:A_A1)
A2
A3 (Non-Clustered Index Name: A_A3)
Table B
B1 PK
B2 FK (FK Constraint Name: B2_A_A1)
B3 (Non-Clustered Index Name: B_B3)
Hi,
You cannot remove Clustered index in your case as Primery Key is relying on
index and index is required for primery key.
All you can do is change clustered index to non-clustered or you can break
the relation and remove primery key to delete index.
Danijel Novak
"Joe K." <Joe K.@.discussions.microsoft.com> wrote in message
news:E7AF8E98-702E-4505-8B23-6282E615FD79@.microsoft.com...
> I have a SQL Server 2000 database that I would like to delete the Cluster
> Index with a primary key. This primary key has a foreign key constraint.
> Please help me create the syntax to delete the Cluster Index on Table A.
> Thank You,
> Table A
> A1 PK (PK Constraint Name:A_A1)
> A2
> A3 (Non-Clustered Index Name: A_A3)
> Table B
> B1 PK
> B2 FK (FK Constraint Name: B2_A_A1)
> B3 (Non-Clustered Index Name: B_B3)
|||As you have a PRIMARY KEY this is treated as a CONSTRAINT rather than an
INDEX.
The syntax is :-
ALTER TABLE table_name DROP CONSTRAINT constraint_name
Firstly you will need to DROP the FOREIGN KEY constraint on TableB, then
DROP the PK on TableA
HTH
"Joe K." <Joe K.@.discussions.microsoft.com> wrote in message
news:E7AF8E98-702E-4505-8B23-6282E615FD79@.microsoft.com...
> I have a SQL Server 2000 database that I would like to delete the Cluster
> Index with a primary key. This primary key has a foreign key constraint.
> Please help me create the syntax to delete the Cluster Index on Table A.
> Thank You,
> Table A
> A1 PK (PK Constraint Name:A_A1)
> A2
> A3 (Non-Clustered Index Name: A_A3)
> Table B
> B1 PK
> B2 FK (FK Constraint Name: B2_A_A1)
> B3 (Non-Clustered Index Name: B_B3)

Delete Cluster Index With PK and FK Constraints

I have a SQL Server 2000 database that I would like to delete the Cluster
Index with a primary key. This primary key has a foreign key constraint.
Please help me create the syntax to delete the Cluster Index on Table A.
Thank You,
Table A
A1 PK (PK Constraint Name:A_A1)
A2
A3 (Non-Clustered Index Name: A_A3)
Table B
B1 PK
B2 FK (FK Constraint Name: B2_A_A1)
B3 (Non-Clustered Index Name: B_B3)Hi,
You cannot remove Clustered index in your case as Primery Key is relying on
index and index is required for primery key.
All you can do is change clustered index to non-clustered or you can break
the relation and remove primery key to delete index.
--
Danijel Novak
"Joe K." <Joe K.@.discussions.microsoft.com> wrote in message
news:E7AF8E98-702E-4505-8B23-6282E615FD79@.microsoft.com...
> I have a SQL Server 2000 database that I would like to delete the Cluster
> Index with a primary key. This primary key has a foreign key constraint.
> Please help me create the syntax to delete the Cluster Index on Table A.
> Thank You,
> Table A
> A1 PK (PK Constraint Name:A_A1)
> A2
> A3 (Non-Clustered Index Name: A_A3)
> Table B
> B1 PK
> B2 FK (FK Constraint Name: B2_A_A1)
> B3 (Non-Clustered Index Name: B_B3)|||As you have a PRIMARY KEY this is treated as a CONSTRAINT rather than an
INDEX.
The syntax is :-
ALTER TABLE table_name DROP CONSTRAINT constraint_name
Firstly you will need to DROP the FOREIGN KEY constraint on TableB, then
DROP the PK on TableA
HTH
"Joe K." <Joe K.@.discussions.microsoft.com> wrote in message
news:E7AF8E98-702E-4505-8B23-6282E615FD79@.microsoft.com...
> I have a SQL Server 2000 database that I would like to delete the Cluster
> Index with a primary key. This primary key has a foreign key constraint.
> Please help me create the syntax to delete the Cluster Index on Table A.
> Thank You,
> Table A
> A1 PK (PK Constraint Name:A_A1)
> A2
> A3 (Non-Clustered Index Name: A_A3)
> Table B
> B1 PK
> B2 FK (FK Constraint Name: B2_A_A1)
> B3 (Non-Clustered Index Name: B_B3)

Delete Cluster Index With PK and FK Constraints

I have a SQL Server 2000 database that I would like to delete the Cluster
Index with a primary key. This primary key has a foreign key constraint.
Please help me create the syntax to delete the Cluster Index on Table A.
Thank You,
Table A
A1 PK (PK Constraint Name:A_A1)
A2
A3 (Non-Clustered Index Name: A_A3)
Table B
B1 PK
B2 FK (FK Constraint Name: B2_A_A1)
B3 (Non-Clustered Index Name: B_B3)Hi,
You cannot remove Clustered index in your case as Primery Key is relying on
index and index is required for primery key.
All you can do is change clustered index to non-clustered or you can break
the relation and remove primery key to delete index.
Danijel Novak
"Joe K." <Joe K.@.discussions.microsoft.com> wrote in message
news:E7AF8E98-702E-4505-8B23-6282E615FD79@.microsoft.com...
> I have a SQL Server 2000 database that I would like to delete the Cluster
> Index with a primary key. This primary key has a foreign key constraint.
> Please help me create the syntax to delete the Cluster Index on Table A.
> Thank You,
> Table A
> A1 PK (PK Constraint Name:A_A1)
> A2
> A3 (Non-Clustered Index Name: A_A3)
> Table B
> B1 PK
> B2 FK (FK Constraint Name: B2_A_A1)
> B3 (Non-Clustered Index Name: B_B3)|||As you have a PRIMARY KEY this is treated as a CONSTRAINT rather than an
INDEX.
The syntax is :-
ALTER TABLE table_name DROP CONSTRAINT constraint_name
Firstly you will need to DROP the FOREIGN KEY constraint on TableB, then
DROP the PK on TableA
HTH
"Joe K." <Joe K.@.discussions.microsoft.com> wrote in message
news:E7AF8E98-702E-4505-8B23-6282E615FD79@.microsoft.com...
> I have a SQL Server 2000 database that I would like to delete the Cluster
> Index with a primary key. This primary key has a foreign key constraint.
> Please help me create the syntax to delete the Cluster Index on Table A.
> Thank You,
> Table A
> A1 PK (PK Constraint Name:A_A1)
> A2
> A3 (Non-Clustered Index Name: A_A3)
> Table B
> B1 PK
> B2 FK (FK Constraint Name: B2_A_A1)
> B3 (Non-Clustered Index Name: B_B3)

Delete any records in table 1 that are not in table 2 (tables joined on 2 columns)

I have two tables. The primary key for both tables is comprised of 2 columns.

How do I delete any records in table 1 that are not in table 2.

e.g.

if this was the data in the 2 tables

Table 1

col1 col2 col 3

0 0 0

0 1 0

Table 2

col1 col2 col3

0 0 0

I would like to delete the records containg the values 0, 1, 0 from table 1 because the is no record in table 2 with the values 0, 1 as the primary key.

Hi,

You can do this as follow (by suggesting that COL1 and COL2 are the key columns):

DELETE FROM table1
FROM table1
LEFT OUTER JOIN table2
ON table1.COL1 = table2.col1
AND table1.COL2 = table2.col2
WHERE table2.COL1 IS NULL

Greetz,

Geert

Geert Verhoeven
Consultant @. Ausy Belgium

My Personal Blog

|||Thanks a million. It seems strange to me the way the From caluse is included twice but it works!|||

the following query will do..

Delete from table1 Where Not Exists(Select 1 From table2 Where table1.Col1=Table2.Col1 and Table1.Col2=Table2.Col2)

Sunday, March 25, 2012

delete all data and restart key count to 0

How do I clear all the entrys from a table and make the keys start all overIf u need the current records again u can copy the records to some new table drop the identity column and create it again with the proper seed and import the data back from the temp table.|||truncate table will remove all data and reset the IDENTITY counter to the original seed

Delete across tables

hi,

I have two tables that are related, ie I created them with;

create table cm_message (
msgid varchar(40) not null primary key,
location varchar(240),
ts timestamp default 'now' not null,
lastsent timestamp
);

create table cm_data (
pkey integer not null primary key,
subdata varchar(255),
msgid varchar(40),
foreign key (msgid) references cm_message(msgid)
);

basically for each entry in cm_message there can be several cm_data entries and they're linked using the msgid fields.

I'm trying to write a purge script that will delete entries (in cm_message and cm_data) that have a cm_message.ts timestamp older than n hours. Can I do a 'delete from ... where cm_message.ts > n' which does some kind of union between the two tables and delete entries from both tables at one stroke?

At the moment I'm looking at selecting all old entries from cm_message and deleting all in cm_data for each msgid, but there must be a more efficient way of using the relational stuff...

thanks,
nikUse the ON DELETE CASCADE option:
...foreign key (msgid) references cm_message(msgid) ON DELETE CASCADE :rolleyes:|||which database system is this? because i don't know of any that will support this --timestamp default 'now'|||thanks LKBrwn_DBA.

rudy, the database is firebird - I think it also accepts TODAY, TOMORROW and YESTERDAY which is nice and handy...

nik|||wow, ya learn sumpin new every day ;)

thanks nik|||Hi,

Does it work on a MySQL database?

//M|||Does it work on a MySQL database?the ON DELETE CASCADE? only for InnoDB tables

delete a primary key with T-SQL

Does anyone know how to delete a primary key with T-SQL?
triocchuReplied in microsoft.public.sqlserver.programming.
Please don't multi-post.
--
David Portas
--
Please reply only to the newsgroup
--
"triocchu" <triocchu@.netvigator.com> wrote in message
news:brmna2$4gq28@.imsp212.netvigator.com...
> Does anyone know how to delete a primary key with T-SQL?
> triocchu
>|||triocchu,
ALTER TABLE tablename DROP CONSTRAINT PrimaryKeyName
---
PETER WARD
WARDY Inc. - www.wardyinc.com
SQL Server Solutions
---
"triocchu" <triocchu@.netvigator.com> wrote in message
news:brmna2$4gq28@.imsp212.netvigator.com...
> Does anyone know how to delete a primary key with T-SQL?
> triocchu
>

delete a primary key with T-SQL

Does anyone know how to delete a primary key with T-SQL?

triocchuReplied in microsoft.public.sqlserver.programming.
Please don't multi-post.

--
David Portas
----
Please reply only to the newsgroup
--

"triocchu" <triocchu@.netvigator.com> wrote in message
news:brmnd9$4gd100@.imsp212.netvigator.com...
> Does anyone know how to delete a primary key with T-SQL?
> triocchu

Thursday, March 22, 2012

Delete

Hey,
I got a table in which i want to delete a few rows. The
table has a primary key and withing the coloums it has
an "instanceid" which is pointed to another table
called "instance" and that table has many pk and fk. When
i try to delete a row from the 1 table mentioned, i get
the error:
Delete statement conflicted with COLUMN REFERENCE
constraint XXXX. The conflict occured in database yyyy,
table 1111, column 3333. The statement has been terminated.
Any ideas for a work arround?Essentially you are try to delete a parent record that has child records.
The FK is preventing you as it would cause orpahns.
You will need to delete all the child records before deleting the parent.
--
HTH
Ryan Waight, MCDBA, MCSE
"nefasha" <anonymous@.discussions.microsoft.com> wrote in message
news:081101c3a78e$7e9b2fc0$a601280a@.phx.gbl...
> Hey,
> I got a table in which i want to delete a few rows. The
> table has a primary key and withing the coloums it has
> an "instanceid" which is pointed to another table
> called "instance" and that table has many pk and fk. When
> i try to delete a row from the 1 table mentioned, i get
> the error:
> Delete statement conflicted with COLUMN REFERENCE
> constraint XXXX. The conflict occured in database yyyy,
> table 1111, column 3333. The statement has been terminated.
> Any ideas for a work arround?|||But it isnt a parent id!!! The table X has a column
instanceid which points to a table y where THAT table has
PK and FK with OTHER tables using the instance id... i
dont see why that affects table X?
>--Original Message--
>Essentially you are try to delete a parent record that
has child records.
>The FK is preventing you as it would cause orpahns.
>You will need to delete all the child records before
deleting the parent.
>--
>HTH
>Ryan Waight, MCDBA, MCSE
>"nefasha" <anonymous@.discussions.microsoft.com> wrote in
message
>news:081101c3a78e$7e9b2fc0$a601280a@.phx.gbl...
>> Hey,
>> I got a table in which i want to delete a few rows. The
>> table has a primary key and withing the coloums it has
>> an "instanceid" which is pointed to another table
>> called "instance" and that table has many pk and fk.
When
>> i try to delete a row from the 1 table mentioned, i get
>> the error:
>> Delete statement conflicted with COLUMN REFERENCE
>> constraint XXXX. The conflict occured in database yyyy,
>> table 1111, column 3333. The statement has been
terminated.
>> Any ideas for a work arround?
>
>.
>|||Nevermind!!!1 I figured what i was doing wrong!!! thanxs
for the help anyways!
>--Original Message--
>But it isnt a parent id!!! The table X has a column
>instanceid which points to a table y where THAT table has
>PK and FK with OTHER tables using the instance id... i
>dont see why that affects table X?
>>--Original Message--
>>Essentially you are try to delete a parent record that
>has child records.
>>The FK is preventing you as it would cause orpahns.
>>You will need to delete all the child records before
>deleting the parent.
>>--
>>HTH
>>Ryan Waight, MCDBA, MCSE
>>"nefasha" <anonymous@.discussions.microsoft.com> wrote in
>message
>>news:081101c3a78e$7e9b2fc0$a601280a@.phx.gbl...
>> Hey,
>> I got a table in which i want to delete a few rows. The
>> table has a primary key and withing the coloums it has
>> an "instanceid" which is pointed to another table
>> called "instance" and that table has many pk and fk.
>When
>> i try to delete a row from the 1 table mentioned, i get
>> the error:
>> Delete statement conflicted with COLUMN REFERENCE
>> constraint XXXX. The conflict occured in database yyyy,
>> table 1111, column 3333. The statement has been
>terminated.
>> Any ideas for a work arround?
>>
>>.
>.
>

Wednesday, March 21, 2012

Delay affecting key violation?!

Hello!
I'm having a little problem with SQL Server (this doesn't happen in Access):
Using ADO:
I begin a transaction
I insert a row into table X, but the primary key already exists so(an error
should be raised immediately)
I insert a related row into a related table X.
I do some more things
Hey! Error?! Key violation?! Where? At table X (my first insert)...
uhhhhhhhhh!!!
Is there any way to make SQL Server send an error immediately if the insert
violate the primary key?
I just don't get this :(
Thanks for all your help.
Jorge Carvalho
rdc02271@.yahoo.comset xact_abort on
"Jorge C" wrote:

> Hello!
> I'm having a little problem with SQL Server (this doesn't happen in Access
):
> Using ADO:
> I begin a transaction
> I insert a row into table X, but the primary key already exists so(an erro
r
> should be raised immediately)
> I insert a related row into a related table X.
> I do some more things
> Hey! Error?! Key violation?! Where? At table X (my first insert)...
> uhhhhhhhhh!!!
> Is there any way to make SQL Server send an error immediately if the inser
t
> violate the primary key?
> I just don't get this :(
> Thanks for all your help.
> Jorge Carvalho
> rdc02271@.yahoo.com
>
>|||Thanks but little problem:
If I set transaction mode to explicit the entire transaction will be rolled
back, neglecting my commit or rollback statement.
So what's the point of having explicit transaction mode when you are unable
to: control the error the minute it happens + make necessary changes and try
again without rolling back the transaction?
Thanks.
Jorge Carvalho
rdc02271@.yahoo.com
"Ray D" <RayD@.discussions.microsoft.com> escreveu na mensagem
news:F78827F1-F1BC-4ADF-B25A-F517529B9C12@.microsoft.com...
> set xact_abort on
> "Jorge C" wrote:
>
>|||You are correct that an error should be raised as soon as you insert a
row that violates a constraint. However, what you haven't told us is
the exact "key violation" error message you are getting? Was it the
foreign key or primary key constraint? It seems like the second insert
caused the error so maybe the message is "INSERT statement conflicted
with COLUMN FOREIGN KEY constraint", which might explain why you see it
as an error "at table X".
Also, check if there are any triggers on these tables. Maybe a trigger
is at fault.
David Portas
SQL Server MVP
--|||Hi!
The primary key at the first table is being violated.
Our scheme is like this: (if you have a better system please share :) )
1)The user fills out an invoice (header+details+some other info)
2)Then we try to save the invoice using the latest invoice number+1;but
since we have more one application doing the same with the same database
(multi-user) the insert for the first table may fail (invoice header). So we
need to change the invoice number and try again.
The primary key violation message refers to this first operation.
Thanks.
Jorge Carvalho
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> escreveu na mensagem
news:1112358100.042970.307850@.z14g2000cwz.googlegroups.com...
> You are correct that an error should be raised as soon as you insert a
> row that violates a constraint. However, what you haven't told us is
> the exact "key violation" error message you are getting? Was it the
> foreign key or primary key constraint? It seems like the second insert
> caused the error so maybe the message is "INSERT statement conflicted
> with COLUMN FOREIGN KEY constraint", which might explain why you see it
> as an error "at table X".
> Also, check if there are any triggers on these tables. Maybe a trigger
> is at fault.
> --
> David Portas
> SQL Server MVP
> --
>|||That isn't an efficient strategy for generating an incrementing key. Also,
as a matter of design principle it's not a good idea to build a system that
generates errors as part of its correct behaviour.
Max invoice number + 1:
INSERT INTO Invoices (invoice_num,... /* other columns */)
SELECT COALESCE(MAX(invoice_num),0)+1, ... /* other data */
FROM Invoices
If it isn't a problem to have gaps in the sequence of invoice numbers then
you might also consider using the IDENTITY property to generate the
sequence.
Don't forget to declare your table's natural key. Maybe (customer,
invoice_date) for example.
David Portas
SQL Server MVP
--

Friday, March 9, 2012

Defining the Scope of SCOPE_IDENTITY

A couple of Web applications in different SQL Server 2000 databases use SCOPE_IDENTITY to retrieve the key value of a record that was just inserted. It works--most of the time. However, from time to time the identity value is not retrieved. Evidence suggests that in these cases, a null value is being retrieved. This has forced me to come up with less-than-ideal workarounds for the missing identity value.

Does anyone have any idea why SCOPE_IDENTITY sometimes fails to retrieve the identity value and transmit it back to the Web page? Could a network issue cause the problem? Is there anything I can do other than rewrite the apps to use a different algorithm than using SCOPE_IDENTITY? Thanks.

I am not aware of any issues with SCOPE_IDENTITY(); this might be an application / connection issue and not a problem with SCOPE_IDENTITY(). I am certainly interested in the outcome of this. Can somebody please check me on this?|||

If you are using embedded SQL in your application it might be worth placing this logic into a stored procedure and calling that from your application. That should avoid any comms problems as the procedure will run or not run as a single call (and not have a problem between statements in the operation).

|||

Yes, the web app uses embedded SQL in classic ASP. The application was written in classic ASP and there has never been a good reason to rewrite it. The web app is the only application that performs DML on the table--there are no separate triggers or other ways into the table.

How could an embedded SQL statement in a single Web page cause scope problems? One Web page consulted during the research on this problem said this situation should be treated as a single scope.

I will probably try the stored procedure method. But I am curious as to why all sources practically demand that SCOPE_IDENTITY be used within a stored procedure when it is allowed to work in other situations.

Thanks for the input.

Defining Foreign keys in Management Studio

Hi,
I am using SQL Server 2005.
I have created the tables for my Database through the Management Studio
front end tool.
I can apply a primary key to the tables easily enough. What I am having
trouble with is assigning multiple columns as primary keys and also how to
define foreign keys between tables.
Does anyone have suggesstions how I can achieve this through management
studio?
Thanks In Advance
MaccaXref: TK2MSFTNGP01.phx.gbl microsoft.public.sqlserver.server:433758
On Wed, 10 May 2006 07:56:02 -0700, Macca wrote:

>Hi,
>I am using SQL Server 2005.
>I have created the tables for my Database through the Management Studio
>front end tool.
>I can apply a primary key to the tables easily enough. What I am having
>trouble with is assigning multiple columns as primary keys
Hi Macca,
Easiest: open a new query window, type
ALTER TABLE MyTable
ADD CONSTRAINT PK_MyTable -- Or any other name
PRIMARY KEY (Col1, Col2, Col3)
Then, click the Execute button.
But if you prefer to use point and cllick, just hold down the
Ctrl-button on your keyboard while selecting key columns, than
right-click and choose "Set Primary Key".

> and also how to
>define foreign keys between tables.
Again, the easiest is to just type and execute the SQL command:
ALTER TABLE ReferingTable
ADD CONSTRAINT LogicalNameGoesHere
FOREIGN KEY (Col1, Col2, Col3)
REFERENCES ReferedTable (Col1, Col2, Col3)
Optionally, add an ON UPDATE and/or ON DELETE clause.
Using point and click: rightclick refering table and choose "Modify".
Click menu-item "Table Designer" / "Relationships". Click "Add". Under
"(General)", find the entry for "Tables and Columns Specification",
click in the emppty field next to it, then click the smalll ellipsis
button. Enter a name for the foreign key constraint. Then, on the right
hand side, use the drop down lists to select the column(s) that form the
relationship. Move to the left-hand side, choose the refered table and
choose the columns that are refered to. Click "OK" to save.
Regardless of whether you use SQL or point and click to set the
relationship, the column(s) in the refered table MUST be set as either a
PRIMARY KEY or a UNIQUE constraint.
Hugo Kornelis, SQL Server MVP|||Hi Hugo,
Thanks for the reply. I have a question with the SQl that creates a foreign
key.
If I have Table1 and Table 2 and Table 1 has a foreign key which is the
Primary key of Table 2. Is Table 1 or Table 2 the referring table in your SQ
L
query?
Thanks
Macca
"Hugo Kornelis" wrote:

> On Wed, 10 May 2006 07:56:02 -0700, Macca wrote:
>
> Hi Macca,
> Easiest: open a new query window, type
> ALTER TABLE MyTable
> ADD CONSTRAINT PK_MyTable -- Or any other name
> PRIMARY KEY (Col1, Col2, Col3)
> Then, click the Execute button.
> But if you prefer to use point and cllick, just hold down the
> Ctrl-button on your keyboard while selecting key columns, than
> right-click and choose "Set Primary Key".
>
> Again, the easiest is to just type and execute the SQL command:
> ALTER TABLE ReferingTable
> ADD CONSTRAINT LogicalNameGoesHere
> FOREIGN KEY (Col1, Col2, Col3)
> REFERENCES ReferedTable (Col1, Col2, Col3)
> Optionally, add an ON UPDATE and/or ON DELETE clause.
> Using point and click: rightclick refering table and choose "Modify".
> Click menu-item "Table Designer" / "Relationships". Click "Add". Under
> "(General)", find the entry for "Tables and Columns Specification",
> click in the emppty field next to it, then click the smalll ellipsis
> button. Enter a name for the foreign key constraint. Then, on the right
> hand side, use the drop down lists to select the column(s) that form the
> relationship. Move to the left-hand side, choose the refered table and
> choose the columns that are refered to. Click "OK" to save.
> Regardless of whether you use SQL or point and click to set the
> relationship, the column(s) in the refered table MUST be set as either a
> PRIMARY KEY or a UNIQUE constraint.
> --
> Hugo Kornelis, SQL Server MVP
>|||> If I have Table1 and Table 2 and Table 1 has a foreign key which is the
> Primary key of Table 2. Is Table 1 or Table 2 the referring table in your
SQL
> query?
It is not the query that describes which table is the referencing or the ref
erenced table. It is the
data model. In the data model you describe, Table2 is the referenced table a
nd Table 1 is the
referencing table.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Macca" <Macca@.discussions.microsoft.com> wrote in message
news:F44BB119-F24C-4146-91CB-0586510C1589@.microsoft.com...[vbcol=seagreen]
> Hi Hugo,
> Thanks for the reply. I have a question with the SQl that creates a foreig
n
> key.
> If I have Table1 and Table 2 and Table 1 has a foreign key which is the
> Primary key of Table 2. Is Table 1 or Table 2 the referring table in your
SQL
> query?
> Thanks
> Macca
> "Hugo Kornelis" wrote:
>

Defining Foreign keys in Management Studio

Hi,
I am using SQL Server 2005.
I have created the tables for my Database through the Management Studio
front end tool.
I can apply a primary key to the tables easily enough. What I am having
trouble with is assigning multiple columns as primary keys and also how to
define foreign keys between tables.
Does anyone have suggesstions how I can achieve this through management
studio?
Thanks In Advance
MaccaOn Wed, 10 May 2006 07:56:02 -0700, Macca wrote:
>Hi,
>I am using SQL Server 2005.
>I have created the tables for my Database through the Management Studio
>front end tool.
>I can apply a primary key to the tables easily enough. What I am having
>trouble with is assigning multiple columns as primary keys
Hi Macca,
Easiest: open a new query window, type
ALTER TABLE MyTable
ADD CONSTRAINT PK_MyTable -- Or any other name
PRIMARY KEY (Col1, Col2, Col3)
Then, click the Execute button.
But if you prefer to use point and cllick, just hold down the
Ctrl-button on your keyboard while selecting key columns, than
right-click and choose "Set Primary Key".
> and also how to
>define foreign keys between tables.
Again, the easiest is to just type and execute the SQL command:
ALTER TABLE ReferingTable
ADD CONSTRAINT LogicalNameGoesHere
FOREIGN KEY (Col1, Col2, Col3)
REFERENCES ReferedTable (Col1, Col2, Col3)
Optionally, add an ON UPDATE and/or ON DELETE clause.
Using point and click: rightclick refering table and choose "Modify".
Click menu-item "Table Designer" / "Relationships". Click "Add". Under
"(General)", find the entry for "Tables and Columns Specification",
click in the emppty field next to it, then click the smalll ellipsis
button. Enter a name for the foreign key constraint. Then, on the right
hand side, use the drop down lists to select the column(s) that form the
relationship. Move to the left-hand side, choose the refered table and
choose the columns that are refered to. Click "OK" to save.
Regardless of whether you use SQL or point and click to set the
relationship, the column(s) in the refered table MUST be set as either a
PRIMARY KEY or a UNIQUE constraint.
--
Hugo Kornelis, SQL Server MVP|||Hi Hugo,
Thanks for the reply. I have a question with the SQl that creates a foreign
key.
If I have Table1 and Table 2 and Table 1 has a foreign key which is the
Primary key of Table 2. Is Table 1 or Table 2 the referring table in your SQL
query?
Thanks
Macca
"Hugo Kornelis" wrote:
> On Wed, 10 May 2006 07:56:02 -0700, Macca wrote:
> >Hi,
> >
> >I am using SQL Server 2005.
> >
> >I have created the tables for my Database through the Management Studio
> >front end tool.
> >
> >I can apply a primary key to the tables easily enough. What I am having
> >trouble with is assigning multiple columns as primary keys
> Hi Macca,
> Easiest: open a new query window, type
> ALTER TABLE MyTable
> ADD CONSTRAINT PK_MyTable -- Or any other name
> PRIMARY KEY (Col1, Col2, Col3)
> Then, click the Execute button.
> But if you prefer to use point and cllick, just hold down the
> Ctrl-button on your keyboard while selecting key columns, than
> right-click and choose "Set Primary Key".
> > and also how to
> >define foreign keys between tables.
> Again, the easiest is to just type and execute the SQL command:
> ALTER TABLE ReferingTable
> ADD CONSTRAINT LogicalNameGoesHere
> FOREIGN KEY (Col1, Col2, Col3)
> REFERENCES ReferedTable (Col1, Col2, Col3)
> Optionally, add an ON UPDATE and/or ON DELETE clause.
> Using point and click: rightclick refering table and choose "Modify".
> Click menu-item "Table Designer" / "Relationships". Click "Add". Under
> "(General)", find the entry for "Tables and Columns Specification",
> click in the emppty field next to it, then click the smalll ellipsis
> button. Enter a name for the foreign key constraint. Then, on the right
> hand side, use the drop down lists to select the column(s) that form the
> relationship. Move to the left-hand side, choose the refered table and
> choose the columns that are refered to. Click "OK" to save.
> Regardless of whether you use SQL or point and click to set the
> relationship, the column(s) in the refered table MUST be set as either a
> PRIMARY KEY or a UNIQUE constraint.
> --
> Hugo Kornelis, SQL Server MVP
>|||> If I have Table1 and Table 2 and Table 1 has a foreign key which is the
> Primary key of Table 2. Is Table 1 or Table 2 the referring table in your SQL
> query?
It is not the query that describes which table is the referencing or the referenced table. It is the
data model. In the data model you describe, Table2 is the referenced table and Table 1 is the
referencing table.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Macca" <Macca@.discussions.microsoft.com> wrote in message
news:F44BB119-F24C-4146-91CB-0586510C1589@.microsoft.com...
> Hi Hugo,
> Thanks for the reply. I have a question with the SQl that creates a foreign
> key.
> If I have Table1 and Table 2 and Table 1 has a foreign key which is the
> Primary key of Table 2. Is Table 1 or Table 2 the referring table in your SQL
> query?
> Thanks
> Macca
> "Hugo Kornelis" wrote:
>> On Wed, 10 May 2006 07:56:02 -0700, Macca wrote:
>> >Hi,
>> >
>> >I am using SQL Server 2005.
>> >
>> >I have created the tables for my Database through the Management Studio
>> >front end tool.
>> >
>> >I can apply a primary key to the tables easily enough. What I am having
>> >trouble with is assigning multiple columns as primary keys
>> Hi Macca,
>> Easiest: open a new query window, type
>> ALTER TABLE MyTable
>> ADD CONSTRAINT PK_MyTable -- Or any other name
>> PRIMARY KEY (Col1, Col2, Col3)
>> Then, click the Execute button.
>> But if you prefer to use point and cllick, just hold down the
>> Ctrl-button on your keyboard while selecting key columns, than
>> right-click and choose "Set Primary Key".
>> > and also how to
>> >define foreign keys between tables.
>> Again, the easiest is to just type and execute the SQL command:
>> ALTER TABLE ReferingTable
>> ADD CONSTRAINT LogicalNameGoesHere
>> FOREIGN KEY (Col1, Col2, Col3)
>> REFERENCES ReferedTable (Col1, Col2, Col3)
>> Optionally, add an ON UPDATE and/or ON DELETE clause.
>> Using point and click: rightclick refering table and choose "Modify".
>> Click menu-item "Table Designer" / "Relationships". Click "Add". Under
>> "(General)", find the entry for "Tables and Columns Specification",
>> click in the emppty field next to it, then click the smalll ellipsis
>> button. Enter a name for the foreign key constraint. Then, on the right
>> hand side, use the drop down lists to select the column(s) that form the
>> relationship. Move to the left-hand side, choose the refered table and
>> choose the columns that are refered to. Click "OK" to save.
>> Regardless of whether you use SQL or point and click to set the
>> relationship, the column(s) in the refered table MUST be set as either a
>> PRIMARY KEY or a UNIQUE constraint.
>> --
>> Hugo Kornelis, SQL Server MVP

Wednesday, March 7, 2012

Defining a clustered key

SQL server 2000: I'm told I need to define a clustered key on an existing table Scores for the rows absID and revID.
what is the syntax for this? I'm looking online but not finding it.
thanks.
DaveYour best bet is to open up Query Analyzer, then hit F1.

Then search for "clustered index key"

BOL is your FRIEND.|||Do it through Enterprise Manager.

Right-click on the table and select "Design Table".
From the menu-bar, click the icon for "Manager Indexes/Keys" (second from right, normally).
Click "New" to create a new index. Select the column(s) you want to include, and check off the box for "Create as clustered". Save and exit.

Who told you the table needed this clustered index? Does the table have a primary key? (Normally the primary key is the clustered index.)

define unique keys

I have a primary key (column name is emp_id) in employee table. Also,
I would like to make a combination of other two columns is unique.
(combination of officecode field and claimno field must be unique).
how can I implement this uniquess in ms sql 2000? thank you.On 17 Oct 2007 13:41:28 -0700, TGEAR wrote:

Quote:

Originally Posted by

>I have a primary key (column name is emp_id) in employee table. Also,
>I would like to make a combination of other two columns is unique.
>(combination of officecode field and claimno field must be unique).
>how can I implement this uniquess in ms sql 2000? thank you.


Hi TGEAR,

CREATE TABLE Demo
(PrimaryKeyColumn int NOT NULL
,HalfOfOtherKeyColumn int NOT NULL
,OtherHalfOfOtherKey int NOT NULL
,SomeOhterKeyForFun int NOT NULL
,CONSTRAINT pk_Demo PRIMARY KEY (PrimaryKeyColumn)
,CONSTRAINT uq_Demo UNIQUE (HalfOfOtherKeyColumn, OtherHalfOfOtherKey)
);

--
Hugo Kornelis, SQL Server MVP
My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis|||TGEAR wrote:

Quote:

Originally Posted by

I have a primary key (column name is emp_id) in employee table. Also,
I would like to make a combination of other two columns is unique.
(combination of officecode field and claimno field must be unique).
how can I implement this uniquess in ms sql 2000? thank you.


alter table employee
add constraint officecode_claimno
unique (officecode, claimno)

Define FK over DB boundaries

Hi,
is it possible to assign a foreign key from DB1 to a primary key from DB2?
Thanks
Christian
Nope, it's not supported. You'd have to do it with triggers.
HTH. Ryan
"Christian Havel" <ChristianHavel@.discussions.microsoft.com> wrote in
message news:0FB2E334-A203-4BAF-BDA1-71A4462E8841@.microsoft.com...
> Hi,
> is it possible to assign a foreign key from DB1 to a primary key from DB2?
> Thanks
> Christian
|||No, you have to implemt that with triggers.
BTW: Do not cross(-language) post.
HTH, jens Suessmeyer.
|||Hi
You could add a check constraint that calls a function, but this is likely
to be very slow.
John
"Christian Havel" wrote:

> Hi,
> is it possible to assign a foreign key from DB1 to a primary key from DB2?
> Thanks
> Christian
|||> You could add a check constraint that calls a function, but this is likely
> to be very slow.
Careful with this - real DRI works in two directions. This would only work
in one direction and only for inserts and updates.
|||Scott
Both the trigger solution and the check constraint require changes to both
databases if you want to cover everything.
John
"Scott Morris" wrote:

> Careful with this - real DRI works in two directions. This would only work
> in one direction and only for inserts and updates.
>
>
|||> Both the trigger solution and the check constraint require changes to both
> databases if you want to cover everything.
Nothing I wrote disagreed with this. But, as I mentioned, a check
constraint is not checked during deletion. A set of triggers, when properly
designed and written, can enforce the required relationship. As far as I
know, check constraints cannot wholely support the requirement.

Define FK over DB boundaries

Hi,
is it possible to assign a foreign key from DB1 to a primary key from DB2?
Thanks
ChristianNope, it's not supported. You'd have to do it with triggers.
HTH. Ryan
"Christian Havel" <ChristianHavel@.discussions.microsoft.com> wrote in
message news:0FB2E334-A203-4BAF-BDA1-71A4462E8841@.microsoft.com...
> Hi,
> is it possible to assign a foreign key from DB1 to a primary key from DB2?
> Thanks
> Christian|||No, you have to implemt that with triggers.
BTW: Do not cross(-language) post.
HTH, jens Suessmeyer.|||Hi
You could add a check constraint that calls a function, but this is likely
to be very slow.
John
"Christian Havel" wrote:

> Hi,
> is it possible to assign a foreign key from DB1 to a primary key from DB2?
> Thanks
> Christian|||> You could add a check constraint that calls a function, but this is likely
> to be very slow.
Careful with this - real DRI works in two directions. This would only work
in one direction and only for inserts and updates.|||Scott
Both the trigger solution and the check constraint require changes to both
databases if you want to cover everything.
John
"Scott Morris" wrote:

> Careful with this - real DRI works in two directions. This would only wor
k
> in one direction and only for inserts and updates.
>
>|||> Both the trigger solution and the check constraint require changes to both
> databases if you want to cover everything.
Nothing I wrote disagreed with this. But, as I mentioned, a check
constraint is not checked during deletion. A set of triggers, when properly
designed and written, can enforce the required relationship. As far as I
know, check constraints cannot wholely support the requirement.

Define FK over DB boundaries

Hi,
is it possible to assign a foreign key from DB1 to a primary key from DB2?
Thanks
ChristianNope, it's not supported. You'd have to do it with triggers.
--
HTH. Ryan
"Christian Havel" <ChristianHavel@.discussions.microsoft.com> wrote in
message news:0FB2E334-A203-4BAF-BDA1-71A4462E8841@.microsoft.com...
> Hi,
> is it possible to assign a foreign key from DB1 to a primary key from DB2?
> Thanks
> Christian|||No, you have to implemt that with triggers.
BTW: Do not cross(-language) post.
HTH, jens Suessmeyer.|||Hi
You could add a check constraint that calls a function, but this is likely
to be very slow.
John
"Christian Havel" wrote:
> Hi,
> is it possible to assign a foreign key from DB1 to a primary key from DB2?
> Thanks
> Christian|||> You could add a check constraint that calls a function, but this is likely
> to be very slow.
Careful with this - real DRI works in two directions. This would only work
in one direction and only for inserts and updates.|||Scott
Both the trigger solution and the check constraint require changes to both
databases if you want to cover everything.
John
"Scott Morris" wrote:
> > You could add a check constraint that calls a function, but this is likely
> > to be very slow.
> Careful with this - real DRI works in two directions. This would only work
> in one direction and only for inserts and updates.
>
>|||> Both the trigger solution and the check constraint require changes to both
> databases if you want to cover everything.
Nothing I wrote disagreed with this. But, as I mentioned, a check
constraint is not checked during deletion. A set of triggers, when properly
designed and written, can enforce the required relationship. As far as I
know, check constraints cannot wholely support the requirement.