Hello Friends,
I am right now working on a project that has a database with over 100 tables in a database. Because of extreme time constraints the developers didn't build in any relationships or constraints between or in the tables. Now I need to remodel the database such that the database is more structured and normalized. I don't have much knowledge about the database design since it is a 2 year old application and the person who developed the database is now gone. I know remodelling the database would require knowledge of the existing database and business rules.
I was wondering if there are any tools that could suggest or discover relationships between tables. For eg. Lets say there are two tables named 'Customer' and 'Order'. I notice that there is a column named 'id' in Customer and a column named 'customer_id' in Order. So I ask the tool to discover a relationship between id and customer_id and it tells me that there is a one-one or one-many or no relationship by comparing values. I heard ERWin would be able to do that but thats expensive. Please do let me know asap.Problem is, without any relational integrity and constraints designed into the database, it probably already contains a lot of data that violates the logical relationships. That makes it impossible for any tool to definitively say what the relationships should be based solely upon the existing data.
I have a script that finds natural keys within a table, which you can use to set the primary key, but that's about it.
Chances are, 10% of your time is going to be occupied with finding out what the relationships are supposed to be, while 90% will involve fixing the bad data you find.
And this: "Because of extreme time constraints the developers didn't build in any relationships or constraints between or in the tables" is total bull. They are just bad developers. I can set a constraint or a foreign key in 30 seconds. They just didn't want to be bothered taking the time to make sure their code submitted correct data to the database, and so they allowed the database to accept any old crap that is sent to it. That's why you have a mess on your hands.|||I used ERWin in order to deduce references. But unfortunately even that didn't suggest much. ERWin tries to deduce what would be relationships between tables. I guess now I have to use logic in order to figure out what the relationships would be.
2012年3月8日星期四
Disappearing Precedence Constraints
Has anyone seen precedence constraints disappear in a package after closing and opening again? In this case, it's not package-wide. Only constraints inside one Foreach Loop container disappeared. Any idea what causes this to happen?
p.s: Yes, I did Save All. Most of these contraints were saved in the package for more than a week anyway.
I have not seen that. Is your package under any source control software? is that the case compare it against previous versions. A similar issue has been reported for many of us; but the problem was inside the Dataflow (when using VSS as source control)though....
|||Hi M. Glenn,
Yep, I've seen it too.
I've seen this and other strange behavior when my Visual Studio environment has been running for days on end (it happens...). My solution is to shut it down and restart the environment. Sometimes the metadata for the objects remains out of sync and the only way to get it back is to either copy it and paste it into a new package or recreate it.
Hope this helps,
Andy
|||Thanks for the quick replies. "...Visual Studio environment...running for days on end": Yeah that's me alright. We're not using any source control utilities, but I was in the habit of leaving VS open for days while working on projects. Not any more. I save frequently, but obviously that's not enough.
I could see and edit the constraints in Package Explorer, and tasks that allow only one connection would correctly return an error if I tried to add another constraint, but they were invisible in Control Flow designer. Closing and restarting the project and/or VS didn't help. Selecting all tasks in the container and dragging them outside the container made the connecters visible again. Then just drag everything back and all is well. Thanks for the tip.
BTW, I'm using VS 2005 Pro--installed after SS05/BIDS. I wonder if those using BIDS without a full VS 2005 install are experiencing this issue. Actually, I’m impressed with how stable SS05 and VS05 are--especially considering how much more complex and feature-filled these products are compared to their predecessors.
|||
M.Glenn wrote:
BTW, I'm using VS 2005 Pro--installed after SS05/BIDS. I wonder if those using BIDS without a full VS 2005 install are experiencing this issue. Actually, I’m impressed with how stable SS05 and VS05 are--especially considering how much more complex and feature-filled these products are compared to their predecessors.
Oh my word. A realist!
Thanks M Glenn. You've restored my faith in the community following some particularly galling diatribes of late!
|||Jamie, those guys must not realize how much progress these products have made in terms of functionality, stability, performance and ease-of-use features (although overall complexity is an unavoidable companion of added functionality). Add to this a world-class customer feedback/support infrastructure (including newsgroups like this one). We all have complaints about Microsoft, but that shouldn't completely blind someone to what's been accomplished here.
The SS and VS "2005" releases are nothing short of amazing. And whoever thought of selling SS05 Developer Edition for $50 is a genius. A year ago I couldn't even get our CIO to spring for that. Ironically, there are so many compelling reasons to upgrade it created a problem of how to fit it all in an executive summary. But DBA's and developers could recognize something special early on. I shelled out $50 from my own pocket and got wowed right off the bat just watching the install routine! Before long I was hounding the CIO without mercy. He eventually gave in when some funds came available.
Recently, I automated report creation, delivery and notification to 40+ healthcare providers with an SSIS package so simple it would make folks who hang out here yawn. But it made me and the CIO look like geniuses. That's what I call progress!
Hi M.Glenn,
Cool! I have to agree with Jamie - it's nice to read about someone's successes with the product.
The forum is similar to a doctor's office - no one shows up and says "Hi folks, everything's fine! See ya!" They usually pop in when something bad is happening or about to happen.
Thanks,
Andy
The forum is similar to a doctor's office - no one shows up and says "Hi folks, everything's fine! See ya!" They usually pop in when something bad is happening or about to happen.
Good analogy. Helps keep the complaints in perspective.
2012年2月24日星期五
Disabling constraints
Hi,
I need to disable all the foreign key and primary key constraints in a table
and reenable them at the end of my commands. I can use ALTER TABLE to do
this but I was wondering if it is a good practise. It is not maintenance
code but production code. Everything is done in a transaction but can I be
sure there is no incidence with concurent accesses ? What about the rights
for the db user ?
Thanks.
FredFrdric Mayot wrote:
> Hi,
> I need to disable all the foreign key and primary key constraints in
> a table and reenable them at the end of my commands. I can use ALTER
> TABLE to do this but I was wondering if it is a good practise. It is
> not maintenance code but production code. Everything is done in a
> transaction but can I be sure there is no incidence with concurent
> accesses ? What about the rights for the db user ?
> Thanks.
> Fred
Why would you need to disable constraints in production code?
David Gugick
Quest Software
www.imceda.com
www.quest.com|||The reason is quite simple.
Assume we have the tables
A(pkA <PK> )
B(pkfkA <PK,FK>, pkfkC <PK,FK> )
C(pkC <PK> )
with the data
A = {1}
C = {11, 12}
B = {(1, 11), (1, 12)}
Now, I want to update the two rows in B in the following manner :
(1, 11) -> (1, 12) and (1, 12) -> (1, 11).
This operation is supposed to be atomic (that's to say in a transaction)
"David Gugick" <david.gugick-nospam@.quest.com> a crit dans le message de
news: %23$J$Y0pbFHA.3040@.TK2MSFTNGP14.phx.gbl...
> Frdric Mayot wrote:
> Why would you need to disable constraints in production code?
> --
> David Gugick
> Quest Software
> www.imceda.com
> www.quest.com|||Have you tried this?
UPDATE B
SET pkfkC = CASE pkfkC
WHEN 11 THEN 12
WHEN 12 THEN 11
END
WHERE pkfkA = 1 AND pkfkC IN (11, 12)
This update is atomic and as such doesn't cause a constraint violation error
.
"Frédéric Mayot" wrote:
> The reason is quite simple.
> Assume we have the tables
> A(pkA <PK> )
> B(pkfkA <PK,FK>, pkfkC <PK,FK> )
> C(pkC <PK> )
> with the data
> A = {1}
> C = {11, 12}
> B = {(1, 11), (1, 12)}
> Now, I want to update the two rows in B in the following manner :
> (1, 11) -> (1, 12) and (1, 12) -> (1, 11).
> This operation is supposed to be atomic (that's to say in a transaction)
>
> "David Gugick" <david.gugick-nospam@.quest.com> a écrit dans le message de
> news: %23$J$Y0pbFHA.3040@.TK2MSFTNGP14.phx.gbl...
>
>
I need to disable all the foreign key and primary key constraints in a table
and reenable them at the end of my commands. I can use ALTER TABLE to do
this but I was wondering if it is a good practise. It is not maintenance
code but production code. Everything is done in a transaction but can I be
sure there is no incidence with concurent accesses ? What about the rights
for the db user ?
Thanks.
FredFrdric Mayot wrote:
> Hi,
> I need to disable all the foreign key and primary key constraints in
> a table and reenable them at the end of my commands. I can use ALTER
> TABLE to do this but I was wondering if it is a good practise. It is
> not maintenance code but production code. Everything is done in a
> transaction but can I be sure there is no incidence with concurent
> accesses ? What about the rights for the db user ?
> Thanks.
> Fred
Why would you need to disable constraints in production code?
David Gugick
Quest Software
www.imceda.com
www.quest.com|||The reason is quite simple.
Assume we have the tables
A(pkA <PK> )
B(pkfkA <PK,FK>, pkfkC <PK,FK> )
C(pkC <PK> )
with the data
A = {1}
C = {11, 12}
B = {(1, 11), (1, 12)}
Now, I want to update the two rows in B in the following manner :
(1, 11) -> (1, 12) and (1, 12) -> (1, 11).
This operation is supposed to be atomic (that's to say in a transaction)
"David Gugick" <david.gugick-nospam@.quest.com> a crit dans le message de
news: %23$J$Y0pbFHA.3040@.TK2MSFTNGP14.phx.gbl...
> Frdric Mayot wrote:
> Why would you need to disable constraints in production code?
> --
> David Gugick
> Quest Software
> www.imceda.com
> www.quest.com|||Have you tried this?
UPDATE B
SET pkfkC = CASE pkfkC
WHEN 11 THEN 12
WHEN 12 THEN 11
END
WHERE pkfkA = 1 AND pkfkC IN (11, 12)
This update is atomic and as such doesn't cause a constraint violation error
.
"Frédéric Mayot" wrote:
> The reason is quite simple.
> Assume we have the tables
> A(pkA <PK> )
> B(pkfkA <PK,FK>, pkfkC <PK,FK> )
> C(pkC <PK> )
> with the data
> A = {1}
> C = {11, 12}
> B = {(1, 11), (1, 12)}
> Now, I want to update the two rows in B in the following manner :
> (1, 11) -> (1, 12) and (1, 12) -> (1, 11).
> This operation is supposed to be atomic (that's to say in a transaction)
>
> "David Gugick" <david.gugick-nospam@.quest.com> a écrit dans le message de
> news: %23$J$Y0pbFHA.3040@.TK2MSFTNGP14.phx.gbl...
>
>
2012年2月14日星期二
disable constraints
Hi,
I disabled all constraints on a database before using DTS by running the
following command:
exec sp_MSforeachtable 'ALTER TABLE ? NOCHECK CONSTRAINT ALL'
When comparing the source tables and the destination tables after the DTS I
found that some DEFAULT constraints were not disabled. The result was that
source columns populated with NULLs got populated on the destinatin columns
with the dafult values as defined in the constraints.
Thanks,
YanivYou cannot disable DEFAULT, UNIQUE or PRIMARY KEY constraints.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Yaniv" <yanive@.rediffmail.com> wrote in message news:ePf1C8o7FHA.4076@.tk2msftngp13.phx.gbl
..
> Hi,
> I disabled all constraints on a database before using DTS by running the f
ollowing command:
> exec sp_MSforeachtable 'ALTER TABLE ? NOCHECK CONSTRAINT ALL'
> When comparing the source tables and the destination tables after the DTS
I found that some
> DEFAULT constraints were not disabled. The result was that source columns
populated with NULLs got
> populated on the destinatin columns with the dafult values as defined in t
he constraints.
>
> Thanks,
> Yaniv
>|||Hi, Yaniv
I think you should have first to drop contstraint (DEFAULT in your case) ,
insert the data and then re-create contraints
"Yaniv" <yanive@.rediffmail.com> wrote in message
news:ePf1C8o7FHA.4076@.tk2msftngp13.phx.gbl...
> Hi,
> I disabled all constraints on a database before using DTS by running the
> following command:
> exec sp_MSforeachtable 'ALTER TABLE ? NOCHECK CONSTRAINT ALL'
> When comparing the source tables and the destination tables after the DTS
> I found that some DEFAULT constraints were not disabled. The result was
> that source columns populated with NULLs got populated on the destinatin
> columns with the dafult values as defined in the constraints.
>
> Thanks,
> Yaniv
>|||Thank you all,
This is what I did; I droped and recreated the defaults but I wanted to
find out why a default constraint was not diabled.
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:OjQBmIp7FHA.3752@.tk2msftngp13.phx.gbl...
> Hi, Yaniv
> I think you should have first to drop contstraint (DEFAULT in your case)
> , insert the data and then re-create contraints
>
>
>
>
> "Yaniv" <yanive@.rediffmail.com> wrote in message
> news:ePf1C8o7FHA.4076@.tk2msftngp13.phx.gbl...
>|||See my earlier reply. You cannot disable default constraints. I.e., default,
pk and uq constraints
are not disabled when you disable "ALL" constraints for a table.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Yaniv" <yanive@.rediffmail.com> wrote in message news:OPF1Tfp7FHA.3388@.TK2MSFTNGP11.phx.gbl
..
> Thank you all,
> This is what I did; I droped and recreated the defaults but I wanted to f
ind out why a default
> constraint was not diabled.
>
> --
> "Uri Dimant" <urid@.iscar.co.il> wrote in message news:OjQBmIp7FHA.3752@.tk2
msftngp13.phx.gbl...
>|||hi guys try the attached file to dynamically drop a default constraint on a
column in a table.
I disabled all constraints on a database before using DTS by running the
following command:
exec sp_MSforeachtable 'ALTER TABLE ? NOCHECK CONSTRAINT ALL'
When comparing the source tables and the destination tables after the DTS I
found that some DEFAULT constraints were not disabled. The result was that
source columns populated with NULLs got populated on the destinatin columns
with the dafult values as defined in the constraints.
Thanks,
YanivYou cannot disable DEFAULT, UNIQUE or PRIMARY KEY constraints.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Yaniv" <yanive@.rediffmail.com> wrote in message news:ePf1C8o7FHA.4076@.tk2msftngp13.phx.gbl
..
> Hi,
> I disabled all constraints on a database before using DTS by running the f
ollowing command:
> exec sp_MSforeachtable 'ALTER TABLE ? NOCHECK CONSTRAINT ALL'
> When comparing the source tables and the destination tables after the DTS
I found that some
> DEFAULT constraints were not disabled. The result was that source columns
populated with NULLs got
> populated on the destinatin columns with the dafult values as defined in t
he constraints.
>
> Thanks,
> Yaniv
>|||Hi, Yaniv
I think you should have first to drop contstraint (DEFAULT in your case) ,
insert the data and then re-create contraints
"Yaniv" <yanive@.rediffmail.com> wrote in message
news:ePf1C8o7FHA.4076@.tk2msftngp13.phx.gbl...
> Hi,
> I disabled all constraints on a database before using DTS by running the
> following command:
> exec sp_MSforeachtable 'ALTER TABLE ? NOCHECK CONSTRAINT ALL'
> When comparing the source tables and the destination tables after the DTS
> I found that some DEFAULT constraints were not disabled. The result was
> that source columns populated with NULLs got populated on the destinatin
> columns with the dafult values as defined in the constraints.
>
> Thanks,
> Yaniv
>|||Thank you all,
This is what I did; I droped and recreated the defaults but I wanted to
find out why a default constraint was not diabled.
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:OjQBmIp7FHA.3752@.tk2msftngp13.phx.gbl...
> Hi, Yaniv
> I think you should have first to drop contstraint (DEFAULT in your case)
> , insert the data and then re-create contraints
>
>
>
>
> "Yaniv" <yanive@.rediffmail.com> wrote in message
> news:ePf1C8o7FHA.4076@.tk2msftngp13.phx.gbl...
>|||See my earlier reply. You cannot disable default constraints. I.e., default,
pk and uq constraints
are not disabled when you disable "ALL" constraints for a table.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Yaniv" <yanive@.rediffmail.com> wrote in message news:OPF1Tfp7FHA.3388@.TK2MSFTNGP11.phx.gbl
..
> Thank you all,
> This is what I did; I droped and recreated the defaults but I wanted to f
ind out why a default
> constraint was not diabled.
>
> --
> "Uri Dimant" <urid@.iscar.co.il> wrote in message news:OjQBmIp7FHA.3752@.tk2
msftngp13.phx.gbl...
>|||hi guys try the attached file to dynamically drop a default constraint on a
column in a table.
Disable all constraints in a table?
I have to delete records in quite a few of the tables of a DataWarehouse and
then populate them from else where. I'm not able to delete the records due to
Foreign Key constraints and I was wondering if there is a simpler way for me
to disable all the constraints in a table.
Thanks,
rgnI think you can use
alter table mytable nocheck constraint all
but I am not sure if it is a good idea as you will disable enforcing the
integrity of your data.
Hope this helps,
Ben Nevarez
Senior Database Administrator
AIG SunAmerica
"rgn" wrote:
> I have to delete records in quite a few of the tables of a DataWarehouse and
> then populate them from else where. I'm not able to delete the records due to
> Foreign Key constraints and I was wondering if there is a simpler way for me
> to disable all the constraints in a table.
> Thanks,
> rgn
then populate them from else where. I'm not able to delete the records due to
Foreign Key constraints and I was wondering if there is a simpler way for me
to disable all the constraints in a table.
Thanks,
rgnI think you can use
alter table mytable nocheck constraint all
but I am not sure if it is a good idea as you will disable enforcing the
integrity of your data.
Hope this helps,
Ben Nevarez
Senior Database Administrator
AIG SunAmerica
"rgn" wrote:
> I have to delete records in quite a few of the tables of a DataWarehouse and
> then populate them from else where. I'm not able to delete the records due to
> Foreign Key constraints and I was wondering if there is a simpler way for me
> to disable all the constraints in a table.
> Thanks,
> rgn
Disable all constraints in a table?
I have to delete records in quite a few of the tables of a DataWarehouse and
then populate them from else where. I'm not able to delete the records due t
o
Foreign Key constraints and I was wondering if there is a simpler way for me
to disable all the constraints in a table.
Thanks,
rgnI think you can use
alter table mytable nocheck constraint all
but I am not sure if it is a good idea as you will disable enforcing the
integrity of your data.
Hope this helps,
Ben Nevarez
Senior Database Administrator
AIG SunAmerica
"rgn" wrote:
> I have to delete records in quite a few of the tables of a DataWarehouse a
nd
> then populate them from else where. I'm not able to delete the records due
to
> Foreign Key constraints and I was wondering if there is a simpler way for
me
> to disable all the constraints in a table.
> Thanks,
> rgn
then populate them from else where. I'm not able to delete the records due t
o
Foreign Key constraints and I was wondering if there is a simpler way for me
to disable all the constraints in a table.
Thanks,
rgnI think you can use
alter table mytable nocheck constraint all
but I am not sure if it is a good idea as you will disable enforcing the
integrity of your data.
Hope this helps,
Ben Nevarez
Senior Database Administrator
AIG SunAmerica
"rgn" wrote:
> I have to delete records in quite a few of the tables of a DataWarehouse a
nd
> then populate them from else where. I'm not able to delete the records due
to
> Foreign Key constraints and I was wondering if there is a simpler way for
me
> to disable all the constraints in a table.
> Thanks,
> rgn
Disable all constraints in a table?
I have to delete records in quite a few of the tables of a DataWarehouse and
then populate them from else where. I'm not able to delete the records due to
Foreign Key constraints and I was wondering if there is a simpler way for me
to disable all the constraints in a table.
Thanks,
rgn
I think you can use
alter table mytable nocheck constraint all
but I am not sure if it is a good idea as you will disable enforcing the
integrity of your data.
Hope this helps,
Ben Nevarez
Senior Database Administrator
AIG SunAmerica
"rgn" wrote:
> I have to delete records in quite a few of the tables of a DataWarehouse and
> then populate them from else where. I'm not able to delete the records due to
> Foreign Key constraints and I was wondering if there is a simpler way for me
> to disable all the constraints in a table.
> Thanks,
> rgn
then populate them from else where. I'm not able to delete the records due to
Foreign Key constraints and I was wondering if there is a simpler way for me
to disable all the constraints in a table.
Thanks,
rgn
I think you can use
alter table mytable nocheck constraint all
but I am not sure if it is a good idea as you will disable enforcing the
integrity of your data.
Hope this helps,
Ben Nevarez
Senior Database Administrator
AIG SunAmerica
"rgn" wrote:
> I have to delete records in quite a few of the tables of a DataWarehouse and
> then populate them from else where. I'm not able to delete the records due to
> Foreign Key constraints and I was wondering if there is a simpler way for me
> to disable all the constraints in a table.
> Thanks,
> rgn
订阅：
博文 (Atom)