SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Table constraints


Table constraints

Author
Message
mfayard
mfayard
Forum Newbie
Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)

Group: General Forum Members
Points: 4 Visits: 13
I am trying to update records in a table that had a check constraint to set the table to read only. That constraint was removed (dropped) and it seems that now I can't update any record that was put in while the constraint was on the table. I can modify any record that is written to the table after the constraint was removed. Let me further clarify this. I can go into Mgmt studio and update any record in this table, but using a program, written in Delphi, using dataset components....it won't work on any record that existed prior to the constraint being removed. I am wondering if there is anything that is lingering on the pre-removal records?
Brian O'Leary
Brian O'Leary
SSC-Enthusiastic
SSC-Enthusiastic (137 reputation)SSC-Enthusiastic (137 reputation)SSC-Enthusiastic (137 reputation)SSC-Enthusiastic (137 reputation)SSC-Enthusiastic (137 reputation)SSC-Enthusiastic (137 reputation)SSC-Enthusiastic (137 reputation)SSC-Enthusiastic (137 reputation)

Group: General Forum Members
Points: 137 Visits: 116
mfayard (9/13/2010)
I am trying to update records in a table that had a check constraint to set the table to read only. That constraint was removed (dropped) and it seems that now I can't update any record that was put in while the constraint was on the table. I can modify any record that is written to the table after the constraint was removed. Let me further clarify this. I can go into Mgmt studio and update any record in this table, but using a program, written in Delphi, using dataset components....it won't work on any record that existed prior to the constraint being removed. I am wondering if there is anything that is lingering on the pre-removal records?


Hi you say the table was read only, but then you you say you cant update any records that were added while the table was read only (how did you rows get there?). How was your check constraint written?

MCITP SQL Server 2005/2008 DBA/DBD
mfayard
mfayard
Forum Newbie
Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)

Group: General Forum Members
Points: 4 Visits: 13
The process that used the table would drop the constraint, then add the records needed, then add the constraint back. Following is the code to add the constraint and to drop it.

Drop Constraint -

ALTER TABLE [dbo].[BranchFundType] WITH NOCHECK ADD CONSTRAINT [chk_read_only] CHECK (((1)=(0)))
ALTER TABLE [dbo].[BranchFundType] CHECK CONSTRAINT [chk_read_only]Show Bart Talley (dev1) added a comment - 11/Aug/10 11:22 AM Constraint has been removed see code below. --remove
IF EXISTS (SELECT * FROM sys.check_constraints WHERE object_id = OBJECT_ID(N'[dbo].[chk_read_only]') AND parent_object_id = OBJECT_ID(N'[dbo].[BranchFundType]'))
ALTER TABLE [dbo].[BranchFundType] DROP CONSTRAINT [chk_read_only]

-- add constraint
ALTER TABLE [dbo].[BranchFundType] WITH NOCHECK ADD CONSTRAINT [chk_read_only] CHECK (((1)=(0)))
ALTER TABLE [dbo].[BranchFundType] CHECK CONSTRAINT [chk_read_only]
Lowell
Lowell
SSC Guru
SSC Guru (70K reputation)SSC Guru (70K reputation)SSC Guru (70K reputation)SSC Guru (70K reputation)SSC Guru (70K reputation)SSC Guru (70K reputation)SSC Guru (70K reputation)SSC Guru (70K reputation)

Group: General Forum Members
Points: 70256 Visits: 40923
interesting use of a check constraint to make a table read only;

could there be a trigger on the table that is also preventing your modifications? depending on the loging in such a trigger, it might explain your situation.

Lowell
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!
Brian O'Leary
Brian O'Leary
SSC-Enthusiastic
SSC-Enthusiastic (137 reputation)SSC-Enthusiastic (137 reputation)SSC-Enthusiastic (137 reputation)SSC-Enthusiastic (137 reputation)SSC-Enthusiastic (137 reputation)SSC-Enthusiastic (137 reputation)SSC-Enthusiastic (137 reputation)SSC-Enthusiastic (137 reputation)

Group: General Forum Members
Points: 137 Visits: 116
Hi

I doubt the inability to update this table has anything to do with the check constraint (if it has been dropped). What exception message do you get when you try to update a row? do you get the same message if you do it? does the Delphi application have update permissions?

(FYI permissions of this sort applied directly to tables is generally considered to be bad practice, you should use stored procedures :-))

MCITP SQL Server 2005/2008 DBA/DBD
mfayard
mfayard
Forum Newbie
Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)

Group: General Forum Members
Points: 4 Visits: 13
There is no trigger on this table. The delphi app has permissions to update the table. I can insert any record into the table. The app can update any record that it inserts. It cannot, however, update any record that it does not insert. It does not give an exception or error message. It just does not do the update.
PaulB-TheOneAndOnly
PaulB-TheOneAndOnly
SSChampion
SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)

Group: General Forum Members
Points: 13867 Visits: 4639
mfayard (9/15/2010)
It cannot, however, update any record that it does not insert. It does not give an exception or error message. It just does not do the update.

Does the transaction completes?
Would you mind in tracing such scenario?

_____________________________________
Pablo (Paul) Berzukov

Author of Understanding Database Administration available at Amazon and other bookstores.

Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.
mfayard
mfayard
Forum Newbie
Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)

Group: General Forum Members
Points: 4 Visits: 13
Yes. It completes have put breakpoints in the code and traced it. All fields are being loaded and the dataset component is in edit mode. It then does a post with all data elements populated with new values. No errors, messages of any type. I also did this for new records and the code traces (steps) through the same code. So there is no deviation in the steps when it does the update successfully or not.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search