Tuesday, March 27, 2012
delete data
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
|||Try ON DELETE {NO ACTION and ON UPDATE {NO ACTION, this will not allow Deletes and Updates. Hope this helps.
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
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
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)
Wednesday, March 7, 2012
Deferring constraint checking in a transaction
Is there a way to defer constraint checking during a sql server database
data import transaction?
I can turn constraints off in sql with an alter table command, but this
requires adding the user to the db_ddladmin group which I'd rather they
didn't belong to.
Hi,
you can disable all / certain constraint defined on tables:
http://sqljunkies.com/WebLog/roman/a...1/30/7037.aspx
-- Disable all table constraints
ALTER TABLE MyTable NOCHECK CONSTRAINT ALL
-- Enable all table constraints
ALTER TABLE MyTable CHECK CONSTRAINT ALL
-- Disable single constraint
ALTER TABLE MyTable NOCHECK CONSTRAINT MyConstraint
-- Enable single constraint
ALTER TABLE MyTable CHECK CONSTRAINT MyConstraint
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
"shmeian" <shmeian@.discussions.microsoft.com> schrieb im Newsbeitrag
news:BC396313-25DB-45FA-A3D2-265109246024@.microsoft.com...
> Hello,
> Is there a way to defer constraint checking during a sql server database
> data import transaction?
> I can turn constraints off in sql with an alter table command, but this
> requires adding the user to the db_ddladmin group which I'd rather they
> didn't belong to.
>
Deferring constraint checking in a transaction
Is there a way to defer constraint checking during a sql server database
data import transaction?
I can turn constraints off in sql with an alter table command, but this
requires adding the user to the db_ddladmin group which I'd rather they
didn't belong to.Hi,
you can disable all / certain constraint defined on tables:
http://sqljunkies.com/WebLog/roman/archive/2005/01/30/7037.aspx
-- Disable all table constraints
ALTER TABLE MyTable NOCHECK CONSTRAINT ALL
-- Enable all table constraints
ALTER TABLE MyTable CHECK CONSTRAINT ALL
-- Disable single constraint
ALTER TABLE MyTable NOCHECK CONSTRAINT MyConstraint
-- Enable single constraint
ALTER TABLE MyTable CHECK CONSTRAINT MyConstraint
HTH, Jens Suessmeyer.
--
http://www.sqlserver2005.de
--
"shmeian" <shmeian@.discussions.microsoft.com> schrieb im Newsbeitrag
news:BC396313-25DB-45FA-A3D2-265109246024@.microsoft.com...
> Hello,
> Is there a way to defer constraint checking during a sql server database
> data import transaction?
> I can turn constraints off in sql with an alter table command, but this
> requires adding the user to the db_ddladmin group which I'd rather they
> didn't belong to.
>
Deferring constraint checking in a transaction
Is there a way to defer constraint checking during a sql server database
data import transaction?
I can turn constraints off in sql with an alter table command, but this
requires adding the user to the db_ddladmin group which I'd rather they
didn't belong to.Hi,
you can disable all / certain constraint defined on tables:
http://sqljunkies.com/WebLog/roman/...01/30/7037.aspx
-- Disable all table constraints
ALTER TABLE MyTable NOCHECK CONSTRAINT ALL
-- Enable all table constraints
ALTER TABLE MyTable CHECK CONSTRAINT ALL
-- Disable single constraint
ALTER TABLE MyTable NOCHECK CONSTRAINT MyConstraint
-- Enable single constraint
ALTER TABLE MyTable CHECK CONSTRAINT MyConstraint
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--
"shmeian" <shmeian@.discussions.microsoft.com> schrieb im Newsbeitrag
news:BC396313-25DB-45FA-A3D2-265109246024@.microsoft.com...
> Hello,
> Is there a way to defer constraint checking during a sql server database
> data import transaction?
> I can turn constraints off in sql with an alter table command, but this
> requires adding the user to the db_ddladmin group which I'd rather they
> didn't belong to.
>