Table constraints

Question

Table constraints

mfayard

SSC Enthusiast

Points: 150
More actions
September 13, 2010 at 2:42 pm

#223826

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?

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Brian O'Leary SSChasing Mays Points: 623 More actions · Answer 1

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 SSC Enthusiast Points: 150 More actions · Answer 2

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 SSC Guru Points: 323518 More actions · Answer 3

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 SSChasing Mays Points: 623 More actions · Answer 4

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 SSC Enthusiast Points: 150 More actions · Answer 5

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 SSC Guru Points: 66772 More actions · Answer 6

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 SSC Enthusiast Points: 150 More actions · Answer 7

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.