relationship question

Hi It may be a stupid question but I can not see why I define a relationship
and disable the option "Disabling a Foreign Key Constraint with INSERT and
UPDATE Statements"
in this case I just dont define the relationship.
Somebody can say a comment.
You can define FK on tables on simply disable them for loading
maintainance or other purposes, and then enable them again. Otherwise
you would have to drop them and recreate them.
CREATE TABLE SomeParentTable
(
ParentPKCol INT
CONSTRAINT PK_SomeParentTable PRIMARY KEY (ParentPKCol)
)
Create table SomeChildTable
(
PKChildCol INT,
ParentPKCol INT NOT NULL
CONSTRAINT fk1_SomeParentTable
FOREIGN KEY
REFERENCES SomeParentTable (ParentPKCol)
)
--Doesn=B4t work, CHECK is enabled
insert into SomeChildTable Values (1,1)
--Disabling the CHECK
ALTER TABLE SomeChildTable NOCHECK CONSTRAINT fk1_SomeParentTable
--This works now
insert into SomeChildTable Values (1,1)
--Delete it once again
DELETE FROM SomeChildTable
--THis is the normal behaviour
ALTER TABLE SomeChildTable NOCHECK CONSTRAINT fk1_SomeParentTable
--Inserting first the parent then the child records
insert into SomeParentTable Values (1)
insert into SomeChildTable Values (1,1)
Drop table SomeChildTable
Drop table SomeParentTable
HTH, Jens Suessmeyer.
|||Ohh absolutly, now I see the reason
Kenny M.
"Jens" wrote:

> You can define FK on tables on simply disable them for loading
> maintainance or other purposes, and then enable them again. Otherwise
> you would have to drop them and recreate them.
>
> CREATE TABLE SomeParentTable
> (
> ParentPKCol INT
> CONSTRAINT PK_SomeParentTable PRIMARY KEY (ParentPKCol)
> )
> Create table SomeChildTable
> (
> PKChildCol INT,
> ParentPKCol INT NOT NULL
> CONSTRAINT fk1_SomeParentTable
> FOREIGN KEY
> REFERENCES SomeParentTable (ParentPKCol)
> )
>
> --Doesn′t work, CHECK is enabled
> insert into SomeChildTable Values (1,1)
> --Disabling the CHECK
> ALTER TABLE SomeChildTable NOCHECK CONSTRAINT fk1_SomeParentTable
> --This works now
> insert into SomeChildTable Values (1,1)
> --Delete it once again
> DELETE FROM SomeChildTable
> --THis is the normal behaviour
> ALTER TABLE SomeChildTable NOCHECK CONSTRAINT fk1_SomeParentTable
> --Inserting first the parent then the child records
> insert into SomeParentTable Values (1)
> insert into SomeChildTable Values (1,1)
>
> Drop table SomeChildTable
> Drop table SomeParentTable
>
> HTH, Jens Suessmeyer.
>
sql

Related to Auto Increament

Hello EveryOne,

I have a program which insert the Data into table Fine. But the problem is that whenever expecption is raised the auto increament id is increases..
i want that user enter the record and without error the auto increament id is 1 and when second user enter the record on table and exception raised and next time he entered again then his record auto increament id is 3 why? But record is not entered only the expecption is raised.

How to resolve this problem to make cosistency in record of other tables.? Help me

Because the database had already obtained the identity value before the exception was encountered. Identity fields are not guaranteed to be consecutive, nor are they necessarily guaranteed to be increasing (although I have never seen a situation in which it hasn't increased). If you assume the identity value only guarantees that only one record will have that value, and that is the only thing you assume, you'll be safe. Assuming anything else is assuming too much.

|||

Always getting consecutive numbers for a column in any database is very difficult to achieve, as databases by their very nature are accessed in parallel and not in serial. Once you start to think about the problems you could encounter trying to guarantee consecutive numbers then you'll realise why it is impossible to achieve and also have good performance.

Unless there is a really, really, really good reason why you don't want gaps in your sequence then just be content that your identity column is unique. It's actual value is usually irrelevent, so don't worry about it.