Referencial Integrity Constraints Questions ?

Hi, Group
Please let me know how can I do the following:
Will prevent the user from deleting an entry if the value is used in a
foreign key.
Best regard
MarioMario,
If you have Foreign key setup, you should not be able to delete a parent
before deleting the child. Below is an example showing FK in effect.
e.g.
create table t1(i int primary key, j int)
create table t2(k int primary key,i int foreign key references t1(i),l int)
go
insert t1 values(1,1)
insert t2 values(2,1,2)
go
--this delete will fail
--due to FK constraint
delete t1
where i=1
go
drop table t2,t1
go
-oj
http://www.rac4sql.net
"Mario Reiley" <mreiley@.cantv.net> wrote in message
news:%23uVCIbV2DHA.1736@.TK2MSFTNGP09.phx.gbl...

quote:

---

> Hi, Group
> Please let me know how can I do the following:
> Will prevent the user from deleting an entry if the value is used in a
> foreign key.
> Best regard
> Mario
>

---

|||OJ describes the default behavour of FK constraints, which seems to be the
behavour you want... You may add the cascade option to the constraint, which
will cause the child records to be automatically deleted when the parent row
is deleted. (Just an FYI)
Wayne Snyder, MCDBA, SQL Server MVP
Computer Education Services Corporation (CESC), Charlotte, NC
www.computeredservices.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Mario Reiley" <mreiley@.cantv.net> wrote in message
news:%23uVCIbV2DHA.1736@.TK2MSFTNGP09.phx.gbl...

quote:

---

> Hi, Group
> Please let me know how can I do the following:
> Will prevent the user from deleting an entry if the value is used in a
> foreign key.
> Best regard
> Mario
>

---

|||OK my Friends but what can i do then and thanks so much for yours response.
Best Regard
Mario
"Mario Reiley" <mreiley@.cantv.net> escribi en el mensaje
news:%23uVCIbV2DHA.1736@.TK2MSFTNGP09.phx.gbl...

quote:

---

> Hi, Group
> Please let me know how can I do the following:
> Will prevent the user from deleting an entry if the value is used in a
> foreign key.
> Best regard
> Mario
>

---

|||OK, let me try explaining.
I have two tables:
Personal (Is my Parent table)
Profession ( Is my Child table)
In Parent is: ProfessionId field.
The thing is how Can I void Delete a row in Profession table when there is a
value in Personal table.
Example:
IF the user not put the profession in the Personal record then no problem
the profession row Is OK (Delete) else the row profession can't be deleting.
Note: Sorry my English.
Best regard
MArio
"Mario Reiley" <mreiley@.cantv.net> escribi en el mensaje
news:%23uVCIbV2DHA.1736@.TK2MSFTNGP09.phx.gbl...

quote:

---

> Hi, Group
> Please let me know how can I do the following:
> Will prevent the user from deleting an entry if the value is used in a
> foreign key.
> Best regard
> Mario
>

---

|||Here's how to add a the relationship.
ALTER TABLE Personal
ADD FOREIGN KEY (ProfessionId)
REFERENCES Profession (ProfessionId)
ON DELETE NO ACTION
"Mario Reiley" <mreiley@.cantv.net> wrote in message
news:%23gTfxJd2DHA.2620@.TK2MSFTNGP09.phx.gbl...

quote:

---

> OK, let me try explaining.
>
> I have two tables:
>
> Personal (Is my Parent table)
> Profession ( Is my Child table)
>
> In Parent is: ProfessionId field.
>
> The thing is how Can I void Delete a row in Profession table when there is

---

a

quote:

---

> value in Personal table.
>
> Example:
>
> IF the user not put the profession in the Personal record then no problem
> the profession row Is OK (Delete) else the row profession can't be

---

deleting.

quote:

---

>
> Note: Sorry my English.
>
> Best regard
> MArio
> "Mario Reiley" <mreiley@.cantv.net> escribi en el mensaje
> news:%23uVCIbV2DHA.1736@.TK2MSFTNGP09.phx.gbl...
>

---

Referencial Integrity Constraints Questions ?

Hi, Group
Please let me know how can I do the following:
Will prevent the user from deleting an entry if the value is used in a
foreign key.
Best regard
MarioMario,
If you have Foreign key setup, you should not be able to delete a parent
before deleting the child. Below is an example showing FK in effect.
e.g.
create table t1(i int primary key, j int)
create table t2(k int primary key,i int foreign key references t1(i),l int)
go
insert t1 values(1,1)
insert t2 values(2,1,2)
go
--this delete will fail
--due to FK constraint
delete t1
where i=1
go
drop table t2,t1
go
-oj
http://www.rac4sql.net
"Mario Reiley" <mreiley@.cantv.net> wrote in message
news:%23uVCIbV2DHA.1736@.TK2MSFTNGP09.phx.gbl...
> Hi, Group
> Please let me know how can I do the following:
> Will prevent the user from deleting an entry if the value is used in a
> foreign key.
> Best regard
> Mario
>|||OJ describes the default behavour of FK constraints, which seems to be the
behavour you want... You may add the cascade option to the constraint, which
will cause the child records to be automatically deleted when the parent row
is deleted. (Just an FYI)
--
Wayne Snyder, MCDBA, SQL Server MVP
Computer Education Services Corporation (CESC), Charlotte, NC
www.computeredservices.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Mario Reiley" <mreiley@.cantv.net> wrote in message
news:%23uVCIbV2DHA.1736@.TK2MSFTNGP09.phx.gbl...
> Hi, Group
> Please let me know how can I do the following:
> Will prevent the user from deleting an entry if the value is used in a
> foreign key.
> Best regard
> Mario
>|||OK my Friends but what can i do then and thanks so much for yours response.
Best Regard
Mario
"Mario Reiley" <mreiley@.cantv.net> escribió en el mensaje
news:%23uVCIbV2DHA.1736@.TK2MSFTNGP09.phx.gbl...
> Hi, Group
> Please let me know how can I do the following:
> Will prevent the user from deleting an entry if the value is used in a
> foreign key.
> Best regard
> Mario
>|||OK, let me try explaining.
I have two tables:
Personal (Is my Parent table)
Profession ( Is my Child table)
In Parent is: ProfessionId field.
The thing is how Can I void Delete a row in Profession table when there is a
value in Personal table.
Example:
IF the user not put the profession in the Personal record then no problem
the profession row Is OK (Delete) else the row profession can't be deleting.
Note: Sorry my English.
Best regard
MArio
"Mario Reiley" <mreiley@.cantv.net> escribió en el mensaje
news:%23uVCIbV2DHA.1736@.TK2MSFTNGP09.phx.gbl...
> Hi, Group
> Please let me know how can I do the following:
> Will prevent the user from deleting an entry if the value is used in a
> foreign key.
> Best regard
> Mario
>|||Here's how to add a the relationship.
ALTER TABLE Personal
ADD FOREIGN KEY (ProfessionId)
REFERENCES Profession (ProfessionId)
ON DELETE NO ACTION
"Mario Reiley" <mreiley@.cantv.net> wrote in message
news:%23gTfxJd2DHA.2620@.TK2MSFTNGP09.phx.gbl...
> OK, let me try explaining.
>
> I have two tables:
>
> Personal (Is my Parent table)
> Profession ( Is my Child table)
>
> In Parent is: ProfessionId field.
>
> The thing is how Can I void Delete a row in Profession table when there is
a
> value in Personal table.
>
> Example:
>
> IF the user not put the profession in the Personal record then no problem
> the profession row Is OK (Delete) else the row profession can't be
deleting.
>
> Note: Sorry my English.
>
> Best regard
> MArio
> "Mario Reiley" <mreiley@.cantv.net> escribió en el mensaje
> news:%23uVCIbV2DHA.1736@.TK2MSFTNGP09.phx.gbl...
> > Hi, Group
> >
> > Please let me know how can I do the following:
> >
> > Will prevent the user from deleting an entry if the value is used in a
> > foreign key.
> >
> > Best regard
> > Mario
> >
> >
>

Reducing the size of the transaction log file

At this time I am only playing with the applications that generate the data and send it to the database. Without doing too much, and with deleting most data tables that were created, my transaction log file has grown over a gigabyte. I tried using the SQL server management studio (express) to shrink the database (tried shrinking files, too) but that did not make the file smaller. Right now there is hardly any data in the database (6 tables, a dozen columns and rows each) so it must be old transactions that are kept in the log. How do I get rid of the old data and make the file size smaller? Thanks.
KamenHi,

I had the same issue yesterday, i had to, Backup the log file then use shrink, backup one moore time and shrink.|||1. Check your database 'Recovery Model', set it according to your requirement.
2. Make a backup plan matching your data sensitivity & requirement as well as schedule it.
3. Plan Log backup along with Log Truncate option.

Refer Books OnLine from SQL Query Analyzer.|||Thank you very much!
Kamen

Reducing filesize while deleting rows?

I've got a very filesize restricted database. I noticed that when I insert 1000 rows my filesize jumps to 80k, but when I delete all but 50 of those rows...the filesize actually increases to 84k. How do I make sure the filesize of my database shrinks when I delete rows?

Thanks!

Use SqlCeEngine.Compact() or SqlCeEngine.Shrink() to do that. You can find description on MSDN.

|||Unfortunately I'm using native C++ for all this, so I don't have access to SqlCeEngine. Is there a SQL command i could execute to perform the equivalent to a shrink routine? If that doesn't exist I'll need some way to do it while an existing OLEDB connection is open and operating. I need this database to run 24/7 with no downtime whatsoever.

Thanks for the help!
|||

The database connection must be closed to do shrink or repair. This is a design requirement so that database file structure can be recreated.

What you can probably do is to make a copy of the database , compact it, momentarily drop connection to original database and replace original with compacted database. You will have to take a read only lock on original db while the copy is compacted so that no new changes take place.

|||

The OLEDB provider has an Engine object, which has a CompactDatabase method.

Reduce Table Size without deleting data

We are using SQL Server 2000 Standard ed, sp4 on Server 2000 Advanced.

We have one table that is three times as large as the rest of the database.
Most of the data is static after approximately 3-6 months, but we are
required to keep it for 8 years. I would like to archive this table (A), but
there are complications.

1. the only way to access the data is through the application (they are
images produced by the application-built on Power-Builder)
2. there are multiple tables refrencing this table and vise-versa
3. we restore the entire db to two other servers for testing and training
regularly
4. there might be more complications that have not been thought of

Currently, our only plan is to setup a seperate server with a copy of this db
on it and the application. Leave only the tables necessary to access the data,
and if this 'archive' works, remove from production the data from the table A
and all references to the table A from rows on the other tables.

I mentioned #3 because someone mentioned a third party tool that may be able
to pull the data from the table, archive it elsewhere, and at the same time,
place a 'pointer' in the table to the new storage location. The tool they
mentioned only works on Oracle and we have not explored beyond that yet.

I am ready to explore ideas and suggestions; I am still new to the DBA world,
I am out of ideas.

Thank you!

--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forum...eneral/200607/1On Thu, 20 Jul 2006 14:23:50 GMT, iamset via SQLMonster.com wrote:

(snip)

Quote:

Originally Posted by

>I am ready to explore ideas and suggestions; I am still new to the DBA world,
>I am out of ideas.

Hi iamset,

You might wish to explore distributed partitioned views. Looks like
they're the exact right tool for this job.

Books Online has good information on them.

--
Hugo Kornelis, SQL Server MVP