Question about self referencing delete

Let's say we have table as follows.
empid mgrid empname
--
1 null abc
2 1 pqr
empid is primary key for the table
mgrid is self referencing foreign key towards empid
Now on above table, I execute DML as:
DELETE FROM emp_1 WHERE empid in (1, 2)
So SQLSerever, will first attempt to delete record where empid is 1. (Isn't
it?) And then SQLServer is supposed to give an error ; as empid = 1 is being
referred as foreign key in record where empid is 2. But it doesn't happen
so. SQLServer doesn't give an error. So does that mean SQLServer first
deletes record where empid is 2. And then it deletes the record where empid
is 1? (In short, deletes all cascading child records first and then parent
records) Is it SQLServer's normal and expected behavior? Or it is not
guaranteed? Or anything else? Please guide. Thanks.
Regards,
PravinThis is a multi-part message in MIME format.
--=_NextPart_000_0030_01C3745C.97B12C90
Content-Type: text/plain;
charset="Windows-1252"
Content-Transfer-Encoding: quoted-printable
Thanks Tom.
I got the point that because parent and child both are there in delete =statement, SQLServer doesn't throw an error.
However, another point you have specified is "In your case, you did not =specify ON DELETE CASCADE in your foreign key". Does that mean SQLServer =allows setting the option 'on delete cascade' for self referencing keys? =If yes, then please inform me how. If no, then your answer that "In your =DELETE, you specified that you were deleting both the child and the =related parent, so there would be no RI violation." is just the answer =for my question. Right?
Note: I was not able to set this option from enterprize manager. Please =guide, Thanks in Advance.
-- Regards,
Pravin Joshi
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message =news:errsWw9cDHA.828@.TK2MSFTNGP11.phx.gbl...
In your case, you did not specify ON DELETE CASCADE in your foreign =key. If you had, it would have failed, since it is a circular =reference. In your DELETE, you specified that you were deleting both =the child and the related parent, so there would be no RI violation. If =you try the following, it should fail:
DELETE FROM emp_1 WHERE empid =3D 1
-- Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"Pravin" <expertco@.vsnl.com> wrote in message =news:OQxXPs9cDHA.456@.TK2MSFTNGP10.phx.gbl...
Let's say we have table as follows.
empid mgrid empname
--
1 null abc
2 1 pqr
empid is primary key for the table
mgrid is self referencing foreign key towards empid
Now on above table, I execute DML as:
DELETE FROM emp_1 WHERE empid in (1, 2)
So SQLSerever, will first attempt to delete record where empid is 1. =(Isn't
it?) And then SQLServer is supposed to give an error ; as empid =3D 1 =is being
referred as foreign key in record where empid is 2. But it doesn't =happen
so. SQLServer doesn't give an error. So does that mean SQLServer first
deletes record where empid is 2. And then it deletes the record where =empid
is 1? (In short, deletes all cascading child records first and then =parent
records) Is it SQLServer's normal and expected behavior? Or it is not
guaranteed? Or anything else? Please guide. Thanks.
Regards,
Pravin
--=_NextPart_000_0030_01C3745C.97B12C90
Content-Type: text/html;
charset="Windows-1252"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&

---

Thanks Tom.
I got the point that because parent =and child both are there in delete statement, SQLServer doesn't throw an error.
However, another point you have =specified is "In your case, you did not specify ON DELETE =CASCADE in your foreign key". Does that mean SQLServer allows setting the =option 'on delete cascade' for self referencing keys? If yes, then please inform me =how. If no, then your answer that "In your DELETE, you =specified that you were deleting both the child and the related parent, so there would =be no RI violation." is just the answer for my question. =Right?
Note: I was not able to set this =option from enterprize manager. Please guide, Thanks in Advance.
-- Regards,Pravin Joshi
"Tom Moreau" = wrote in message news:errsWw9cDHA.828@.T=K2MSFTNGP11.phx.gbl...
In your case, you did not specify ON =DELETE CASCADE in your foreign key. If you had, it would have failed, =since it is a circular reference. In your DELETE, you specified that you =were deleting both the child and the related parent, so there would be no =RI violation. If you try the following, it should =fail:

DELETE FROM emp_1 WHERE empid ==3D 1

-- Tom

=---T=homas A. Moreau, BSc, PhD, MCSE, MCDBASQL Server MVPColumnist, SQL =Server ProfessionalToronto, ON Canadahttp://www.pinnaclepublishing.com/sql">www.pinnaclepublishing.com=/sql


"Pravin" wrote in message news:OQxXPs9cDHA.456@.T=K2MSFTNGP10.phx.gbl...Let's say we have table as follows.empid mgrid =empname--1 &nbs=p; null =abc2 1 pqrempid is primary key =for the tablemgrid is self referencing foreign key towards =empidNow on above table, I execute DML as: DELETE FROM emp_1 =WHERE empid in (1, 2)So SQLSerever, will first attempt to delete =record where empid is 1. (Isn'tit?) And then SQLServer is supposed to =give an error ; as empid =3D 1 is beingreferred as foreign key in record =where empid is 2. But it doesn't happenso. SQLServer doesn't give an error. So =does that mean SQLServer firstdeletes record where empid is 2. And then =it deletes the record where empidis 1? (In short, deletes all =cascading child records first and then parentrecords) Is it SQLServer's normal and = expected behavior? Or it is notguaranteed? Or anything else? =Please guide. =Thanks.Regards,Pravin

---

--=_NextPart_000_0030_01C3745C.97B12C90--|||This is a multi-part message in MIME format.
--=_NextPart_000_000F_01C37454.B43BD200
Content-Type: text/plain;
charset="Windows-1252"
Content-Transfer-Encoding: quoted-printable
The point I was making was that you cannot use ON DELETE CASCADE in a =self-referencing situation. You should avoid using EM for creating =tables. I stick with scripting it out and then saving the scripts under =version control.
-- Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
.
"Pravin" <expertco@.vsnl.com> wrote in message =news:usF6m5CdDHA.560@.TK2MSFTNGP11.phx.gbl...
Thanks Tom.
I got the point that because parent and child both are there in delete =statement, SQLServer doesn't throw an error.
However, another point you have specified is "In your case, you did not =specify ON DELETE CASCADE in your foreign key". Does that mean SQLServer =allows setting the option 'on delete cascade' for self referencing keys? =If yes, then please inform me how. If no, then your answer that "In your =DELETE, you specified that you were deleting both the child and the =related parent, so there would be no RI violation." is just the answer =for my question. Right?
Note: I was not able to set this option from enterprize manager. Please =guide, Thanks in Advance.
-- Regards,
Pravin Joshi
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message =news:errsWw9cDHA.828@.TK2MSFTNGP11.phx.gbl...
In your case, you did not specify ON DELETE CASCADE in your foreign =key. If you had, it would have failed, since it is a circular =reference. In your DELETE, you specified that you were deleting both =the child and the related parent, so there would be no RI violation. If =you try the following, it should fail:
DELETE FROM emp_1 WHERE empid =3D 1
-- Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"Pravin" <expertco@.vsnl.com> wrote in message =news:OQxXPs9cDHA.456@.TK2MSFTNGP10.phx.gbl...
Let's say we have table as follows.
empid mgrid empname
--
1 null abc
2 1 pqr
empid is primary key for the table
mgrid is self referencing foreign key towards empid
Now on above table, I execute DML as:
DELETE FROM emp_1 WHERE empid in (1, 2)
So SQLSerever, will first attempt to delete record where empid is 1. =(Isn't
it?) And then SQLServer is supposed to give an error ; as empid =3D 1 =is being
referred as foreign key in record where empid is 2. But it doesn't =happen
so. SQLServer doesn't give an error. So does that mean SQLServer first
deletes record where empid is 2. And then it deletes the record where =empid
is 1? (In short, deletes all cascading child records first and then =parent
records) Is it SQLServer's normal and expected behavior? Or it is not
guaranteed? Or anything else? Please guide. Thanks.
Regards,
Pravin
--=_NextPart_000_000F_01C37454.B43BD200
Content-Type: text/html;
charset="Windows-1252"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&

---

The point I was making was that you =cannot use ON DELETE CASCADE in a self-referencing situation. You should avoid =using EM for creating tables. I stick with scripting it out and then saving =the scripts under version control.
-- Tom
----Thomas A. =Moreau, BSc, PhD, MCSE, MCDBASQL Server MVPColumnist, SQL Server ProfessionalToronto, ON Canadahttp://www.pinnaclepublishing.com/sql">www.pinnaclepublishing.com=/sql.
"Pravin" wrote in message news:usF6m5CdDHA.560@.T=K2MSFTNGP11.phx.gbl...
Thanks Tom.
I got the point that because parent =and child both are there in delete statement, SQLServer doesn't throw an error.
However, another point you have =specified is "In your case, you did not specify ON DELETE =CASCADE in your foreign key". Does that mean SQLServer allows setting the =option 'on delete cascade' for self referencing keys? If yes, then please inform me =how. If no, then your answer that "In your DELETE, you =specified that you were deleting both the child and the related parent, so there would =be no RI violation." is just the answer for my question. =Right?
Note: I was not able to set this =option from enterprize manager. Please guide, Thanks in Advance.
-- Regards,Pravin Joshi
"Tom Moreau" = wrote in message news:errsWw9cDHA.828@.T=K2MSFTNGP11.phx.gbl...
In your case, you did not specify ON =DELETE CASCADE in your foreign key. If you had, it would have failed, =since it is a circular reference. In your DELETE, you specified that you =were deleting both the child and the related parent, so there would be no =RI violation. If you try the following, it should =fail:

DELETE FROM emp_1 WHERE empid ==3D 1

-- Tom

=---T=homas A. Moreau, BSc, PhD, MCSE, MCDBASQL Server MVPColumnist, SQL =Server ProfessionalToronto, ON Canadahttp://www.pinnaclepublishing.com/sql">www.pinnaclepublishing.com=/sql


"Pravin" wrote in message news:OQxXPs9cDHA.456@.T=K2MSFTNGP10.phx.gbl...Let's say we have table as follows.empid mgrid =empname--1 &nbs=p; null =abc2 1 pqrempid is primary key =for the tablemgrid is self referencing foreign key towards =empidNow on above table, I execute DML as: DELETE FROM emp_1 =WHERE empid in (1, 2)So SQLSerever, will first attempt to delete =record where empid is 1. (Isn'tit?) And then SQLServer is supposed to =give an error ; as empid =3D 1 is beingreferred as foreign key in record =where empid is 2. But it doesn't happenso. SQLServer doesn't give an error. So =does that mean SQLServer firstdeletes record where empid is 2. And then =it deletes the record where empidis 1? (In short, deletes all =cascading child records first and then parentrecords) Is it SQLServer's normal and = expected behavior? Or it is notguaranteed? Or anything else? =Please guide. =Thanks.Regards,Pravin

---

--=_NextPart_000_000F_01C37454.B43BD200--|||This is a multi-part message in MIME format.
--=_NextPart_000_000D_01C374C4.4266C7F0
Content-Type: text/plain;
charset="Windows-1252"
Content-Transfer-Encoding: quoted-printable
Ok Tom. That clears the doubt. Thanks.
-- Regards,
Pravin
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message =news:#aD7FZHdDHA.4020@.tk2msftngp13.phx.gbl...
The point I was making was that you cannot use ON DELETE CASCADE in a =self-referencing situation. You should avoid using EM for creating =tables. I stick with scripting it out and then saving the scripts under =version control.
-- Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
.
"Pravin" <expertco@.vsnl.com> wrote in message =news:usF6m5CdDHA.560@.TK2MSFTNGP11.phx.gbl...
Thanks Tom.
I got the point that because parent and child both are there in delete =statement, SQLServer doesn't throw an error.
However, another point you have specified is "In your case, you did =not specify ON DELETE CASCADE in your foreign key". Does that mean =SQLServer allows setting the option 'on delete cascade' for self =referencing keys? If yes, then please inform me how. If no, then your =answer that "In your DELETE, you specified that you were deleting both =the child and the related parent, so there would be no RI violation." is =just the answer for my question. Right?
Note: I was not able to set this option from enterprize manager. =Please guide, Thanks in Advance.
-- Regards,
Pravin Joshi
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message =news:errsWw9cDHA.828@.TK2MSFTNGP11.phx.gbl...
In your case, you did not specify ON DELETE CASCADE in your foreign =key. If you had, it would have failed, since it is a circular =reference. In your DELETE, you specified that you were deleting both =the child and the related parent, so there would be no RI violation. If =you try the following, it should fail:
DELETE FROM emp_1 WHERE empid =3D 1
-- Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"Pravin" <expertco@.vsnl.com> wrote in message =news:OQxXPs9cDHA.456@.TK2MSFTNGP10.phx.gbl...
Let's say we have table as follows.
empid mgrid empname
--
1 null abc
2 1 pqr
empid is primary key for the table
mgrid is self referencing foreign key towards empid
Now on above table, I execute DML as:
DELETE FROM emp_1 WHERE empid in (1, 2)
So SQLSerever, will first attempt to delete record where empid is 1. =(Isn't
it?) And then SQLServer is supposed to give an error ; as empid =3D =1 is being
referred as foreign key in record where empid is 2. But it doesn't =happen
so. SQLServer doesn't give an error. So does that mean SQLServer =first
deletes record where empid is 2. And then it deletes the record =where empid
is 1? (In short, deletes all cascading child records first and then =parent
records) Is it SQLServer's normal and expected behavior? Or it is =not
guaranteed? Or anything else? Please guide. Thanks.
Regards,
Pravin
--=_NextPart_000_000D_01C374C4.4266C7F0
Content-Type: text/html;
charset="Windows-1252"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&

---

Ok Tom. That clears the doubt. Thanks.
-- Regards,Pravin
"Tom Moreau" = wrote in message news:#aD7FZHdDHA.4020=@.tk2msftngp13.phx.gbl...
The point I was making was that you =cannot use ON DELETE CASCADE in a self-referencing situation. You should =avoid using EM for creating tables. I stick with scripting it out and =then saving the scripts under version control.
-- Tom

----Thomas A. =Moreau, BSc, PhD, MCSE, MCDBASQL Server MVPColumnist, SQL Server ProfessionalToronto, ON Canadahttp://www.pinnaclepublishing.com/sql">www.pinnaclepublishing.com=/sql.
"Pravin" wrote in message news:usF6m5CdDHA.560@.T=K2MSFTNGP11.phx.gbl...
Thanks Tom.

I got the point that because parent =and child both are there in delete statement, SQLServer doesn't throw an error.

However, another point you have =specified is "In your case, you did not specify ON DELETE =CASCADE in your foreign key". Does that mean SQLServer allows setting =the option 'on delete cascade' for self referencing keys? If yes, then =please inform me how. If no, then your answer that "In =your DELETE, you specified that you were deleting both the child and the related =parent, so there would be no RI violation." is just the answer for my =question. Right?

Note: I was not able to set =this option from enterprize manager. Please guide, Thanks in Advance.
-- Regards,Pravin Joshi
"Tom Moreau" = wrote in message news:errsWw9cDHA.828@.T=K2MSFTNGP11.phx.gbl...
In your case, you did not specify =ON DELETE CASCADE in your foreign key. If you had, it would have failed, =since it is a circular reference. In your DELETE, you specified that =you were deleting both the child and the related parent, so there would =be no RI violation. If you try the following, it should =fail:

DELETE FROM emp_1 WHERE =empid =3D 1

-- Tom

=---T=homas A. Moreau, BSc, PhD, MCSE, MCDBASQL Server MVPColumnist, SQL =Server ProfessionalToronto, ON Canadahttp://www.pinnaclepublishing.com/sql">www.pinnaclepublishing.com=/sql


"Pravin" wrote in =message news:OQxXPs9cDHA.456@.T=K2MSFTNGP10.phx.gbl...Let's say we have table as follows.empid mgrid =empname--1 &nbs=p; null =abc2 1 pqrempid is primary =key for the tablemgrid is self referencing foreign key towards =empidNow on above table, I execute DML as: DELETE FROM =emp_1 WHERE empid in (1, 2)So SQLSerever, will first attempt to =delete record where empid is 1. (Isn'tit?) And then SQLServer is =supposed to give an error ; as empid =3D 1 is beingreferred as foreign key =in record where empid is 2. But it doesn't happenso. SQLServer doesn't =give an error. So does that mean SQLServer firstdeletes record where =empid is 2. And then it deletes the record where empidis 1? (In short, =deletes all cascading child records first and then parentrecords) Is it =SQLServer's normal and expected behavior? Or it is notguaranteed? Or =anything else? Please guide. Thanks.Regards,Pravin

---

--=_NextPart_000_000D_01C374C4.4266C7F0--sql

Question about Primary key

Hi there

Ok here is my problem.
Lets say i have 20 rows in my table and i delete 19 and add 1.
The primary key's will be 1 and 21, my question is how do i get it to say 1 and 2 ?

looks like your table primary key is an identity column.

Why do you want to do this ? What if you delete a column and add another again ? Do you want to keep repeating this ?

|||Looks like you need:

DBCC

CHECKIDENT(tableName, RESEED, new_identity_value)

Make sure to set the new identity value to the max of the current identity fields. i.e. run something with logic like:

var maxIdentity = SELECT MAX(ID) FROM tableName

DBCC CHECKIDENT(tableName, RESEED, maxIdentity)

If your last identity was 1 after the delete, the next row entered will be 2.

Question about Primary Key

I want to create a Primay Key
as combination of 3 fields.
Prod_NO
Prod_Item
Prod_cont
In this case there is a chance that PRod_item
or Prod_Cont will be blank, can I still create
these three as Primary Key? If not, what is the
alternate way to do so?
Thank you,I would recommend having as a primary key a value that is guaranteed to be
unique in all instances for each record like the identity key, you can
certainly use your combination of fields as a 'candidate' key and compine
them in joins and such.
Troy
"Me" <Me@.discussions.microsoft.com> wrote in message
news:E7E2961B-5A43-4246-8B27-68F99477D8FA@.microsoft.com...
>I want to create a Primay Key
> as combination of 3 fields.
> Prod_NO
> Prod_Item
> Prod_cont
> In this case there is a chance that PRod_item
> or Prod_Cont will be blank, can I still create
> these three as Primary Key? If not, what is the
> alternate way to do so?
> Thank you,
>

Question about Primary Key

PK columns cannot be null. But you can get uniqueness by
creating it as a unique constraint or unique index... Bruce

>--Original Message--
>I want to create a Primay Key
>as combination of 3 fields.
>Prod_NO
>Prod_Item
>Prod_cont
>In this case there is a chance that PRod_item
>or Prod_Cont will be blank, can I still create
>these three as Primary Key? If not, what is the
>alternate way to do so?
>Thank you,
>.
>Bruce,
I think what you suggested will work, but do you foresee
any other problems in this? Will everything else
work the same? I haven't used it any times hence
the question.
Thank you,
-M
"Bruce de Freitas" wrote:

> PK columns cannot be null. But you can get uniqueness by
> creating it as a unique constraint or unique index... Bruce
>
>|||It depends...
PK Constraints do not allow NULLs, but UNIQUE constraints allow a single
NULL.
So if your data looked like:
1, NULL, NULL
That would be okay for a UNIQUE Constraint.
Another record of
1, NULL, NULL will violate the constraint however.
HTH
Rick Sawtell
MCT, MCSD, MCDBA
"Me" <Me@.discussions.microsoft.com> wrote in message
news:33CCE845-1F74-4FE3-9570-B79E0D442C20@.microsoft.com...[vbcol=seagreen]
> Bruce,
> I think what you suggested will work, but do you foresee
> any other problems in this? Will everything else
> work the same? I haven't used it any times hence
> the question.
> Thank you,
> -M
> "Bruce de Freitas" wrote:
>

question about primary key

how can I set my primary key to star again from 1' for example, if I had a
table with some rows in it and I deleted all that rows, next time when I
insert a new row in that table, primary key starts from last primary key of
previous rows, but I want it to start again from 1.
is that possible'
thanks!Bernard,
presumably you mean that your primary key has an identity attribute? In this
case if you truncate the table the seed is reset, or you can use DBCC
CHECKIDENT :
USE pubs
GO
DBCC CHECKIDENT (jobs, RESEED)
GO
to get the original seed value (usually 1)
or
USE pubs
GO
DBCC CHECKIDENT (jobs, RESEED, 30)
GO
to set the initial value to be 30.
HTH,
Paul Ibison

question about primary key

how can I set my primary key to star again from 1' for example, if I had a
table with some rows in it and I deleted all that rows, next time when I
insert a new row in that table, primary key starts from last primary key of
previous rows, but I want it to start again from 1.
is that possible'
thanks!Bernard,
presumably you mean that your primary key has an identity attribute? In this
case if you truncate the table the seed is reset, or you can use DBCC
CHECKIDENT :
USE pubs
GO
DBCC CHECKIDENT (jobs, RESEED)
GO
to get the original seed value (usually 1)
or
USE pubs
GO
DBCC CHECKIDENT (jobs, RESEED, 30)
GO
to set the initial value to be 30.
HTH,
Paul Ibisonsql

Question about Primary Key

I want to create a Primay Key
as combination of 3 fields.
Prod_NO
Prod_Item
Prod_cont
In this case there is a chance that PRod_item
or Prod_Cont will be blank, can I still create
these three as Primary Key? If not, what is the
alternate way to do so?
Thank you,I would recommend having as a primary key a value that is guaranteed to be
unique in all instances for each record like the identity key, you can
certainly use your combination of fields as a 'candidate' key and compine
them in joins and such.
Troy
"Me" <Me@.discussions.microsoft.com> wrote in message
news:E7E2961B-5A43-4246-8B27-68F99477D8FA@.microsoft.com...
>I want to create a Primay Key
> as combination of 3 fields.
> Prod_NO
> Prod_Item
> Prod_cont
> In this case there is a chance that PRod_item
> or Prod_Cont will be blank, can I still create
> these three as Primary Key? If not, what is the
> alternate way to do so?
> Thank you,
>|||PK columns cannot be null. But you can get uniqueness by
creating it as a unique constraint or unique index... Bruce
>--Original Message--
>I want to create a Primay Key
>as combination of 3 fields.
>Prod_NO
>Prod_Item
>Prod_cont
>In this case there is a chance that PRod_item
>or Prod_Cont will be blank, can I still create
>these three as Primary Key? If not, what is the
>alternate way to do so?
>Thank you,
>.
>|||Bruce,
I think what you suggested will work, but do you foresee
any other problems in this? Will everything else
work the same? I haven't used it any times hence
the question.
Thank you,
-M
"Bruce de Freitas" wrote:
> PK columns cannot be null. But you can get uniqueness by
> creating it as a unique constraint or unique index... Bruce
>
> >--Original Message--
> >I want to create a Primay Key
> >as combination of 3 fields.
> >Prod_NO
> >Prod_Item
> >Prod_cont
> >
> >In this case there is a chance that PRod_item
> >or Prod_Cont will be blank, can I still create
> >these three as Primary Key? If not, what is the
> >alternate way to do so?
> >
> >Thank you,
> >
> >.
> >
>|||It depends...
PK Constraints do not allow NULLs, but UNIQUE constraints allow a single
NULL.
So if your data looked like:
1, NULL, NULL
That would be okay for a UNIQUE Constraint.
Another record of
1, NULL, NULL will violate the constraint however.
HTH
Rick Sawtell
MCT, MCSD, MCDBA
"Me" <Me@.discussions.microsoft.com> wrote in message
news:33CCE845-1F74-4FE3-9570-B79E0D442C20@.microsoft.com...
> Bruce,
> I think what you suggested will work, but do you foresee
> any other problems in this? Will everything else
> work the same? I haven't used it any times hence
> the question.
> Thank you,
> -M
> "Bruce de Freitas" wrote:
> > PK columns cannot be null. But you can get uniqueness by
> > creating it as a unique constraint or unique index... Bruce
> >
> >
> > >--Original Message--
> > >I want to create a Primay Key
> > >as combination of 3 fields.
> > >Prod_NO
> > >Prod_Item
> > >Prod_cont
> > >
> > >In this case there is a chance that PRod_item
> > >or Prod_Cont will be blank, can I still create
> > >these three as Primary Key? If not, what is the
> > >alternate way to do so?
> > >
> > >Thank you,
> > >
> > >.
> > >
> >

question about primary key

how can I set my primary key to star again from 1? for example, if I had a
table with some rows in it and I deleted all that rows, next time when I
insert a new row in that table, primary key starts from last primary key of
previous rows, but I want it to start again from 1.
is that possible?
thanks!
Bernard,
presumably you mean that your primary key has an identity attribute? In this
case if you truncate the table the seed is reset, or you can use DBCC
CHECKIDENT :
USE pubs
GO
DBCC CHECKIDENT (jobs, RESEED)
GO
to get the original seed value (usually 1)
or
USE pubs
GO
DBCC CHECKIDENT (jobs, RESEED, 30)
GO
to set the initial value to be 30.
HTH,
Paul Ibison

Question about Primary Key

I want to create a Primay Key
as combination of 3 fields.
Prod_NO
Prod_Item
Prod_cont
In this case there is a chance that PRod_item
or Prod_Cont will be blank, can I still create
these three as Primary Key? If not, what is the
alternate way to do so?
Thank you,
I would recommend having as a primary key a value that is guaranteed to be
unique in all instances for each record like the identity key, you can
certainly use your combination of fields as a 'candidate' key and compine
them in joins and such.
Troy
"Me" <Me@.discussions.microsoft.com> wrote in message
news:E7E2961B-5A43-4246-8B27-68F99477D8FA@.microsoft.com...
>I want to create a Primay Key
> as combination of 3 fields.
> Prod_NO
> Prod_Item
> Prod_cont
> In this case there is a chance that PRod_item
> or Prod_Cont will be blank, can I still create
> these three as Primary Key? If not, what is the
> alternate way to do so?
> Thank you,
>

Question about Primary Key

PK columns cannot be null. But you can get uniqueness by
creating it as a unique constraint or unique index... Bruce

>--Original Message--
>I want to create a Primay Key
>as combination of 3 fields.
>Prod_NO
>Prod_Item
>Prod_cont
>In this case there is a chance that PRod_item
>or Prod_Cont will be blank, can I still create
>these three as Primary Key? If not, what is the
>alternate way to do so?
>Thank you,
>.
>
Bruce,
I think what you suggested will work, but do you foresee
any other problems in this? Will everything else
work the same? I haven't used it any times hence
the question.
Thank you,
-M
"Bruce de Freitas" wrote:

> PK columns cannot be null. But you can get uniqueness by
> creating it as a unique constraint or unique index... Bruce
>
>
|||It depends...
PK Constraints do not allow NULLs, but UNIQUE constraints allow a single
NULL.
So if your data looked like:
1, NULL, NULL
That would be okay for a UNIQUE Constraint.
Another record of
1, NULL, NULL will violate the constraint however.
HTH
Rick Sawtell
MCT, MCSD, MCDBA
"Me" <Me@.discussions.microsoft.com> wrote in message
news:33CCE845-1F74-4FE3-9570-B79E0D442C20@.microsoft.com...[vbcol=seagreen]
> Bruce,
> I think what you suggested will work, but do you foresee
> any other problems in this? Will everything else
> work the same? I haven't used it any times hence
> the question.
> Thank you,
> -M
> "Bruce de Freitas" wrote:

question about like

Hello, i'm just wondering if i have for example a table with 2 colummns the first one is some primary key (int) and the other is of the image data type.
For example one row would be:

key data
232 0x7A5B6789D4E2...(thats just how sql server 2005 express shows me the data when i make a query insede Management Studio Express)

now i'm wondering if it is possible to do query for lets say %89D4% with LIKE,(i kinda want to search for a certain pattern of bits or in this case hexadecimal values ) i've tryed but i get an error. I just want to know if it is possible to do this with like and how and if it isn't, what other options do i have (full-text search - ?)?Well, the problem is that you are looking at binary data, not string data... Also, know that the image datatype is deprecated and should be replaced with varbinary(max).

What was the error you received?|||i tryed the following sql statement:
select * from DOCU_hlp.dbo.HTML a1 where a1.ID=100009 AND a1.HTML LIKE 0x%89D4% ;

and got the error message:
Msg 102, Level 15, State 1, Line 1
Incorrect syntax near 'D4'.

however this statement: select * from DOCU_hlp.dbo.HTML a1 where a1.ID=100009 AND a1.HTML LIKE 0x89D4 ;
does not give me any errors, but this statement doesn't really help any, since it searches for the exact value 0x89D4.

and about the part that image is depricated, well the database that i have to use already exists, and is full of data so i'm not to sure that i have anything to say about the data types that they use, just have to go with what they have ...|||

FitzFarseer wrote:

i tryed the following sql statement:
select * from DOCU_hlp.dbo.HTML a1 where a1.ID=100009 AND a1.HTML LIKE 0x%89D4% ;

and got the error message:
Msg 102, Level 15, State 1, Line 1
Incorrect syntax near 'D4'.

however this statement: select * from DOCU_hlp.dbo.HTML a1 where a1.ID=100009 AND a1.HTML LIKE 0x89D4 ;
does not give me any errors, but this statement doesn't really help any, since it searches for the exact value 0x89D4.

and about the part that image is depricated, well the database that i have to use already exists, and is full of data so i'm not to sure that i have anything to say about the data types that they use, just have to go with what they have ...

You need to surround your like statements with quotes...

select * from table where field like '%xxx%'|||select * from DOCU_hlp.dbo.HTML a1 where a1.ID=100009 AND a1.HTML LIKE '%89D4%' ;

gives me the following error:
Msg 8116, Level 16, State 1, Line 1
Argument data type varchar is invalid for argument 2 of like function.|||For your reading pleasure, and for reference:

MSDN Transact-SQL Reference for: LIKE

http://msdn2.microsoft.com/en-us/library/ms179859.aspx|||

FitzFarseer wrote:

select * from DOCU_hlp.dbo.HTML a1 where a1.ID=100009 AND a1.HTML LIKE '%89D4%' ;

gives me the following error:
Msg 8116, Level 16, State 1, Line 1
Argument data type varchar is invalid for argument 2 of like function.

I think this is a result of trying to use like on a binary column. Syntactically, it is correct.sql

Question about INSERT

Hi,

I have a table with columns X, Y, and Z. X is the primary key and is an integer that is set to auto-increment whenever a new row is added. Y and Z are pieces of information which are not necessarily unique to any given row, so in order to get the row I just added, I really need to know X.

So when I insert a new row, I would like to somehow have X returned to me, so that I can then use it as a pointer since in my application a row that is just added must often be modified very shortly afterwards.

Is there a call which will do this? Right now I am using:

sqlAdapter.InsertCommand.ExecuteNonQuery();

Which just returns the number of the rows affected, not the actual row, or its primary key.

Thanks,

Hi,

Here is a good example that shows you how to do it: http://davidhayden.com/blog/dave/archive/2006/02/16/2803.aspx

Greetz,

Geert

Geert Verhoeven
Consultant @. Ausy Belgium

My Personal Blog

|||

Try using ExecuteScalar() instead of ExecuteNonQuery() with an appropriate Insert Command String illustrated below. The code just illustrates the basic need as per the scenario in the Question and may not be fully functional.

int newX = 0;
string strInsertCommandText = "INSERT INTO MyTable (Y, Z) VALUES (@.Y, @.Z); SELECT CAST(scope_identity() AS int)";
sqlAdapter.InsertCommand.CommandType = CommandType.Text;
sqlAdapter.InsertCommand.CommandText = strInsertCommandText;

//Set the Parameter Values here.
sqlAdapter.InsertCommand.Parameters.Add("@.Y", SqlDbType.VarChar);
sqlAdapter.InsertCommand.Paramaters["@.Y"].Value = "Y Value";
sqlAdapter.InsertCommand.Parameters.Add("@.Z", SqlDbType.VarChar);
sqlAdapter.InsertCommand.Paramaters["@.Z"].Value = "Z Value";

//This line inserts a new record into the table and returns the generated primary key for the new record.
newX = (Int32)sqlAdapter.InsertCommand.ExecuteScalar();

Question about getting the latest identity field in a specific table

Dear All,

Suppose in the program a record is added to a table whose
primary key is a identity field. If I really want to get the lastest
value for that field after the insertion, is it the best way to use
IDENT_CURRENT() to obtain this value?

Thanks for your kind attention

Yours faithfully,
BennyI would rather use SCOPE_IDENTITY() or @.@.IDENTITY depending on the
requirements. SQL Server Books Online states that, IDENT_CURRENT is similar
to the 2000 identity functions SCOPE_IDENTITY and @.@.IDENTITY. All three
functions return last-generated identity values. However, the scope and
session on which 'last' is defined in each of these functions differ.

- IDENT_CURRENT returns the last identity value generated for a specific
table in any session and any scope.
- @.@.IDENTITY returns the last identity value generated for any table in the
current session, across all scopes.
- SCOPE_IDENTITY returns the last identity value generated for any table in
the current session and the current scope.

--
- Anith
( Please reply to newsgroups only )|||Anith Sen (anith@.bizdatasolutions.com) writes:
> - IDENT_CURRENT returns the last identity value generated for a specific
> table in any session and any scope.

One important thing to clarify here is that IDENT_CURRENT() can be affected
by insertions by other processes, where as scope_identity and @.@.identity
cannot.

Thus, ident_current() is rarely the function you should call in application
code.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

Question about database design and primary keys

Hi!
I have seen two approaches to primary keys. First one - and it is likedefault - is to use surrogate key as primary key. For each table I willcreate some autonumeric field hat cannot be changed once it has value.Some materials refer to this key also as technical primary key. Idesign my databases this way usually.
The other approach is to create primary key of fields that make primarykey on database logical model. This approach is not so popular and hassome side effects like a little bit clumpsy looking joins andunconvenient use in applications.
Question: What is the main idea behind second approach? Or how explain their preference database designers who are using second approach?
If a natural key can be used as the primary key, even if this is a composite key made up of a number of fields, I would generally use itonly when I need to later synchronize the data with a database that would not know about my autonumber key. I generally use the surrogate key.|||Synchronization is not so deep in point that I'm looking for. Maybe there is no point at all. :)
But... Even if you are using surrogate key you have unique constraintor index on natural primary key fields. So you can use it whensynchronizing.
|||Perhaps, but in the case I was thinking of, the table involved will be wiped out and recreated periodically, so any use of the surrogate key will not be helpful.|||It refers to somekind of a temporary table/temporary data solution. Butmy question is about "usual" tables. I just took over one system wherethis kind of approach is used and I'm not very sure I want to modify~140 tables and ~400 stored procedures. Just trying to understand whathad previous programmer in his mind. :)
|||This topic generates lots of debate. If you search Google for:

"Natural Key" "Surrogate Key"

you will see a lot of the arguments for and against eachapproach. Joe Celko is a major proponent of the philosophy thatproperly normalized and constructed databases should use natural keys.
I generally use surrogate keys. I have yet to read anything onthis topic that has swayed me to believe that natural keys arebetter. The only advantage I have seen discussed is purelyacademic -- that it is the "right" way to do things.
That being said -- is your current database structure causing youproblems? Personally I would just leave well enough alone.

|||No problems with database, I just was curious about pros of natural keys approach as it means more processing usually (complex primary key) on joins and it isnot so convenient to use in web applications.

question about cascading deletes

Hi, I'm using SQL server 2000, and I have set up two tables, table A
and table B. Table A and B have a foreign key constraint such that if
an entry is deleted in table A, then all the entries in table B
associated with that entry are deleted as well. I imagine that I can
find out the number of records that are deleted in table A when I
execute the SQL, but is there an easy way of determining the number of
records that will be deleted in table B?

Thanks,
Dan"Dan" <enjoybears@.sbcglobal.net> wrote in message
news:8c475c1a.0310241218.3b49498c@.posting.google.c om...
> Hi, I'm using SQL server 2000, and I have set up two tables, table A
> and table B. Table A and B have a foreign key constraint such that if
> an entry is deleted in table A, then all the entries in table B
> associated with that entry are deleted as well. I imagine that I can
> find out the number of records that are deleted in table A when I
> execute the SQL, but is there an easy way of determining the number of
> records that will be deleted in table B?
> Thanks,
> Dan

When you execute the delete statement, @.@.ROWCOUNT will have the number of
rows deleted from A, but there is no similar way to find out the rows
deleted from B. You could query how many rows will be affected by your
delete criteria before deleting, but that may not be reliable - if the
system is busy, the number of rows could change between your SELECT and your
DELETE queries.

One possible approach is a trigger on the child table (B) which stores
@.@.ROWCOUNT in a log table:

create trigger dbo.MyTrigger
on dbo.MyChildTable
after delete
as
declare @.i int
set @.i = @.@.ROWCOUNT
if @.i > 0
insert into dbo.MyLogTable (TableName, RowsDeleted)
values ('MyChildTable', @.i)

Simon

Question about Aliasing?

Heya,

While reading about aliasing using two different references, one author states that the aliasing is annotated using the key AS like in:
select...
FROM Emp as E1;

but in the other reference, the author states the key AS is not necessary, so the above query can be written instead as follows:
select...
FROM Emp E1

i am just wondering which one is correct? what MySQL/Oracle supports?

Many thankswhich one is correct depends on which database you're using

off the top of my head i can't remember which one requires AS (as opposed to it being optional)

besides, the sql reads better with AS, so i always throw it in, consequently i never get the syntax error when it's missing

helps?|||It works either way in Oracle or SQL Server. I'm not sure about mySQL. You're always safe using AS.

Question about "Foreign key constraint"

I have two tables "Courses" ( with primary key "courseID) and "Prerequisties"
(with the foreign key "courseID"), when I want Insert new record into
"Prerequisties" table. It always complains :
"Insert statement conflicited with column FOREIGN KEY CONSTRAINT
FK_Prerequisties_Courses. The conflicit occured in Database table 'Courses',
colume 'courseID' ".
I used similar tables and foreign key, they worked well. But just not for
above tables. Can anyone please tell me from where should I start to fix the
issue. Thanks.
The tables should be set up as follows:
Courses
CourseID PrimaryKey
Prerequisites
PreReqID PrimaryKey
CourseID ForeignKey REFERENCES Courses(CourseID)
If you have already set up your ForeignKey in the Prerequisites table, then
you are attempting to insert rows with a CourseID that does not exist in the
Courses table.
If you have your ForeignKey in the Courses table, then you have it backwards
and should create it as shown above.
HTH
Rick Sawtell
MCT, MCSD, MCDBA
"LaoJ" <LaoJ@.discussions.microsoft.com> wrote in message
news:F7E86C9A-F643-493F-9CA4-80C0698D4CCF@.microsoft.com...
> I have two tables "Courses" ( with primary key "courseID) and
"Prerequisties"
> (with the foreign key "courseID"), when I want Insert new record into
> "Prerequisties" table. It always complains :
> "Insert statement conflicited with column FOREIGN KEY CONSTRAINT
> FK_Prerequisties_Courses. The conflicit occured in Database table
'Courses',
> colume 'courseID' ".
> I used similar tables and foreign key, they worked well. But just not for
> above tables. Can anyone please tell me from where should I start to fix
the
> issue. Thanks.
>
|||Actually, a prerequisite is usually a relationship between two courses.
There will be two CourseID columns (one for the required course and one for
the requiring course) and therefore two FKs.
"Rick Sawtell" <ricksawtell@.hotmail.com> wrote in message
news:uTSMTT6iEHA.2660@.TK2MSFTNGP15.phx.gbl...
> The tables should be set up as follows:
> Courses
> CourseID PrimaryKey
> Prerequisites
> PreReqID PrimaryKey
> CourseID ForeignKey REFERENCES Courses(CourseID)
> If you have already set up your ForeignKey in the Prerequisites table,
then
> you are attempting to insert rows with a CourseID that does not exist in
the
> Courses table.
> If you have your ForeignKey in the Courses table, then you have it
backwards[vbcol=seagreen]
> and should create it as shown above.
> HTH
> Rick Sawtell
> MCT, MCSD, MCDBA
>
> "LaoJ" <LaoJ@.discussions.microsoft.com> wrote in message
> news:F7E86C9A-F643-493F-9CA4-80C0698D4CCF@.microsoft.com...
> "Prerequisties"
> 'Courses',
for
> the
>
|||Thanks, Rick,
Actually for thr "Prerequisites" table
" PreReqID" is also a foregin key, there is no PrimaryKey for
"Prerequisites" table.
I used SQL server "Enterprise Manager" diagram to setup the relationship
between two tables. So I don't know how to add " CourseID ForeignKey
REFERENCES Courses(CourseID)", could you please point out.
"Rick Sawtell" wrote:

> The tables should be set up as follows:
> Courses
> CourseID PrimaryKey
> Prerequisites
> PreReqID PrimaryKey
> CourseID ForeignKey REFERENCES Courses(CourseID)
> If you have already set up your ForeignKey in the Prerequisites table, then
> you are attempting to insert rows with a CourseID that does not exist in the
> Courses table.
> If you have your ForeignKey in the Courses table, then you have it backwards
> and should create it as shown above.
> HTH
> Rick Sawtell
> MCT, MCSD, MCDBA
>
> "LaoJ" <LaoJ@.discussions.microsoft.com> wrote in message
> news:F7E86C9A-F643-493F-9CA4-80C0698D4CCF@.microsoft.com...
> "Prerequisties"
> 'Courses',
> the
>
>
|||Open up the Query Analyzer.
Find the first table by using the drill-downs on the left side.
Right-click the table and choose Script Object to Clipboard as CREATE.
Paste that script here.
Right-click the next table and choose the same thing and paste it here as
well.
By having your CREATE TABLE commands, we will be better able to help you out
on this one.
Rick
"LaoJ" <LaoJ@.discussions.microsoft.com> wrote in message
news:6ED8C986-EF42-4E2F-A4A6-710DDC666ABC@.microsoft.com...[vbcol=seagreen]
> Thanks, Rick,
> Actually for thr "Prerequisites" table
> " PreReqID" is also a foregin key, there is no PrimaryKey for
> "Prerequisites" table.
> I used SQL server "Enterprise Manager" diagram to setup the relationship
> between two tables. So I don't know how to add " CourseID ForeignKey
> REFERENCES Courses(CourseID)", could you please point out.
>
> "Rick Sawtell" wrote:
then[vbcol=seagreen]
the[vbcol=seagreen]
backwards[vbcol=seagreen]
for[vbcol=seagreen]
fix[vbcol=seagreen]
|||"Rick Sawtell" wrote:
..> By having your CREATE TABLE commands, we will be better able to help you
out
> on this one.
My database has over 40 tables that all generated by using "Enterprise
Manager" , so there are no related Create table commands, all others working
fine except these two. Anyway thanks for help.
|||Sorry Rick, I misunderstood your e-mail, here is Create Table command:
CREATE TABLE [Courses] (
[CourseID] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[CourseName] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[CourseDesc] [varchar] (1000) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[tStamp] [datetime] NULL ,
[userId] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[show] [bit] NOT NULL CONSTRAINT [DF_Courses_show] DEFAULT (1),
CONSTRAINT [PK_Courses] PRIMARY KEY CLUSTERED
(
[CourseID]
) ON [PRIMARY]
) ON [PRIMARY]
GO
CREATE TABLE [CoursePreRequisites] (
[CourseID] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[PreReqID] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
CONSTRAINT [FK_CoursePreRequisites_Courses] FOREIGN KEY
(
[PreReqID]
) REFERENCES [Courses] (
[CourseID]
) ON DELETE CASCADE ON UPDATE CASCADE ,
CONSTRAINT [FK_CoursePreRequisites_PreRequisites] FOREIGN KEY
(
[PreReqID]
) REFERENCES [PreRequisites] (
[PreReqID]
) ON DELETE CASCADE ON UPDATE CASCADE
) ON [PRIMARY]
GO
CREATE TABLE [PreRequisites] (
[PreReqID] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[PreReqName] [char] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[PreReqDesc] [varchar] (200) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[tStamp] [datetime] NULL ,
[userId] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[show] [bit] NOT NULL CONSTRAINT [DF_PreRequisites_show] DEFAULT (1),
CONSTRAINT [PK_PreRequisites] PRIMARY KEY CLUSTERED
(
[PreReqID]
) ON [PRIMARY]
) ON [PRIMARY]
GO
Thanks again for help me !
"LaoJ" wrote:

>
> "Rick Sawtell" wrote:
> .> By having your CREATE TABLE commands, we will be better able to help you
> out
> My database has over 40 tables that all generated by using "Enterprise
> Manager" , so there are no related Create table commands, all others working
> fine except these two. Anyway thanks for help.
>
|||Up
Sorry Rick, I misunderstood your e-mail, here is Create Table command:
CREATE TABLE [Courses] (
[CourseID] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[CourseName] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[CourseDesc] [varchar] (1000) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[tStamp] [datetime] NULL ,
[userId] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[show] [bit] NOT NULL CONSTRAINT [DF_Courses_show] DEFAULT (1),
CONSTRAINT [PK_Courses] PRIMARY KEY CLUSTERED
(
[CourseID]
) ON [PRIMARY]
) ON [PRIMARY]
GO
CREATE TABLE [CoursePreRequisites] (
[CourseID] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[PreReqID] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
CONSTRAINT [FK_CoursePreRequisites_Courses] FOREIGN KEY
(
[PreReqID]
) REFERENCES [Courses] (
[CourseID]
) ON DELETE CASCADE ON UPDATE CASCADE ,
CONSTRAINT [FK_CoursePreRequisites_PreRequisites] FOREIGN KEY
(
[PreReqID]
) REFERENCES [PreRequisites] (
[PreReqID]
) ON DELETE CASCADE ON UPDATE CASCADE
) ON [PRIMARY]
GO
CREATE TABLE [PreRequisites] (
[PreReqID] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[PreReqName] [char] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[PreReqDesc] [varchar] (200) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[tStamp] [datetime] NULL ,
[userId] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[show] [bit] NOT NULL CONSTRAINT [DF_PreRequisites_show] DEFAULT (1),
CONSTRAINT [PK_PreRequisites] PRIMARY KEY CLUSTERED
(
[PreReqID]
) ON [PRIMARY]
) ON [PRIMARY]
GO
Thanks again for help me !
..
|||I'm not getting the same errors that you are. (I'm getting something else
weird going on in my system.)
When I run your script (I moved the courseprerequisites table to the end),
the tables generated fine. I added rows to the courses table and identicle
rows to the prerequisites table. The CoursePreRequisites however would
allow me to add rows that violated the first FK constraint, but not the
second. Strange. I'll have to delve a little deeper and see what is going
on.
The only thing I could suggest to you is the following:
1. Get rid of the PreRequisites table. Store all of your courses in the
Courses table and then just use two references from the PreRequisites table
back to the Courses table.
2. Put a Primary Key constraint on the CoursePreRequisites table that
encompasses both FK columns within it. Example:
ALTER TABLE CoursePreRequisites
ADD CONSTRAINT PK_CoursePreRequisites PRIMARY KEY (CourseID, PreReqID)
This will keep you from creating duplicate pre-reqs for a course.
Sorry I was not more helpful.
Rick

Question about "Foreign key constraint"

I have two tables "Courses" ( with primary key "courseID) and "Prerequisties
"
(with the foreign key "courseID"), when I want Insert new record into
"Prerequisties" table. It always complains :
"Insert statement conflicited with column FOREIGN KEY CONSTRAINT
FK_Prerequisties_Courses. The conflicit occured in Database table 'Courses',
colume 'courseID' ".
I used similar tables and foreign key, they worked well. But just not for
above tables. Can anyone please tell me from where should I start to fix the
issue. Thanks.The tables should be set up as follows:
Courses
CourseID PrimaryKey
Prerequisites
PreReqID PrimaryKey
CourseID ForeignKey REFERENCES Courses(CourseID)
If you have already set up your ForeignKey in the Prerequisites table, then
you are attempting to insert rows with a CourseID that does not exist in the
Courses table.
If you have your ForeignKey in the Courses table, then you have it backwards
and should create it as shown above.
HTH
Rick Sawtell
MCT, MCSD, MCDBA
"LaoJ" <LaoJ@.discussions.microsoft.com> wrote in message
news:F7E86C9A-F643-493F-9CA4-80C0698D4CCF@.microsoft.com...
> I have two tables "Courses" ( with primary key "courseID) and
"Prerequisties"
> (with the foreign key "courseID"), when I want Insert new record into
> "Prerequisties" table. It always complains :
> "Insert statement conflicited with column FOREIGN KEY CONSTRAINT
> FK_Prerequisties_Courses. The conflicit occured in Database table
'Courses',
> colume 'courseID' ".
> I used similar tables and foreign key, they worked well. But just not for
> above tables. Can anyone please tell me from where should I start to fix
the
> issue. Thanks.
>|||Actually, a prerequisite is usually a relationship between two courses.
There will be two CourseID columns (one for the required course and one for
the requiring course) and therefore two FKs.
"Rick Sawtell" <ricksawtell@.hotmail.com> wrote in message
news:uTSMTT6iEHA.2660@.TK2MSFTNGP15.phx.gbl...
> The tables should be set up as follows:
> Courses
> CourseID PrimaryKey
> Prerequisites
> PreReqID PrimaryKey
> CourseID ForeignKey REFERENCES Courses(CourseID)
> If you have already set up your ForeignKey in the Prerequisites table,
then
> you are attempting to insert rows with a CourseID that does not exist in
the
> Courses table.
> If you have your ForeignKey in the Courses table, then you have it
backwards
> and should create it as shown above.
> HTH
> Rick Sawtell
> MCT, MCSD, MCDBA
>
> "LaoJ" <LaoJ@.discussions.microsoft.com> wrote in message
> news:F7E86C9A-F643-493F-9CA4-80C0698D4CCF@.microsoft.com...
> "Prerequisties"
> 'Courses',
for[vbcol=seagreen]
> the
>|||Thanks, Rick,
Actually for thr "Prerequisites" table
" PreReqID" is also a foregin key, there is no PrimaryKey for
"Prerequisites" table.
I used SQL server "Enterprise Manager" diagram to setup the relationship
between two tables. So I don't know how to add " CourseID ForeignKey
REFERENCES Courses(CourseID)", could you please point out.
"Rick Sawtell" wrote:

> The tables should be set up as follows:
> Courses
> CourseID PrimaryKey
> Prerequisites
> PreReqID PrimaryKey
> CourseID ForeignKey REFERENCES Courses(CourseID)
> If you have already set up your ForeignKey in the Prerequisites table, the
n
> you are attempting to insert rows with a CourseID that does not exist in t
he
> Courses table.
> If you have your ForeignKey in the Courses table, then you have it backwar
ds
> and should create it as shown above.
> HTH
> Rick Sawtell
> MCT, MCSD, MCDBA
>
> "LaoJ" <LaoJ@.discussions.microsoft.com> wrote in message
> news:F7E86C9A-F643-493F-9CA4-80C0698D4CCF@.microsoft.com...
> "Prerequisties"
> 'Courses',
> the
>
>|||Open up the Query Analyzer.
Find the first table by using the drill-downs on the left side.
Right-click the table and choose Script Object to Clipboard as CREATE.
Paste that script here.
Right-click the next table and choose the same thing and paste it here as
well.
By having your CREATE TABLE commands, we will be better able to help you out
on this one.
Rick
"LaoJ" <LaoJ@.discussions.microsoft.com> wrote in message
news:6ED8C986-EF42-4E2F-A4A6-710DDC666ABC@.microsoft.com...[vbcol=seagreen]
> Thanks, Rick,
> Actually for thr "Prerequisites" table
> " PreReqID" is also a foregin key, there is no PrimaryKey for
> "Prerequisites" table.
> I used SQL server "Enterprise Manager" diagram to setup the relationship
> between two tables. So I don't know how to add " CourseID ForeignKey
> REFERENCES Courses(CourseID)", could you please point out.
>
> "Rick Sawtell" wrote:
>
then[vbcol=seagreen]
the[vbcol=seagreen]
backwards[vbcol=seagreen]
for[vbcol=seagreen]
fix[vbcol=seagreen]|||"Rick Sawtell" wrote:
.> By having your CREATE TABLE commands, we will be better able to help you
out
> on this one.
My database has over 40 tables that all generated by using "Enterprise
Manager" , so there are no related Create table commands, all others working
fine except these two. Anyway thanks for help.|||Sorry Rick, I misunderstood your e-mail, here is Create Table command:
CREATE TABLE [Courses] (
[CourseID] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
,
[CourseName] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
NULL ,
[CourseDesc] [varchar] (1000) COLLATE SQL_Latin1_General_CP1_CI_AS N
OT NULL ,
[tStamp] [datetime] NULL ,
[userId] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[show] [bit] NOT NULL CONSTRAINT [DF_Courses_show] DEFAULT (1),
CONSTRAINT [PK_Courses] PRIMARY KEY CLUSTERED
(
[CourseID]
) ON [PRIMARY]
) ON [PRIMARY]
GO
CREATE TABLE [CoursePreRequisites] (
[CourseID] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
,
[PreReqID] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
,
CONSTRAINT [FK_CoursePreRequisites_Courses] FOREIGN KEY
(
[PreReqID]
) REFERENCES [Courses] (
[CourseID]
) ON DELETE CASCADE ON UPDATE CASCADE ,
CONSTRAINT & #91;FK_CoursePreRequisites_PreRequisites
] FOREIGN KEY
(
[PreReqID]
) REFERENCES [PreRequisites] (
[PreReqID]
) ON DELETE CASCADE ON UPDATE CASCADE
) ON [PRIMARY]
GO
CREATE TABLE [PreRequisites] (
[PreReqID] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
,
[PreReqName] [char] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NU
LL ,
[PreReqDesc] [varchar] (200) COLLATE SQL_Latin1_General_CP1_CI_AS NU
LL ,
[tStamp] [datetime] NULL ,
[userId] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[show] [bit] NOT NULL CONSTRAINT [DF_PreRequisites_show] DEFAULT
(1),
CONSTRAINT [PK_PreRequisites] PRIMARY KEY CLUSTERED
(
[PreReqID]
) ON [PRIMARY]
) ON [PRIMARY]
GO
Thanks again for help me !
"LaoJ" wrote:

>
> "Rick Sawtell" wrote:
> .> By having your CREATE TABLE commands, we will be better able to help yo
u
> out
> My database has over 40 tables that all generated by using "Enterprise
> Manager" , so there are no related Create table commands, all others worki
ng
> fine except these two. Anyway thanks for help.
>|||Up
Sorry Rick, I misunderstood your e-mail, here is Create Table command:
CREATE TABLE [Courses] (
[CourseID] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
,
[CourseName] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
NULL ,
[CourseDesc] [varchar] (1000) COLLATE SQL_Latin1_General_CP1_CI_AS N
OT NULL ,
[tStamp] [datetime] NULL ,
[userId] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[show] [bit] NOT NULL CONSTRAINT [DF_Courses_show] DEFAULT (1),
CONSTRAINT [PK_Courses] PRIMARY KEY CLUSTERED
(
[CourseID]
) ON [PRIMARY]
) ON [PRIMARY]
GO
CREATE TABLE [CoursePreRequisites] (
[CourseID] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
,
[PreReqID] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
,
CONSTRAINT [FK_CoursePreRequisites_Courses] FOREIGN KEY
(
[PreReqID]
) REFERENCES [Courses] (
[CourseID]
) ON DELETE CASCADE ON UPDATE CASCADE ,
CONSTRAINT & #91;FK_CoursePreRequisites_PreRequisites
] FOREIGN KEY
(
[PreReqID]
) REFERENCES [PreRequisites] (
[PreReqID]
) ON DELETE CASCADE ON UPDATE CASCADE
) ON [PRIMARY]
GO
CREATE TABLE [PreRequisites] (
[PreReqID] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
,
[PreReqName] [char] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NU
LL ,
[PreReqDesc] [varchar] (200) COLLATE SQL_Latin1_General_CP1_CI_AS NU
LL ,
[tStamp] [datetime] NULL ,
[userId] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[show] [bit] NOT NULL CONSTRAINT [DF_PreRequisites_show] DEFAULT
(1),
CONSTRAINT [PK_PreRequisites] PRIMARY KEY CLUSTERED
(
[PreReqID]
) ON [PRIMARY]
) ON [PRIMARY]
GO
Thanks again for help me !
.|||I'm not getting the same errors that you are. (I'm getting something else
weird going on in my system.)
When I run your script (I moved the courseprerequisites table to the end),
the tables generated fine. I added rows to the courses table and identicle
rows to the prerequisites table. The CoursePreRequisites however would
allow me to add rows that violated the first FK constraint, but not the
second. Strange. I'll have to delve a little deeper and see what is going
on.
The only thing I could suggest to you is the following:
1. Get rid of the PreRequisites table. Store all of your courses in the
Courses table and then just use two references from the PreRequisites table
back to the Courses table.
2. Put a Primary Key constraint on the CoursePreRequisites table that
encompasses both FK columns within it. Example:
ALTER TABLE CoursePreRequisites
ADD CONSTRAINT PK_CoursePreRequisites PRIMARY KEY (CourseID, PreReqID)
This will keep you from creating duplicate pre-reqs for a course.
Sorry I was not more helpful.
Rick

question abot primary key

how can I set my primary key to star again from 1?? for example, if I had a
table with some rows in it and I deleted all that rows, next time when I
insert a new row in that table, primary key starts from last primary key of
previous rows, but I want it to start again from 1.

is that possible??

thanks!I think you are talking about an IDENTITY column, which is not the same as a
Primary Key (although IDENTITY is used as a surrogate key).

DBCC CHECKIDENT ('TableName', RESEED,0)

--
David Portas
SQL Server MVP
--|||>> how can I set my primary key to start again from 1? <<

Please read a book on RDBMS; you have absolutely no idea what you are
doing.

Let's get back to the basics of an RDBMS. Rows are not records; fields
are not columns; tables are not files; there is no sequential access
or ordering in an RDBMS, so "first", "next" and "last" are totally
meaningless.

A primary key **BY DEFINITION** is a subset of attributes (columns).
Keys do not "start" anywhere. Each entity in the data model has a
unique primary key by its nature; it is not assigned by the PHYSICAL
storage used to hold the PHYSICAL representation of the DBMS.

If you had a 1950's magnetic tape file, then your question would make
sense. They start at record #1 each time you hang a new magnetic tape
on the drive, which is obviously your mental model. This is the
difference between a VIN number and a parking space number.

question

anyone know if creating an index on a table that has no
primary key and no indexes it will defrag the table of
fragmentation... if so how does it do that?Creating a clustered index will reorg data pages. When you add a primary
key on a table with no clustered index, SQL Server will create a unique
clustered index to support the constraint.
--
Hope this helps.
Dan Guzman
SQL Server MVP
":)" <anonymous@.discussions.microsoft.com> wrote in message
news:5cfb01c3e5a5$de1ccda0$a401280a@.phx.gbl...
> anyone know if creating an index on a table that has no
> primary key and no indexes it will defrag the table of
> fragmentation... if so how does it do that?|||I know Dan knows this but just to clarify for novices...
you can specify a NC index for a PK. However it will default to clustered if
there isn't already a clustered index...
--
Brian
"Dan Guzman" <danguzman@.nospam-earthlink.net> wrote in message
news:ux6lLja5DHA.2776@.TK2MSFTNGP09.phx.gbl...
> Creating a clustered index will reorg data pages. When you add a primary
> key on a table with no clustered index, SQL Server will create a unique
> clustered index to support the constraint.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> ":)" <anonymous@.discussions.microsoft.com> wrote in message
> news:5cfb01c3e5a5$de1ccda0$a401280a@.phx.gbl...
> > anyone know if creating an index on a table that has no
> > primary key and no indexes it will defrag the table of
> > fragmentation... if so how does it do that?
>