How to check constraint ONLY on commit? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

How to check constraint ONLY on commit?

Author

Topic

punki
Starting Member

10 Posts

Posted - 2006-12-14 : 12:23:11

Hi
Here is my problem:

CREATE TABLE [adept].[TEST] (
[id] int NOT NULL,
[fk_id] int NULL,
PRIMARY KEY CLUSTERED ([id]),
CONSTRAINT [TEST_fk] FOREIGN KEY ([fk_id])
REFERENCES [adept].[TEST] ([id])
ON UPDATE NO ACTION
ON DELETE NO ACTION
)
ON [PRIMARY]
GO

And now i whant do somthing like this:

BEGIN TRAN

....(1) insert into adept.TEST(id,fk_id) values(2,1)
....(2) insert into adept.TEST(id,fk_id) values(1,null)

COMMIT TRAN

Ok sow problem is that (1) is refering to not yet inserted (2) sow
i whant thata mssql check that CONSTRAINT on commit NOT during inserting. But how to do it???

Kristen
Test

22859 Posts

Posted - 2006-12-14 : 12:29:03

You can't

Insert the first one with a valid [dummy] value, insert the second one, update the first one to reference the newly added value, commit. Its a PITA ...

In your example inserting them in reverse order would work, I think, [you could store them in a temporary table, and insert them "ORDER BY" so that the RI is handled in the correct order] but I expect your real-world problem doesn't allow that?

Kristen

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2006-12-14 : 12:38:44

What algorithm would have you insert the child before the parent?

If you insert the parents first, there's no problem.

- Jeff

punki
Starting Member

10 Posts

Posted - 2006-12-14 : 12:45:02

im sure there is way to doit. I know that if you first insert parent all will be ok but what if you get 1000 of records and you dont know ho is ho parent?? As far as I know there is way to first insert all records and on commit check all constraint but i dont know how :/

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2006-12-14 : 12:52:50

Put all the data into a staging table (if it is not there already), and then you can do something like this:


declare @rc int;
set @rc = 1;

while (@rc <> 0)
begin
  insert into TEST (id, fk_id)
  select STAGING.id, STAGING.fk_id
  from STAGING
  inner join TEST T1 on STAGING.fk_id = T1.id
  left outer join TEST T2 on STAGING.id = T2.id
  where t2.id is Null

  set @rc = @@rowcount
end

that adds rows that aren't already there that also have an existing parent, so the inserts will succeed. It might require a few iterations to get it all in there, but that will bulk load your data from one table to the next if the table has FK references to itself without violating any constraints (FK or PK).

there may be a way to do it all in 1 INSERT with some clever ORDER BY clause, but I can't think of one just yet.

- Jeff

punki
Starting Member

10 Posts

Posted - 2006-12-14 : 13:10:04

Problem is that this table is only example of my problem. in reality i need some universal way to doit . Thats whay i need this checking on commit.

snSQL
Master Smack Fu Yak Hacker

1837 Posts

Posted - 2006-12-14 : 13:20:49

quote:
Originally posted by punki

im sure there is way to doit. I know that if you first insert parent all will be ok but what if you get 1000 of records and you dont know ho is ho parent?? As far as I know there is way to first insert all records and on commit check all constraint but i dont know how :/

You can disable the constraint, run your code then enable the constraint again.

ALTER TABLE adept.TEST NOCHECK CONSTRAINT [TEST_fk]
-- perform inserts
ALTER TABLE adept.TEST CHECK CONSTRAINT [TEST_fk]

punki
Starting Member

10 Posts

Posted - 2006-12-14 : 13:34:41

Yes thats almost ideal solution :)
tnx all for answers.
But if someone will know how to set off checking CONSTRAINTs during transaction in automatic way pleas let me know :)
For now i will use snSQL option.

Kristen
Test

22859 Posts

Posted - 2006-12-15 : 02:33:52

"You can disable the constraint, run your code then enable the constraint again."

Which will also be disabling the constraint for any other user of the database during that time. (I suppose you could lock the table, but then everyone else will get blocked / time out / etc.)

And how will you handle the error if the constraint cannot be re-enabled? In effect your code will abort leaving the table WITHOUT the constraint.

I would solve the problem in the data - e.g. using a staging table, as Jess and I suggested.

There is no automatic way of solving this in SQL Server (do other databases such as Oracle allow checking of constraints on Commit? It would obviously help with renaming FK Parent and Child records)

Kristen

punki
Starting Member

10 Posts

Posted - 2006-12-15 : 06:31:50

Thats what i was looking for :/

Deferrable constraints

SQL99 standards say that constraints can be either DEFERRABLE or NOT DEFERRABLE (default). A NOT DEFERRABLE constraint is checked after each DDL statement; DEFERRABLE constraints can either be checked immediately after every INSERT, DELETE, or UPDATE (INITIALLY IMMEDIATE) or at the end of the transaction (INITIALLY DEFERRED).

That feature can be especially helpful when data loads are performed with no particular order; that allows you to load data into child table(s) first, then into parent table(s). Another use would be loading data that does not comply with a CHECK constraint and then updating it appropriately.

The only vendor out of our "big three" who provides deferrable constraints is Oracle 9i. The syntax is

[[NOT] DEFERRABLE
[INITIALLY {IMMEDIATE | DEFERRED}]]

or

[[INITIALLY {IMMEDIATE |
DEFERRED}] [NOT] DEFERRABLE]

Kristen
Test

22859 Posts

Posted - 2006-12-15 : 06:35:41

"The only vendor out of our "big three" who provides deferrable constraints is Oracle 9i"

Bother!

punki
Starting Member

10 Posts

Posted - 2006-12-15 : 06:47:36

yes only Oracle :/

punki
Starting Member

10 Posts

Posted - 2006-12-15 : 06:59:22

there is one more thing:

BEGIN TRAN;
ALTER TABLE adept.TEST NOCHECK CONSTRAINT ALL
insert into adept.TEST(id,fk_id) values(2,2)
insert into adept.TEST(id,fk_id) values(1,null)
if there was error
rollback tran
ALTER TABLE adept.TEST CHECK CONSTRAINT ALL
COMMIT TRAN

on commit mssql DOESNT check CONSTRAINT and will commit this data (there is refer to id 2 which doesnt exist)!!!!

snSQL
Master Smack Fu Yak Hacker

1837 Posts

Posted - 2006-12-15 : 10:06:36

The data is checked when you reenable the constraint, not when you commit, so you should check for error after the second ALTER TABLE. Also, don't use ALL, use the foreign key constraint name so that you only disable that one constraint, you don't want to disable all constraints on the table.

snSQL
Master Smack Fu Yak Hacker

1837 Posts

Posted - 2006-12-15 : 10:09:35

quote:
Which will also be disabling the constraint for any other user of the database during that time. (I suppose you could lock the table, but then everyone else will get blocked / time out / etc.)

And how will you handle the error if the constraint cannot be re-enabled? In effect your code will abort leaving the table WITHOUT the constraint.

I would solve the problem in the data - e.g. using a staging table, as Jess and I suggested.

I'd agree - but the constraint option is good for loading scenarios (punki said a 1000 records at a time), in which case a table lock may be acceptable.

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2006-12-15 : 10:43:24

or, instead of messing with all this, you just use simple SQL statements to load data that doesn't violate any constraints, as I demonstrated ... Why the example is specific, of course, the technique I showed is easily applied to different situations.

I personally tend to go with the solutions that are simple, short, clear, don't require changing settings or violating data integrity, and don't rely on error checking after the fact.

Why not just insert only valid data using simple JOINS?

- Jeff

punki
Starting Member

10 Posts

Posted - 2006-12-17 : 08:06:30

snSQL:
ALTER TABLE adept.TEST CHECK CONSTRAINT ALL !!!DIDNT!!! check if constrains are ok! it only turn them on and do nothing more. comit didnt check constraints either. So i was able tu inserts corupt data!!
jsmith8858:
I have no other way then just write some code to do what DB should be doing :/
sow im doing somathing like that:

<strat tran>
do{
a=<count how many inserts we have>
try{
<inserts values>
}
catch(Exception){
<do nothing>
}
if(<count how many inserts we have><a){
is_progres=true;
}
else{
is_progres=false;
}

{while( is_progres);

<end tran>
+ rollback and things like that

Kristen
Test

22859 Posts

Posted - 2006-12-18 : 04:07:31

Afraid I reckon that's your best way, for now.

Kristen

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2006-12-18 : 10:34:58

quote:
Originally posted by punki

jsmith8858:
I have no other way then just write some code to do what DB should be doing :/
sow im doing somathing like that:

<strat tran>
do{
a=<count how many inserts we have>
try{
<inserts values>
}
catch(Exception){
<do nothing>
}
if(<count how many inserts we have><a){
is_progres=true;
}
else{
is_progres=false;
}

{while( is_progres);

<end tran>
+ rollback and things like that

I don't understand what you are saying .. why can't you do it the way I demonstrated?

It is very bad practice to execute code that you know will cause exceptions, when you can write code that will avoid exceptions in the first place.

Do you understand the code that i posted and how it works? Did you even try to implement it?

If you give me a more accurate picture of your situation, I can help you adapt code to what you have. All you gave us was your "Test" schema, and that's what I wrote the code for, so why bother giving us that schema if it doesn't apply to your current situation?

It is pretty easy to implement the technique that I showed you if you know basic SQL, there is no reason to try over and over to insert data into tables when you know that constraints will be violated when you can simply only insert valid data in the first place. Simply use LEFT OUTER JOINS or WHERE NOT EXISTS() clauses in your INSERT statements to ensure that you are only inserting valid data. It works.

- Jeff

punki
Starting Member

10 Posts

Posted - 2006-12-18 : 12:20:47

jsmith8858:
Yes, i didnt give you enough information to understand my problem. What i really whant to do is to be able to do somthing like replication. i only have msde mssql sow i must make it. I have for exapmle 10 db on one server(master) and the same 10 db on other(slave). i whant to synch them but i dont whant to hard code their structure. This code i wrote is able to insert rows in any table and i dont need to know their structure. I agre with you that ignoring exception isnt good solution but as far i test it it works fine (for now ;) ).

punki
Starting Member

10 Posts

Posted - 2006-12-18 : 12:26:49

db=tables ;)

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources