Antivirus install to db server: primary

Showing posts with label primary. Show all posts

Friday, March 30, 2012

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 secondary file

Hi,
i read about primary and secondary files, but not everything is obvious to
me.
If i define a database with a primary + log file and 3 secondary files:
1) do i have to also define for each secondary file a log file?
2) when i create a table, where is it placed (in the primary, in one of the
secondary files)? Who decides this?
3) same question about data
Thanks
Ben
1) No, you don't need to have a 1:1 relationship between data and log files.
2) If you don't specify a filegroup when you create a table or index, then
it goes into the default filegroup. You can change the default filegroup.
3) See #2. The data go into the table and the table is in whichever
filegroup it was created.
Best practices:
Keep the primary data and log files small. Do not place user objects in the
primary filegroup. Rather, create a separate filegroup and place the
objects there. Change the default filegroup to be something other than the
primary fielgroup.
Note: You can delete secondary data and log files. You cannot delete the
primary ones (file numbers 1 and 2). Thus, to migrate to another file, you
can add files to the user-defined filegroups, empty the original file and
the drop that file. In order to migrate primary files, you need to detach
the DB and move those files. Keeping them small mitigates the downtime.
HTH
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau
"Ben" <b@.en.sd> wrote in message
news:%23QuvGZdfHHA.1816@.TK2MSFTNGP06.phx.gbl...
Hi,
i read about primary and secondary files, but not everything is obvious to
me.
If i define a database with a primary + log file and 3 secondary files:
1) do i have to also define for each secondary file a log file?
2) when i create a table, where is it placed (in the primary, in one of the
secondary files)? Who decides this?
3) same question about data
Thanks
Ben
|||Thanks
"Tom Moreau" <tom@.dont.spam.me.cips.ca> schreef in bericht
news:elNsijdfHHA.4704@.TK2MSFTNGP06.phx.gbl...
> 1) No, you don't need to have a 1:1 relationship between data and log
> files.
> 2) If you don't specify a filegroup when you create a table or index, then
> it goes into the default filegroup. You can change the default filegroup.
> 3) See #2. The data go into the table and the table is in whichever
> filegroup it was created.
> Best practices:
> Keep the primary data and log files small. Do not place user objects in
> the
> primary filegroup. Rather, create a separate filegroup and place the
> objects there. Change the default filegroup to be something other than
> the
> primary fielgroup.
> Note: You can delete secondary data and log files. You cannot delete the
> primary ones (file numbers 1 and 2). Thus, to migrate to another file,
> you
> can add files to the user-defined filegroups, empty the original file and
> the drop that file. In order to migrate primary files, you need to detach
> the DB and move those files. Keeping them small mitigates the downtime.
> HTH
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
> SQL Server MVP
> Toronto, ON Canada
> https://mvp.support.microsoft.com/profile/Tom.Moreau
>
> "Ben" <b@.en.sd> wrote in message
> news:%23QuvGZdfHHA.1816@.TK2MSFTNGP06.phx.gbl...
> Hi,
> i read about primary and secondary files, but not everything is obvious to
> me.
> If i define a database with a primary + log file and 3 secondary files:
> 1) do i have to also define for each secondary file a log file?
> 2) when i create a table, where is it placed (in the primary, in one of
> the
> secondary files)? Who decides this?
> 3) same question about data
> Thanks
> Ben
>

question about secondary file

Hi,
i read about primary and secondary files, but not everything is obvious to
me.
If i define a database with a primary + log file and 3 secondary files:
1) do i have to also define for each secondary file a log file?
2) when i create a table, where is it placed (in the primary, in one of the
secondary files)? Who decides this?
3) same question about data
Thanks
Ben1) No, you don't need to have a 1:1 relationship between data and log files.
2) If you don't specify a filegroup when you create a table or index, then
it goes into the default filegroup. You can change the default filegroup.
3) See #2. The data go into the table and the table is in whichever
filegroup it was created.
Best practices:
Keep the primary data and log files small. Do not place user objects in the
primary filegroup. Rather, create a separate filegroup and place the
objects there. Change the default filegroup to be something other than the
primary fielgroup.
Note: You can delete secondary data and log files. You cannot delete the
primary ones (file numbers 1 and 2). Thus, to migrate to another file, you
can add files to the user-defined filegroups, empty the original file and
the drop that file. In order to migrate primary files, you need to detach
the DB and move those files. Keeping them small mitigates the downtime.
HTH
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau
"Ben" <b@.en.sd> wrote in message
news:%23QuvGZdfHHA.1816@.TK2MSFTNGP06.phx.gbl...
Hi,
i read about primary and secondary files, but not everything is obvious to
me.
If i define a database with a primary + log file and 3 secondary files:
1) do i have to also define for each secondary file a log file?
2) when i create a table, where is it placed (in the primary, in one of the
secondary files)? Who decides this?
3) same question about data
Thanks
Ben|||Thanks
"Tom Moreau" <tom@.dont.spam.me.cips.ca> schreef in bericht
news:elNsijdfHHA.4704@.TK2MSFTNGP06.phx.gbl...
> 1) No, you don't need to have a 1:1 relationship between data and log
> files.
> 2) If you don't specify a filegroup when you create a table or index, then
> it goes into the default filegroup. You can change the default filegroup.
> 3) See #2. The data go into the table and the table is in whichever
> filegroup it was created.
> Best practices:
> Keep the primary data and log files small. Do not place user objects in
> the
> primary filegroup. Rather, create a separate filegroup and place the
> objects there. Change the default filegroup to be something other than
> the
> primary fielgroup.
> Note: You can delete secondary data and log files. You cannot delete the
> primary ones (file numbers 1 and 2). Thus, to migrate to another file,
> you
> can add files to the user-defined filegroups, empty the original file and
> the drop that file. In order to migrate primary files, you need to detach
> the DB and move those files. Keeping them small mitigates the downtime.
> HTH
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
> SQL Server MVP
> Toronto, ON Canada
> https://mvp.support.microsoft.com/profile/Tom.Moreau
>
> "Ben" <b@.en.sd> wrote in message
> news:%23QuvGZdfHHA.1816@.TK2MSFTNGP06.phx.gbl...
> Hi,
> i read about primary and secondary files, but not everything is obvious to
> me.
> If i define a database with a primary + log file and 3 secondary files:
> 1) do i have to also define for each secondary file a log file?
> 2) when i create a table, where is it placed (in the primary, in one of
> the
> secondary files)? Who decides this?
> 3) same question about data
> Thanks
> Ben
>sql

question about secondary file

Hi,
i read about primary and secondary files, but not everything is obvious to
me.
If i define a database with a primary + log file and 3 secondary files:
1) do i have to also define for each secondary file a log file?
2) when i create a table, where is it placed (in the primary, in one of the
secondary files)? Who decides this?
3) same question about data
Thanks
Ben1) No, you don't need to have a 1:1 relationship between data and log files.
2) If you don't specify a filegroup when you create a table or index, then
it goes into the default filegroup. You can change the default filegroup.
3) See #2. The data go into the table and the table is in whichever
filegroup it was created.
Best practices:
Keep the primary data and log files small. Do not place user objects in the
primary filegroup. Rather, create a separate filegroup and place the
objects there. Change the default filegroup to be something other than the
primary fielgroup.
Note: You can delete secondary data and log files. You cannot delete the
primary ones (file numbers 1 and 2). Thus, to migrate to another file, you
can add files to the user-defined filegroups, empty the original file and
the drop that file. In order to migrate primary files, you need to detach
the DB and move those files. Keeping them small mitigates the downtime.
HTH
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau
"Ben" <b@.en.sd> wrote in message
news:%23QuvGZdfHHA.1816@.TK2MSFTNGP06.phx.gbl...
Hi,
i read about primary and secondary files, but not everything is obvious to
me.
If i define a database with a primary + log file and 3 secondary files:
1) do i have to also define for each secondary file a log file?
2) when i create a table, where is it placed (in the primary, in one of the
secondary files)? Who decides this?
3) same question about data
Thanks
Ben|||Thanks
"Tom Moreau" <tom@.dont.spam.me.cips.ca> schreef in bericht
news:elNsijdfHHA.4704@.TK2MSFTNGP06.phx.gbl...
> 1) No, you don't need to have a 1:1 relationship between data and log
> files.
> 2) If you don't specify a filegroup when you create a table or index, then
> it goes into the default filegroup. You can change the default filegroup.
> 3) See #2. The data go into the table and the table is in whichever
> filegroup it was created.
> Best practices:
> Keep the primary data and log files small. Do not place user objects in
> the
> primary filegroup. Rather, create a separate filegroup and place the
> objects there. Change the default filegroup to be something other than
> the
> primary fielgroup.
> Note: You can delete secondary data and log files. You cannot delete the
> primary ones (file numbers 1 and 2). Thus, to migrate to another file,
> you
> can add files to the user-defined filegroups, empty the original file and
> the drop that file. In order to migrate primary files, you need to detach
> the DB and move those files. Keeping them small mitigates the downtime.
> HTH
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
> SQL Server MVP
> Toronto, ON Canada
> https://mvp.support.microsoft.com/profile/Tom.Moreau
>
> "Ben" <b@.en.sd> wrote in message
> news:%23QuvGZdfHHA.1816@.TK2MSFTNGP06.phx.gbl...
> Hi,
> i read about primary and secondary files, but not everything is obvious to
> me.
> If i define a database with a primary + log file and 3 secondary files:
> 1) do i have to also define for each secondary file a log file?
> 2) when i create a table, where is it placed (in the primary, in one of
> the
> secondary files)? Who decides this?
> 3) same question about data
> Thanks
> Ben
>

Monday, March 26, 2012

Question about Primary Keys in MSDE

I upsized an Access database into MSDE to begin scalling it up to SQL Server.
I have a curious problem: my queries don't return any results if the table has a Access primary key before they are upsized.
I've deleted and re-created the database to check this, and if I use Access to remove the primary key before upsizing, the queries will return a result; if I re-instate the primary key and upsize again, the queries return nothing.
(This is true regardless of using VS Net, Web Matrix, or by hand...)
I'm a newbie with MSDE and with upsizing from Access. Is this expected -- or have I maybe done something wrong, again?
- Tinker
Nevermind... The whole MSDE-Access-Upsizing thing just got to be too much and I switched over to SQL Server 2000. That solved EVERYthing.
Tinkersql

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

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

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

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:

Friday, March 23, 2012

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

Wednesday, March 21, 2012

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();

Friday, March 9, 2012

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.

Wednesday, March 7, 2012

Question about a Select Statement

Hello,

I'm quite new to SQL and so my question may sound a bit strange.

Let me say I have the primary Keys 2049, 2090,4080,7803

For those 4 primary Keys I want to Select the rows from a table. How ca I do this in 1 SQL-Statement.

My way until now: Select [column-List] From tab1 Where ID=2049;
Select [column-list] From tab1 Where ID = 2090
...

So I split up into 4 Statements

There MUST be a way doing this in one statement!!!!!

Can you help me?

Kind regardsTry this:

where ID in (2049,2090,4080,7803)

Saturday, February 25, 2012

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

Friday, March 30, 2012

Question about self referencing delete

question about secondary file

question about secondary file

question about secondary file

Monday, March 26, 2012

Question about Primary Keys in MSDE

Question about Primary key

Question about Primary Key

Question about Primary Key

question about primary key

question about primary key

Question about Primary Key

question about primary key

Question about Primary Key

Question about Primary Key

Friday, March 23, 2012

question about like

Wednesday, March 21, 2012

Question about INSERT

Friday, March 9, 2012

Question about database design and primary keys

Wednesday, March 7, 2012

Question about a Select Statement

Saturday, February 25, 2012

Question about "Foreign key constraint"

Question about "Foreign key constraint"

Antivirus install to db server

Blog Archive

About Me