Constraints?

I am importing data from a .csv. I have noticed that it copies this
information over and over when I import the data. I only want it to update
anything that is different and add any new lines in the .csv file. I have
looked at setting up a primary key but this won't work.
ProjectID, Phase, Unit,Tract, Release, UnitPlan, UnitOpt
As long as one of these fields is different I want it to allow it to be
entered. My problem is some of them may be null. Which is ok.
KB5IR,10,405,,,4516,,
KB5IR,10,406,,4516,REV,
I am not sure how to go about doing this!
Message posted via webservertalk.com
http://www.webservertalk.com/Uwe/Forum...amming/200603/1brenenger via webservertalk.com wrote:
> I am importing data from a .csv. I have noticed that it copies this
> information over and over when I import the data. I only want it to update
> anything that is different and add any new lines in the .csv file. I have
> looked at setting up a primary key but this won't work.
> ProjectID, Phase, Unit,Tract, Release, UnitPlan, UnitOpt
> As long as one of these fields is different I want it to allow it to be
> entered. My problem is some of them may be null. Which is ok.
> KB5IR,10,405,,,4516,,
> KB5IR,10,406,,4516,REV,
> I am not sure how to go about doing this!
> --
> Message posted via webservertalk.com
> http://www.webservertalk.com/Uwe/Forum...amming/200603/1
Do you have an existing data model or are you designing one? Don't make
the mistake of trying to mimic a file in a table. Relational design
principles and file storage are not the same!
Assuming you have or create a suitably normalized data model there are
basically 2 possible approaches:
1. Transform the file as you load it to the normalized schema (using
DTS or Integration Services or some other tool for example)
2. Load the file as-is to an intermediate "staging" table and then
transform the data to your real data model using SQL. The staging table
isn't used for anything other than the load process and the data is
usually deleted some time afterwards.
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||Here are a couple of options to look at-
use of a unique index with the ignore dupe option (it will just not enter
the data for duplicate insert attempts) . This would not work with the
updates you mention however. Another option would be to import into a load
table then use a procedure containing the merge logic to update/insert as
appropriate.
HTH
--Tony
"brenenger via webservertalk.com" wrote:

> I am importing data from a .csv. I have noticed that it copies this
> information over and over when I import the data. I only want it to update
> anything that is different and add any new lines in the .csv file. I have
> looked at setting up a primary key but this won't work.
> ProjectID, Phase, Unit,Tract, Release, UnitPlan, UnitOpt
> As long as one of these fields is different I want it to allow it to be
> entered. My problem is some of them may be null. Which is ok.
> KB5IR,10,405,,,4516,,
> KB5IR,10,406,,4516,REV,
> I am not sure how to go about doing this!
> --
> Message posted via webservertalk.com
> http://www.webservertalk.com/Uwe/Forum...amming/200603/1
>|||Hi everyone. I am sorry about this. I am VERY new to SQL and I am having a
hard time trying to figure out what to do. I got Access configured to link t
o
SQL Server and I think I'll be able to get the rest of this figured out. My
only problem now is the import. I need to UPDATE/INSERT the .csv information
.
Here is the transformation that I got help creating.
Function Main()
IF DTSSource("Col001") <>0 Then
' add a 0 to the front, and keep only the 6 right characters.
'21706 -> 021706 -> 021706
'100106 -> 0100106 -> 100106
str = DTSSource("Col001")
iMo = CInt( Mid( str, 1, 1) )
iDay = CInt( Mid( str, 2, 2) )
iYear = CInt( Mid( str, 4, 2) )
DTSDestination("DelDate") = DateSerial( iYear, iMo, iDay)
Else
'This will not return a value for the date
'which means it will be NULL if the col001 = "0"
'If you want to specify a date then uncomment the next line
'DTSDestination("DelDate") = "19000101"
End If
DTSDestination("ProjectID") = DTSSource("Col002")
DTSDestination("Phase") = DTSSource("Col003")
DTSDestination("Unit") = DTSSource("Col004")
If DTSSource("Col005") = Null Then
DTSDestination ("Tract") = " "
Else
End If
If DTSSource("Col006") = Null Then
DTSDestination ("Release") = " "
Else
End IF
DTSDestination("UnitPlan") = DTSSource("Col007")
DTSDestination("UnitOpt") = DTSSource("Col008")
DTSDestination("POComp") = DTSSource("Col009")
DTSDestination("PrjFrm") = DTSSource("Col010")
DTSDestination("OrderNo") = DTSSource("Col011")
DTSDestination("OrderStat") = DTSSource("Col012")
DTSDestination("Boxes") = DTSSource("Col013")
Main = DTSTransformStat_OK
End Function
I cannot create a PK because I can have null values. As long as ProjectID,
Phase, Unit, Tract, Release, UnitPlan, UnitOpt are unique all together it
should be ok. Here is an example of what my .csv looks like. I need to be
able to import this periodically. I would like for it to update anything tha
t
has changed and insert anything that is missing. The way it is now, it just
adds the import to the table. So I could have this stuff listed over and ove
r.
Which I don't want.
21706 KBMP NEW 200 2031 Y Y 64149 SCHED 17
21706 KBMP NEW 201 2031 Y Y 64150 SCHED 8
21706 KBMP NEW 201 2031 OPT04 Y Y 64151 SCHED 13
21706 KBMP NEW 201 2031 OPT136 Y Y 64151 SCHED 0
21706 KBMP NEW 201 2031 OPT142 Y Y 64151 SCHED 0
21706 KBMP NEW 201 2031 OPT143 Y Y 64151 SCHED 0
21706 KBMP NEW 201 2031 OPT144 Y Y 64151 SCHED 0
Message posted via webservertalk.com
http://www.webservertalk.com/Uwe/Forum...amming/200603/1|||brenenger via webservertalk.com wrote:
> Hi everyone. I am sorry about this. I am VERY new to SQL and I am having a
> hard time trying to figure out what to do. I got Access configured to link
to
> SQL Server and I think I'll be able to get the rest of this figured out. M
y
> only problem now is the import. I need to UPDATE/INSERT the .csv informati
on.
> Here is the transformation that I got help creating.
> Function Main()
> IF DTSSource("Col001") <>0 Then
> ' add a 0 to the front, and keep only the 6 right characters.
> '21706 -> 021706 -> 021706
> '100106 -> 0100106 -> 100106
> str = DTSSource("Col001")
> iMo = CInt( Mid( str, 1, 1) )
> iDay = CInt( Mid( str, 2, 2) )
> iYear = CInt( Mid( str, 4, 2) )
> DTSDestination("DelDate") = DateSerial( iYear, iMo, iDay)
> Else
> 'This will not return a value for the date
> 'which means it will be NULL if the col001 = "0"
> 'If you want to specify a date then uncomment the next line
> 'DTSDestination("DelDate") = "19000101"
> End If
>
> DTSDestination("ProjectID") = DTSSource("Col002")
> DTSDestination("Phase") = DTSSource("Col003")
> DTSDestination("Unit") = DTSSource("Col004")
> If DTSSource("Col005") = Null Then
> DTSDestination ("Tract") = " "
> Else
> End If
> If DTSSource("Col006") = Null Then
> DTSDestination ("Release") = " "
> Else
> End IF
> DTSDestination("UnitPlan") = DTSSource("Col007")
> DTSDestination("UnitOpt") = DTSSource("Col008")
> DTSDestination("POComp") = DTSSource("Col009")
> DTSDestination("PrjFrm") = DTSSource("Col010")
> DTSDestination("OrderNo") = DTSSource("Col011")
> DTSDestination("OrderStat") = DTSSource("Col012")
> DTSDestination("Boxes") = DTSSource("Col013")
> Main = DTSTransformStat_OK
> End Function
> I cannot create a PK because I can have null values. As long as ProjectID,
> Phase, Unit, Tract, Release, UnitPlan, UnitOpt are unique all together it
> should be ok. Here is an example of what my .csv looks like. I need to be
> able to import this periodically. I would like for it to update anything t
hat
> has changed and insert anything that is missing. The way it is now, it jus
t
> adds the import to the table. So I could have this stuff listed over and o
ver.
> Which I don't want.
>
> 21706 KBMP NEW 200 2031 Y Y 64149 SCHED 17
> 21706 KBMP NEW 201 2031 Y Y 64150 SCHED 8
> 21706 KBMP NEW 201 2031 OPT04 Y Y 64151 SCHED 13
> 21706 KBMP NEW 201 2031 OPT136 Y Y 64151 SCHED 0
> 21706 KBMP NEW 201 2031 OPT142 Y Y 64151 SCHED 0
> 21706 KBMP NEW 201 2031 OPT143 Y Y 64151 SCHED 0
> 21706 KBMP NEW 201 2031 OPT144 Y Y 64151 SCHED 0
> --
> Message posted via webservertalk.com
> http://www.webservertalk.com/Uwe/Forum...amming/200603/1
I think the easiest approach will be to load this data to a temporary
staging table that matches the file structure. Then use UPDATE and
INSERT statements to load the data into actual table(s) in your
database. In the staging table you can use a file name, date and/or row
number as the key.
You will find it VERY hard and maybe even impossible to maintain the
integrity of the changing data unless you first implement the correct
data model with keys in each table. I can't help you to do that just
based on a list of column names and a snapshot of your data file. The
data model should be based on your business rules and knowledge of your
business environment. There is little point in basing your data model
on the format that happens to have been used in this file.
If you don't already know about relational design principles like
Normalization and the normal forms then you should master those
concepts before you attempt a final design.
On the other hand if you don't care about design right now and just
want to see some data in a table then you could create a unique index
on all columns using the IGNORE_DUP_KEY option. That will eliminate any
duplicates but won't help you any further than that:
CREATE UNIQUE NONCLUSTERED INDEX idx_tbl
ON tbl (col1, col2, col3)
WITH IGNORE_DUP_KEY ;
IMPORTANT: I do NOT recommend this for a live production environment.
Hope this helps.
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||Ok. I am importing a .csv file in it's own table. I want this table to be
overwritten each time it is updated. I am using DTS to import. Once this
table is populated, I need to run a Stored Procedure that will look at that
table and tblUnitImport. This is the code I have so far, I am not sure if I
am doing this right. If the row is not listed in tblUnitImport then I need t
o
INSERT it, otherwise I need to update the DELDATE and/or ORDERSTAT field if
it is different in m#ds001o1. Am I on the right track?
BEGIN INSERT INTO tblUnitImport
(Deldate, ProjectID, Phase, Unit, Tract,
Release, UnitPlan, UnitOpt, POComp, PrjFrm, OrderNo, OrderStat, Boxes)
SELECT Col001, Col002, Col003,Col004, Col005, Col006,
Col007, Col008, Col009, Col010, Col011, Col012, Col013
FROM m#ds001o1 AS M
WHERE NOT EXISTS
(SELECT *
FROM tblUnitImport
WHERE tblUnitImport.
ProjectId = m#ds001o1.Col002
tblUnitImport.
Phase = m#ds001o1.Col003
tblUnitImport.
Unit = m#ds001o1.Col004
tblUnitImport.
Tract = m#ds001o1.Col005
tblUnitImport.
Release = m#ds001o1.Col006
tblUnitImport.
UnitPlan = m#ds001o1.Col007
tblUnitImport.
UnitOpt = m#ds001o1.Col008);
UPDATE tblUnitImport
SET col1 =
(SELECT
col001, col012
FROM
m#ds001o1
WHERE
tblUnitImport.ProjectId = m#ds001o1.Col002
tblUnitImport.Phase = m#ds001o1.Col003
tblUnitImport.Unit = m#ds001o1.Col004
tblUnitImport.Tract = m#ds001o1.Col005
tblUnitImport.Release = m#ds001o1.Col006
tblUnitImport.UnitPlan = m#ds001o1.Col007
tblUnitImport.UnitOpt = m#ds001o1.Col008)
WHERE EXISTS
(SELECT
*
FROM
Foobar
WHERE
Foobar.keycol = Merge_table.keycol); END;
Message posted via http://www.webservertalk.com

Constraints: disable / enable constraints issue

Isn't there a way, other then Enterprise Manager, to disable and / or enable
constraints, in particular primary and foreign keys? I am migrating data
daily from one system to SQL and to disable and enable manually is
inconvenient and combersome. I looked through BOL and cannot find a direct
answer on how to create a process to automatically disable and / or enable
constraints. Thanks.
You can use ALTER TABLE to disable a foreign key. You cannot disable a
primary key, since it uses a unique index.
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com
"Leida" <Leida@.discussions.microsoft.com> wrote in message
news:AD81D4EF-060C-41FA-82D0-A223EB41BD9A@.microsoft.com...
Isn't there a way, other then Enterprise Manager, to disable and / or enable
constraints, in particular primary and foreign keys? I am migrating data
daily from one system to SQL and to disable and enable manually is
inconvenient and combersome. I looked through BOL and cannot find a direct
answer on how to create a process to automatically disable and / or enable
constraints. Thanks.
|||Hi Leida
Foreign keys can be disabled using the ALTER TABLE command. Please see Books
Online for full syntax, or have Enterprise Manager script the operation to
show you the syntax to use.
Primary Keys cannot be disabled since they are supported by a unique index.
A unique index always must be maintained, so the only way to not enforce the
Primary Key is to drop the index, which means dropping the constraint.
HTH
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"Leida" <Leida@.discussions.microsoft.com> wrote in message
news:AD81D4EF-060C-41FA-82D0-A223EB41BD9A@.microsoft.com...
> Isn't there a way, other then Enterprise Manager, to disable and / or
> enable
> constraints, in particular primary and foreign keys? I am migrating data
> daily from one system to SQL and to disable and enable manually is
> inconvenient and combersome. I looked through BOL and cannot find a direct
> answer on how to create a process to automatically disable and / or enable
> constraints. Thanks.
|||"Leida" <Leida@.discussions.microsoft.com> wrote in message
news:AD81D4EF-060C-41FA-82D0-A223EB41BD9A@.microsoft.com...

> Isn't there a way, other then Enterprise Manager, to disable and / or
enable
> constraints, in particular primary and foreign keys?
ALTER TABLE tablename NOCHECK CONSTRAINT ALL
|||Leida,
Importing dirty data is a common problem. This is typically handled as
follows:
1) Load the data in a load table. This is a table with no constraints,
and may just have varchar columns
2) Clean up the data
3) Copy the data in the right order to the target table(s).
The target tables will have all their constraints in place and need not
be removed or disabled. This guarantees a consistent database at all
times.
By the way: please note that when you enable constraints after they have
been disabled the existing data will *not* be validated. This means you
can introduce invalid data in the table.
Also note that when you disable a constraint, this is not just for your
connection, but server wide. So if you do not insert invalid data,
another user might...
Hope this helps,
Gert-Jan
Leida wrote:
> Isn't there a way, other then Enterprise Manager, to disable and / or enable
> constraints, in particular primary and foreign keys? I am migrating data
> daily from one system to SQL and to disable and enable manually is
> inconvenient and combersome. I looked through BOL and cannot find a direct
> answer on how to create a process to automatically disable and / or enable
> constraints. Thanks.
|||Thank you for your response. I have not tried this way of loading the data. I
will definately try this out.
"Gert-Jan Strik" wrote:

> Leida,
> Importing dirty data is a common problem. This is typically handled as
> follows:
> 1) Load the data in a load table. This is a table with no constraints,
> and may just have varchar columns
> 2) Clean up the data
> 3) Copy the data in the right order to the target table(s).
> The target tables will have all their constraints in place and need not
> be removed or disabled. This guarantees a consistent database at all
> times.
> By the way: please note that when you enable constraints after they have
> been disabled the existing data will *not* be validated. This means you
> can introduce invalid data in the table.
> Also note that when you disable a constraint, this is not just for your
> connection, but server wide. So if you do not insert invalid data,
> another user might...
> Hope this helps,
> Gert-Jan
>
> Leida wrote:
>
|||Thank you for your response, and the syntax. It was helpful.
"Mark Wilden" wrote:

> "Leida" <Leida@.discussions.microsoft.com> wrote in message
> news:AD81D4EF-060C-41FA-82D0-A223EB41BD9A@.microsoft.com...
> enable
> ALTER TABLE tablename NOCHECK CONSTRAINT ALL
>
>

Constraints: disable / enable constraints issue

Isn't there a way, other then Enterprise Manager, to disable and / or enable
constraints, in particular primary and foreign keys? I am migrating data
daily from one system to SQL and to disable and enable manually is
inconvenient and combersome. I looked through BOL and cannot find a direct
answer on how to create a process to automatically disable and / or enable
constraints. Thanks.You can use ALTER TABLE to disable a foreign key. You cannot disable a
primary key, since it uses a unique index.
Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com
"Leida" <Leida@.discussions.microsoft.com> wrote in message
news:AD81D4EF-060C-41FA-82D0-A223EB41BD9A@.microsoft.com...
Isn't there a way, other then Enterprise Manager, to disable and / or enable
constraints, in particular primary and foreign keys? I am migrating data
daily from one system to SQL and to disable and enable manually is
inconvenient and combersome. I looked through BOL and cannot find a direct
answer on how to create a process to automatically disable and / or enable
constraints. Thanks.|||Hi Leida
Foreign keys can be disabled using the ALTER TABLE command. Please see Books
Online for full syntax, or have Enterprise Manager script the operation to
show you the syntax to use.
Primary Keys cannot be disabled since they are supported by a unique index.
A unique index always must be maintained, so the only way to not enforce the
Primary Key is to drop the index, which means dropping the constraint.
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"Leida" <Leida@.discussions.microsoft.com> wrote in message
news:AD81D4EF-060C-41FA-82D0-A223EB41BD9A@.microsoft.com...
> Isn't there a way, other then Enterprise Manager, to disable and / or
> enable
> constraints, in particular primary and foreign keys? I am migrating data
> daily from one system to SQL and to disable and enable manually is
> inconvenient and combersome. I looked through BOL and cannot find a direct
> answer on how to create a process to automatically disable and / or enable
> constraints. Thanks.|||"Leida" <Leida@.discussions.microsoft.com> wrote in message
news:AD81D4EF-060C-41FA-82D0-A223EB41BD9A@.microsoft.com...

> Isn't there a way, other then Enterprise Manager, to disable and / or
enable
> constraints, in particular primary and foreign keys?
ALTER TABLE tablename NOCHECK CONSTRAINT ALL|||Leida,
Importing dirty data is a common problem. This is typically handled as
follows:
1) Load the data in a load table. This is a table with no constraints,
and may just have varchar columns
2) Clean up the data
3) Copy the data in the right order to the target table(s).
The target tables will have all their constraints in place and need not
be removed or disabled. This guarantees a consistent database at all
times.
By the way: please note that when you enable constraints after they have
been disabled the existing data will *not* be validated. This means you
can introduce invalid data in the table.
Also note that when you disable a constraint, this is not just for your
connection, but server wide. So if you do not insert invalid data,
another user might...
Hope this helps,
Gert-Jan
Leida wrote:
> Isn't there a way, other then Enterprise Manager, to disable and / or enab
le
> constraints, in particular primary and foreign keys? I am migrating data
> daily from one system to SQL and to disable and enable manually is
> inconvenient and combersome. I looked through BOL and cannot find a direct
> answer on how to create a process to automatically disable and / or enable
> constraints. Thanks.|||Thank you for your response. I have not tried this way of loading the data.
I
will definately try this out.
"Gert-Jan Strik" wrote:

> Leida,
> Importing dirty data is a common problem. This is typically handled as
> follows:
> 1) Load the data in a load table. This is a table with no constraints,
> and may just have varchar columns
> 2) Clean up the data
> 3) Copy the data in the right order to the target table(s).
> The target tables will have all their constraints in place and need not
> be removed or disabled. This guarantees a consistent database at all
> times.
> By the way: please note that when you enable constraints after they have
> been disabled the existing data will *not* be validated. This means you
> can introduce invalid data in the table.
> Also note that when you disable a constraint, this is not just for your
> connection, but server wide. So if you do not insert invalid data,
> another user might...
> Hope this helps,
> Gert-Jan
>
> Leida wrote:
>|||Thank you for your response, and the syntax. It was helpful.
"Mark Wilden" wrote:

> "Leida" <Leida@.discussions.microsoft.com> wrote in message
> news:AD81D4EF-060C-41FA-82D0-A223EB41BD9A@.microsoft.com...
>
> enable
> ALTER TABLE tablename NOCHECK CONSTRAINT ALL
>
>

Constraints: disable / enable constraints issue

Isn't there a way, other then Enterprise Manager, to disable and / or enable
constraints, in particular primary and foreign keys? I am migrating data
daily from one system to SQL and to disable and enable manually is
inconvenient and combersome. I looked through BOL and cannot find a direct
answer on how to create a process to automatically disable and / or enable
constraints. Thanks.You can use ALTER TABLE to disable a foreign key. You cannot disable a
primary key, since it uses a unique index.
--
Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com
"Leida" <Leida@.discussions.microsoft.com> wrote in message
news:AD81D4EF-060C-41FA-82D0-A223EB41BD9A@.microsoft.com...
Isn't there a way, other then Enterprise Manager, to disable and / or enable
constraints, in particular primary and foreign keys? I am migrating data
daily from one system to SQL and to disable and enable manually is
inconvenient and combersome. I looked through BOL and cannot find a direct
answer on how to create a process to automatically disable and / or enable
constraints. Thanks.|||"Leida" <Leida@.discussions.microsoft.com> wrote in message
news:AD81D4EF-060C-41FA-82D0-A223EB41BD9A@.microsoft.com...
> Isn't there a way, other then Enterprise Manager, to disable and / or
enable
> constraints, in particular primary and foreign keys?
ALTER TABLE tablename NOCHECK CONSTRAINT ALL|||Hi Leida
Foreign keys can be disabled using the ALTER TABLE command. Please see Books
Online for full syntax, or have Enterprise Manager script the operation to
show you the syntax to use.
Primary Keys cannot be disabled since they are supported by a unique index.
A unique index always must be maintained, so the only way to not enforce the
Primary Key is to drop the index, which means dropping the constraint.
--
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"Leida" <Leida@.discussions.microsoft.com> wrote in message
news:AD81D4EF-060C-41FA-82D0-A223EB41BD9A@.microsoft.com...
> Isn't there a way, other then Enterprise Manager, to disable and / or
> enable
> constraints, in particular primary and foreign keys? I am migrating data
> daily from one system to SQL and to disable and enable manually is
> inconvenient and combersome. I looked through BOL and cannot find a direct
> answer on how to create a process to automatically disable and / or enable
> constraints. Thanks.|||Leida,
Importing dirty data is a common problem. This is typically handled as
follows:
1) Load the data in a load table. This is a table with no constraints,
and may just have varchar columns
2) Clean up the data
3) Copy the data in the right order to the target table(s).
The target tables will have all their constraints in place and need not
be removed or disabled. This guarantees a consistent database at all
times.
By the way: please note that when you enable constraints after they have
been disabled the existing data will *not* be validated. This means you
can introduce invalid data in the table.
Also note that when you disable a constraint, this is not just for your
connection, but server wide. So if you do not insert invalid data,
another user might...
Hope this helps,
Gert-Jan
Leida wrote:
> Isn't there a way, other then Enterprise Manager, to disable and / or enable
> constraints, in particular primary and foreign keys? I am migrating data
> daily from one system to SQL and to disable and enable manually is
> inconvenient and combersome. I looked through BOL and cannot find a direct
> answer on how to create a process to automatically disable and / or enable
> constraints. Thanks.|||Thank you for your response. I have not tried this way of loading the data. I
will definately try this out.
"Gert-Jan Strik" wrote:
> Leida,
> Importing dirty data is a common problem. This is typically handled as
> follows:
> 1) Load the data in a load table. This is a table with no constraints,
> and may just have varchar columns
> 2) Clean up the data
> 3) Copy the data in the right order to the target table(s).
> The target tables will have all their constraints in place and need not
> be removed or disabled. This guarantees a consistent database at all
> times.
> By the way: please note that when you enable constraints after they have
> been disabled the existing data will *not* be validated. This means you
> can introduce invalid data in the table.
> Also note that when you disable a constraint, this is not just for your
> connection, but server wide. So if you do not insert invalid data,
> another user might...
> Hope this helps,
> Gert-Jan
>
> Leida wrote:
> >
> > Isn't there a way, other then Enterprise Manager, to disable and / or enable
> > constraints, in particular primary and foreign keys? I am migrating data
> > daily from one system to SQL and to disable and enable manually is
> > inconvenient and combersome. I looked through BOL and cannot find a direct
> > answer on how to create a process to automatically disable and / or enable
> > constraints. Thanks.
>|||Thank you for your response, and the syntax. It was helpful.
"Mark Wilden" wrote:
> "Leida" <Leida@.discussions.microsoft.com> wrote in message
> news:AD81D4EF-060C-41FA-82D0-A223EB41BD9A@.microsoft.com...
> > Isn't there a way, other then Enterprise Manager, to disable and / or
> enable
> > constraints, in particular primary and foreign keys?
> ALTER TABLE tablename NOCHECK CONSTRAINT ALL
>
>

Constraints...

I am creating the following constraints on a table and keep getting some
errors... what is the problem?
ALTER TABLE [dbo].[CMStb] ADD
CONSTRAINT [FK_CMStb_RecipDemotb] FOREIGN KEY
(
[OriginalRecipid]
) REFERENCES [dbo].[RecipDemotb] (
[OriginalRecipid]
) ON DELETE CASCADE NOT FOR REPLICATION
GO
ALTER TABLE [dbo].[Eligibilitytb] ADD
CONSTRAINT [FK_Eligibilitytb_RecipDemotb] FOREIGN KEY
(
[OriginalRecipid]
) REFERENCES [dbo].[RecipDemotb] (
[OriginalRecipid]
) ON DELETE CASCADE NOT FOR REPLICATION
GO
Error Message
Server: Msg 547, Level 16, State 1, Line 1
ALTER TABLE statement conflicted with COLUMN FOREIGN KEY constraint
'FK_CMStb_RecipDemotb'. The conflict occurred in database 'EDITPS', table
'RecipDemotb', column 'OriginalRecipid'.
Server: Msg 547, Level 16, State 1, Line 1
ALTER TABLE statement conflicted with COLUMN FOREIGN KEY constraint
'FK_Eligibilitytb_RecipDemotb'. The conflict occurred in database 'EDITPS',
table 'RecipDemotb', column 'OriginalRecipid'.
Sounds like the tables already have data that violate the foreign key
constraint. Try this:
SELECT * FROM dbo.CMStb
WHERE OriginalRecipid NOT IN
(
SELECT OriginalRecipid FROM dbo.RecipDemotb GROUP BY OriginalRecipid
)
If you get rows back, this is why the constraint fails.
(Is the "tb" at the end of the object name meant to stand for "table"? If
so, and Celko spots it, heaven help you.)
http://www.aspfaq.com/
(Reverse address to reply.)
"stoko" <stoko@.discussions.microsoft.com> wrote in message
news:9C5527C1-A010-4BC6-B78C-0755FF93BCD5@.microsoft.com...
> I am creating the following constraints on a table and keep getting some
> errors... what is the problem?
> ALTER TABLE [dbo].[CMStb] ADD
> CONSTRAINT [FK_CMStb_RecipDemotb] FOREIGN KEY
> (
> [OriginalRecipid]
> ) REFERENCES [dbo].[RecipDemotb] (
> [OriginalRecipid]
> ) ON DELETE CASCADE NOT FOR REPLICATION
> GO
> ALTER TABLE [dbo].[Eligibilitytb] ADD
> CONSTRAINT [FK_Eligibilitytb_RecipDemotb] FOREIGN KEY
> (
> [OriginalRecipid]
> ) REFERENCES [dbo].[RecipDemotb] (
> [OriginalRecipid]
> ) ON DELETE CASCADE NOT FOR REPLICATION
> GO
>
> Error Message
> Server: Msg 547, Level 16, State 1, Line 1
> ALTER TABLE statement conflicted with COLUMN FOREIGN KEY constraint
> 'FK_CMStb_RecipDemotb'. The conflict occurred in database 'EDITPS', table
> 'RecipDemotb', column 'OriginalRecipid'.
> Server: Msg 547, Level 16, State 1, Line 1
> ALTER TABLE statement conflicted with COLUMN FOREIGN KEY constraint
> 'FK_Eligibilitytb_RecipDemotb'. The conflict occurred in database
'EDITPS',
> table 'RecipDemotb', column 'OriginalRecipid'.
>

Constraints...

I am creating the following constraints on a table and keep getting some
errors... what is the problem'
ALTER TABLE [dbo].[CMStb] ADD
CONSTRAINT [FK_CMStb_RecipDemotb] FOREIGN KEY
(
[OriginalRecipid]
) REFERENCES [dbo].[RecipDemotb] (
[OriginalRecipid]
) ON DELETE CASCADE NOT FOR REPLICATION
GO
ALTER TABLE [dbo].[Eligibilitytb] ADD
CONSTRAINT [FK_Eligibilitytb_RecipDemotb] FOREIGN KEY
(
[OriginalRecipid]
) REFERENCES [dbo].[RecipDemotb] (
[OriginalRecipid]
) ON DELETE CASCADE NOT FOR REPLICATION
GO
Error Message
Server: Msg 547, Level 16, State 1, Line 1
ALTER TABLE statement conflicted with COLUMN FOREIGN KEY constraint
'FK_CMStb_RecipDemotb'. The conflict occurred in database 'EDITPS', table
'RecipDemotb', column 'OriginalRecipid'.
Server: Msg 547, Level 16, State 1, Line 1
ALTER TABLE statement conflicted with COLUMN FOREIGN KEY constraint
'FK_Eligibilitytb_RecipDemotb'. The conflict occurred in database 'EDITPS',
table 'RecipDemotb', column 'OriginalRecipid'.Sounds like the tables already have data that violate the foreign key
constraint. Try this:
SELECT * FROM dbo.CMStb
WHERE OriginalRecipid NOT IN
(
SELECT OriginalRecipid FROM dbo.RecipDemotb GROUP BY OriginalRecipid
)
If you get rows back, this is why the constraint fails.
(Is the "tb" at the end of the object name meant to stand for "table"? If
so, and Celko spots it, heaven help you.)
--
http://www.aspfaq.com/
(Reverse address to reply.)
"stoko" <stoko@.discussions.microsoft.com> wrote in message
news:9C5527C1-A010-4BC6-B78C-0755FF93BCD5@.microsoft.com...
> I am creating the following constraints on a table and keep getting some
> errors... what is the problem'
> ALTER TABLE [dbo].[CMStb] ADD
> CONSTRAINT [FK_CMStb_RecipDemotb] FOREIGN KEY
> (
> [OriginalRecipid]
> ) REFERENCES [dbo].[RecipDemotb] (
> [OriginalRecipid]
> ) ON DELETE CASCADE NOT FOR REPLICATION
> GO
> ALTER TABLE [dbo].[Eligibilitytb] ADD
> CONSTRAINT [FK_Eligibilitytb_RecipDemotb] FOREIGN KEY
> (
> [OriginalRecipid]
> ) REFERENCES [dbo].[RecipDemotb] (
> [OriginalRecipid]
> ) ON DELETE CASCADE NOT FOR REPLICATION
> GO
>
> Error Message
> Server: Msg 547, Level 16, State 1, Line 1
> ALTER TABLE statement conflicted with COLUMN FOREIGN KEY constraint
> 'FK_CMStb_RecipDemotb'. The conflict occurred in database 'EDITPS', table
> 'RecipDemotb', column 'OriginalRecipid'.
> Server: Msg 547, Level 16, State 1, Line 1
> ALTER TABLE statement conflicted with COLUMN FOREIGN KEY constraint
> 'FK_Eligibilitytb_RecipDemotb'. The conflict occurred in database
'EDITPS',
> table 'RecipDemotb', column 'OriginalRecipid'.
>

Constraints...

I am creating the following constraints on a table and keep getting some
errors... what is the problem'
ALTER TABLE [dbo].[CMStb] ADD
CONSTRAINT [FK_CMStb_RecipDemotb] FOREIGN KEY
(
[OriginalRecipid]
) REFERENCES [dbo].[RecipDemotb] (
[OriginalRecipid]
) ON DELETE CASCADE NOT FOR REPLICATION
GO
ALTER TABLE [dbo].[Eligibilitytb] ADD
CONSTRAINT [FK_Eligibilitytb_RecipDemotb] FOREIGN KEY
(
[OriginalRecipid]
) REFERENCES [dbo].[RecipDemotb] (
[OriginalRecipid]
) ON DELETE CASCADE NOT FOR REPLICATION
GO
Error Message
Server: Msg 547, Level 16, State 1, Line 1
ALTER TABLE statement conflicted with COLUMN FOREIGN KEY constraint
'FK_CMStb_RecipDemotb'. The conflict occurred in database 'EDITPS', table
'RecipDemotb', column 'OriginalRecipid'.
Server: Msg 547, Level 16, State 1, Line 1
ALTER TABLE statement conflicted with COLUMN FOREIGN KEY constraint
'FK_Eligibilitytb_RecipDemotb'. The conflict occurred in database 'EDITPS',
table 'RecipDemotb', column 'OriginalRecipid'.Sounds like the tables already have data that violate the foreign key
constraint. Try this:
SELECT * FROM dbo.CMStb
WHERE OriginalRecipid NOT IN
(
SELECT OriginalRecipid FROM dbo.RecipDemotb GROUP BY OriginalRecipid
)
If you get rows back, this is why the constraint fails.
(Is the "tb" at the end of the object name meant to stand for "table"? If
so, and Celko spots it, heaven help you.)
http://www.aspfaq.com/
(Reverse address to reply.)
"stoko" <stoko@.discussions.microsoft.com> wrote in message
news:9C5527C1-A010-4BC6-B78C-0755FF93BCD5@.microsoft.com...
> I am creating the following constraints on a table and keep getting some
> errors... what is the problem'
> ALTER TABLE [dbo].[CMStb] ADD
> CONSTRAINT [FK_CMStb_RecipDemotb] FOREIGN KEY
> (
> [OriginalRecipid]
> ) REFERENCES [dbo].[RecipDemotb] (
> [OriginalRecipid]
> ) ON DELETE CASCADE NOT FOR REPLICATION
> GO
> ALTER TABLE [dbo].[Eligibilitytb] ADD
> CONSTRAINT [FK_Eligibilitytb_RecipDemotb] FOREIGN KEY
> (
> [OriginalRecipid]
> ) REFERENCES [dbo].[RecipDemotb] (
> [OriginalRecipid]
> ) ON DELETE CASCADE NOT FOR REPLICATION
> GO
>
> Error Message
> Server: Msg 547, Level 16, State 1, Line 1
> ALTER TABLE statement conflicted with COLUMN FOREIGN KEY constraint
> 'FK_CMStb_RecipDemotb'. The conflict occurred in database 'EDITPS', table
> 'RecipDemotb', column 'OriginalRecipid'.
> Server: Msg 547, Level 16, State 1, Line 1
> ALTER TABLE statement conflicted with COLUMN FOREIGN KEY constraint
> 'FK_Eligibilitytb_RecipDemotb'. The conflict occurred in database
'EDITPS',
> table 'RecipDemotb', column 'OriginalRecipid'.
>

constraints on views

Hi.
At first let me present the problem:
T(a int, b int, c bit)
I would like to see to it that (a,b) is unique where c = 1. The easiest way
to accomplish this would be creating a view like this:
create view V as select a,b from T where c = 1
And then putting a unique constraint on (a,b) in V.
I am open to other solutions as well, of course.
Thx,
Agoston
Create an indexed view with a unique index on (a,b).
David Portas
SQL Server MVP
|||"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:570905D0-1D39-4CAF-995C-2DD91A2EC6B4@.microsoft.com...
> Create an indexed view with a unique index on (a,b).
Does this feature exists in SQL Server 7? Unfortunately I'm forced to work
with that, and I remember reading about this feature not being present in
SQL 7.
Thx,
Agoston

> --
> David Portas
> SQL Server MVP
> --
>
|||You are correct, indexed views are not available in SQL 7. Probably the best
thing you can do in SQL 7 is to create a trigger that enforces this for you.
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.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
"Agoston Bejo" <gusz1@.freemail.hu> wrote in message
news:evpzFRF2EHA.3408@.tk2msftngp13.phx.gbl...
> Hi.
> At first let me present the problem:
> T(a int, b int, c bit)
> I would like to see to it that (a,b) is unique where c = 1. The easiest
way
> to accomplish this would be creating a view like this:
> create view V as select a,b from T where c = 1
> And then putting a unique constraint on (a,b) in V.
> I am open to other solutions as well, of course.
> Thx,
> Agoston
>
|||It is not clear from your message if you want to use regular view or
indexed view, but in any case you can only have constraint on indexed
view.
Another way that you can use is to work with stored procedure.
Instead of giving the users permissions to work directly with the
table, you can grant them execute permissions on a stored procedure
that inserts the data to the table (or rejects the data) according to
your criteria.
Another way is to use trigger or instead of trigger (instead of
trigger would be better).
My favorite of all of those is the stored procedure.
Adi
|||You could also consider a UNIQUE CONSTRAINT on the combination of a, b, and
c. What is the rule when c <> 1?
Sincerely,
Anthony Thomas

"Agoston Bejo" <gusz1@.freemail.hu> wrote in message
news:evpzFRF2EHA.3408@.tk2msftngp13.phx.gbl...
Hi.
At first let me present the problem:
T(a int, b int, c bit)
I would like to see to it that (a,b) is unique where c = 1. The easiest way
to accomplish this would be creating a view like this:
create view V as select a,b from T where c = 1
And then putting a unique constraint on (a,b) in V.
I am open to other solutions as well, of course.
Thx,
Agoston

constraints on views

Hi.
At first let me present the problem:
T(a int, b int, c bit)
I would like to see to it that (a,b) is unique where c = 1. The easiest way
to accomplish this would be creating a view like this:
create view V as select a,b from T where c = 1
And then putting a unique constraint on (a,b) in V.
I am open to other solutions as well, of course.
Thx,
AgostonCreate an indexed view with a unique index on (a,b).
--
David Portas
SQL Server MVP
--|||"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:570905D0-1D39-4CAF-995C-2DD91A2EC6B4@.microsoft.com...
> Create an indexed view with a unique index on (a,b).
Does this feature exists in SQL Server 7? Unfortunately I'm forced to work
with that, and I remember reading about this feature not being present in
SQL 7.
Thx,
Agoston
> --
> David Portas
> SQL Server MVP
> --
>|||You are correct, indexed views are not available in SQL 7. Probably the best
thing you can do in SQL 7 is to create a trigger that enforces this for you.
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.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
"Agoston Bejo" <gusz1@.freemail.hu> wrote in message
news:evpzFRF2EHA.3408@.tk2msftngp13.phx.gbl...
> Hi.
> At first let me present the problem:
> T(a int, b int, c bit)
> I would like to see to it that (a,b) is unique where c = 1. The easiest
way
> to accomplish this would be creating a view like this:
> create view V as select a,b from T where c = 1
> And then putting a unique constraint on (a,b) in V.
> I am open to other solutions as well, of course.
> Thx,
> Agoston
>|||It is not clear from your message if you want to use regular view or
indexed view, but in any case you can only have constraint on indexed
view.
Another way that you can use is to work with stored procedure.
Instead of giving the users permissions to work directly with the
table, you can grant them execute permissions on a stored procedure
that inserts the data to the table (or rejects the data) according to
your criteria.
Another way is to use trigger or instead of trigger (instead of
trigger would be better).
My favorite of all of those is the stored procedure.
Adi|||You could also consider a UNIQUE CONSTRAINT on the combination of a, b, and
c. What is the rule when c <> 1?
Sincerely,
Anthony Thomas
"Agoston Bejo" <gusz1@.freemail.hu> wrote in message
news:evpzFRF2EHA.3408@.tk2msftngp13.phx.gbl...
Hi.
At first let me present the problem:
T(a int, b int, c bit)
I would like to see to it that (a,b) is unique where c = 1. The easiest way
to accomplish this would be creating a view like this:
create view V as select a,b from T where c = 1
And then putting a unique constraint on (a,b) in V.
I am open to other solutions as well, of course.
Thx,
Agoston

constraints on views

Hi.
At first let me present the problem:
T(a int, b int, c bit)
I would like to see to it that (a,b) is unique where c = 1. The easiest way
to accomplish this would be creating a view like this:
create view V as select a,b from T where c = 1
And then putting a unique constraint on (a,b) in V.
I am open to other solutions as well, of course.
Thx,
AgostonCreate an indexed view with a unique index on (a,b).
David Portas
SQL Server MVP
--|||"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:570905D0-1D39-4CAF-995C-2DD91A2EC6B4@.microsoft.com...
> Create an indexed view with a unique index on (a,b).
Does this feature exists in SQL Server 7? Unfortunately I'm forced to work
with that, and I remember reading about this feature not being present in
SQL 7.
Thx,
Agoston

> --
> David Portas
> SQL Server MVP
> --
>|||You are correct, indexed views are not available in SQL 7. Probably the best
thing you can do in SQL 7 is to create a trigger that enforces this for you.
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.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
"Agoston Bejo" <gusz1@.freemail.hu> wrote in message
news:evpzFRF2EHA.3408@.tk2msftngp13.phx.gbl...
> Hi.
> At first let me present the problem:
> T(a int, b int, c bit)
> I would like to see to it that (a,b) is unique where c = 1. The easiest
way
> to accomplish this would be creating a view like this:
> create view V as select a,b from T where c = 1
> And then putting a unique constraint on (a,b) in V.
> I am open to other solutions as well, of course.
> Thx,
> Agoston
>|||It is not clear from your message if you want to use regular view or
indexed view, but in any case you can only have constraint on indexed
view.
Another way that you can use is to work with stored procedure.
Instead of giving the users permissions to work directly with the
table, you can grant them execute permissions on a stored procedure
that inserts the data to the table (or rejects the data) according to
your criteria.
Another way is to use trigger or instead of trigger (instead of
trigger would be better).
My favorite of all of those is the stored procedure.
Adi|||You could also consider a UNIQUE CONSTRAINT on the combination of a, b, and
c. What is the rule when c <> 1?
Sincerely,
Anthony Thomas
"Agoston Bejo" <gusz1@.freemail.hu> wrote in message
news:evpzFRF2EHA.3408@.tk2msftngp13.phx.gbl...
Hi.
At first let me present the problem:
T(a int, b int, c bit)
I would like to see to it that (a,b) is unique where c = 1. The easiest way
to accomplish this would be creating a view like this:
create view V as select a,b from T where c = 1
And then putting a unique constraint on (a,b) in V.
I am open to other solutions as well, of course.
Thx,
Agoston

Constraints on Table Field

Hi,

I have a table which saves attachment files.

I need to create a constraint on table which will restrict the size of attachments in the table. How this can be done? I know we can create a check constraint on table but how to restrict the field size? Pls. advise. Thanks in advance.

Are you storing your files as text in the table? If so you might be able to do something checking the datalength like:

create table dbo.testor
( targetFile nvarchar(max)
check ( datalength(targetFile) <= 30000 )
)

|||

Thanks for your response.

I am using image as datatype to attach files. I need to restrict this column size to only accept 50 kb size.

Thanks,

constraints on char,varchar

hI,
Is there is a way to find constraints on columns char and
varchar alone..(through query)
Sridhar.
Hi
You could try something like:
SELECT object_name(c.id), c.name, t.name, s.*
FROM sysconstraints s JOIN syscolumns c on c.id = s.id and c.colid = s.colid
JOIN systypes t on c.xtype = t.xtype and t.name LIKE '%char%'
John
<anonymous@.discussions.microsoft.com> wrote in message
news:91d001c43330$171e8760$a001280a@.phx.gbl...
> hI,
> Is there is a way to find constraints on columns char and
> varchar alone..(through query)
> Sridhar.
|||Thanks John..
Is it possible to find out the indexes on char columns

>--Original Message--
>Hi
>You could try something like:
>SELECT object_name(c.id), c.name, t.name, s.*
>FROM sysconstraints s JOIN syscolumns c on c.id = s.id
and c.colid = s.colid
>JOIN systypes t on c.xtype = t.xtype and t.name LIKE '%
char%'[vbcol=seagreen]
>John
><anonymous@.discussions.microsoft.com> wrote in message
>news:91d001c43330$171e8760$a001280a@.phx.gbl...
and
>
>.
>
|||> Is it possible to find out the indexes on char columns
I modified John's original script for this requirement:
SELECT object_name(i.id), i.name, c.name, t.name
FROM sysindexes i
JOIN sysindexkeys ik ON ik.id = i.id
JOIN syscolumns c ON c.id = ik.id AND c.colid = ik.colid
JOIN systypes t ON c.xtype = t.xtype AND t.name LIKE '%char%'
ORDER BY object_name(i.id), i.name, c.name
Hope this helps.
Dan Guzman
SQL Server MVP
<anonymous@.discussions.microsoft.com> wrote in message
news:8f6401c43345$6601f1e0$a401280a@.phx.gbl...[vbcol=seagreen]
> Thanks John..
> Is it possible to find out the indexes on char columns
>
> and c.colid = s.colid
> char%'
> and

constraints on char,varchar

hI,
Is there is a way to find constraints on columns char and
varchar alone..(through query)
Sridhar.Hi
You could try something like:
SELECT object_name(c.id), c.name, t.name, s.*
FROM sysconstraints s JOIN syscolumns c on c.id = s.id and c.colid = s.colid
JOIN systypes t on c.xtype = t.xtype and t.name LIKE '%char%'
John
<anonymous@.discussions.microsoft.com> wrote in message
news:91d001c43330$171e8760$a001280a@.phx.gbl...
> hI,
> Is there is a way to find constraints on columns char and
> varchar alone..(through query)
> Sridhar.|||Thanks John..
Is it possible to find out the indexes on char columns

>--Original Message--
>Hi
>You could try something like:
>SELECT object_name(c.id), c.name, t.name, s.*
>FROM sysconstraints s JOIN syscolumns c on c.id = s.id
and c.colid = s.colid
>JOIN systypes t on c.xtype = t.xtype and t.name LIKE '%
char%'
>John
><anonymous@.discussions.microsoft.com> wrote in message
>news:91d001c43330$171e8760$a001280a@.phx.gbl...
and[vbcol=seagreen]
>
>.
>|||> Is it possible to find out the indexes on char columns
I modified John's original script for this requirement:
SELECT object_name(i.id), i.name, c.name, t.name
FROM sysindexes i
JOIN sysindexkeys ik ON ik.id = i.id
JOIN syscolumns c ON c.id = ik.id AND c.colid = ik.colid
JOIN systypes t ON c.xtype = t.xtype AND t.name LIKE '%char%'
ORDER BY object_name(i.id), i.name, c.name
Hope this helps.
Dan Guzman
SQL Server MVP
<anonymous@.discussions.microsoft.com> wrote in message
news:8f6401c43345$6601f1e0$a401280a@.phx.gbl...[vbcol=seagreen]
> Thanks John..
> Is it possible to find out the indexes on char columns
>
> and c.colid = s.colid
> char%'
> and

constraints on char,varchar

hI,
Is there is a way to find constraints on columns char and
varchar alone..(through query)
Sridhar.Hi
You could try something like:
SELECT object_name(c.id), c.name, t.name, s.*
FROM sysconstraints s JOIN syscolumns c on c.id = s.id and c.colid = s.colid
JOIN systypes t on c.xtype = t.xtype and t.name LIKE '%char%'
John
<anonymous@.discussions.microsoft.com> wrote in message
news:91d001c43330$171e8760$a001280a@.phx.gbl...
> hI,
> Is there is a way to find constraints on columns char and
> varchar alone..(through query)
> Sridhar.|||Thanks John..
Is it possible to find out the indexes on char columns
>--Original Message--
>Hi
>You could try something like:
>SELECT object_name(c.id), c.name, t.name, s.*
>FROM sysconstraints s JOIN syscolumns c on c.id = s.id
and c.colid = s.colid
>JOIN systypes t on c.xtype = t.xtype and t.name LIKE '%
char%'
>John
><anonymous@.discussions.microsoft.com> wrote in message
>news:91d001c43330$171e8760$a001280a@.phx.gbl...
>> hI,
>> Is there is a way to find constraints on columns char
and
>> varchar alone..(through query)
>> Sridhar.
>
>.
>|||> Is it possible to find out the indexes on char columns
I modified John's original script for this requirement:
SELECT object_name(i.id), i.name, c.name, t.name
FROM sysindexes i
JOIN sysindexkeys ik ON ik.id = i.id
JOIN syscolumns c ON c.id = ik.id AND c.colid = ik.colid
JOIN systypes t ON c.xtype = t.xtype AND t.name LIKE '%char%'
ORDER BY object_name(i.id), i.name, c.name
--
Hope this helps.
Dan Guzman
SQL Server MVP
<anonymous@.discussions.microsoft.com> wrote in message
news:8f6401c43345$6601f1e0$a401280a@.phx.gbl...
> Thanks John..
> Is it possible to find out the indexes on char columns
>
> >--Original Message--
> >Hi
> >
> >You could try something like:
> >
> >SELECT object_name(c.id), c.name, t.name, s.*
> >FROM sysconstraints s JOIN syscolumns c on c.id = s.id
> and c.colid = s.colid
> >JOIN systypes t on c.xtype = t.xtype and t.name LIKE '%
> char%'
> >
> >John
> >
> ><anonymous@.discussions.microsoft.com> wrote in message
> >news:91d001c43330$171e8760$a001280a@.phx.gbl...
> >> hI,
> >>
> >> Is there is a way to find constraints on columns char
> and
> >> varchar alone..(through query)
> >>
> >> Sridhar.
> >
> >
> >.
> >

Constraints in SQL server

i have a question about constraints in SQL server.

i have three tables:

Client
---
clientID
name
etc...

Bank
---
BankID (a virtual number, identity)
ClientID (FK: Client)
BankAccountNumber
BankName

Order
---
OrderID
ClientID
BankID

a Client an have one or more bankAccounts.. if he place an order, he can select wich bankaccount will be used to pay.

how do i have to set the constraints so that the bankAccount in Order, is allways of THAT client in order.?

Thanx...can someone give me some help? please...|||I'm not sure exactly what you're asking. It seems that if your application is going to create an order for a customer and then let the customer determine which bank account they're going to use for the order, then you have the appropriate information already. You have bank account # (or bankid) and you have customer information (clientid).

BTW, it appears that ClientID is redundant data in the Order table. Through BankID you can derive ClientID.|||It sounds like you might need a trigger. Check out this article and see if the information contained within might help:Enforcing Business Rules with Triggers.

Terri

Constraints in SQL server

I wanna know how to define constraints in sql server.
everytime i try something it says "error validating constraint'

does someone have an example how a constraint have to look like?

PS: i want to the constraint that check's this:
i have an order.. an order haves orderlines
and we have a 'contract' .. and contract have 'contractlines'

.. an order can became a 'contract' .. and some of the orderlines will be 'contractlines'

so a contract have a FK:orderID.. and a contractline have FK:contractID&orderlineID

i want to check if the orderlineID in contractline exists in the correspondending order..

>>>something like:
orderlineID exists in ( select orderlineID from orderline left join
order on order.orderID = orderLine.orderID left join contract on
contract.orderID = order.orderID left join contract on contractLine.contractID = contract.contractIDIt sounds like you want foreign key constraints:

create table Orders
(
OrderID int not null,
primary key (OrderID),
)
create table OrderLines
(
OrderID int not null,
OrderLineID int not null,
primary key (OrderID, OrderLineID),
)
create table Contracts
(
ContractID int not null,
OrderID int null,
primary key (ContractID),
foreign key (OrderID) references Orders(OrderID),
)
create table ContractLines
(
ContractID int not null,
ContractLineID int not null,
OrderID int null,
OrderLineID int null,
primary key (ContractID, ContractLineID),
foreign key (OrderID, OrderLineID) references OrderLines(OrderID, OrderLineID),
)

I don't think this is a very good design for several reasons, including the following:
1) I used nulls
2) I have no idea if the various IDs are natural or surrogate keys
3) I assumed a lot of things about how the four concepts orders, contracts, order lines and contract lines are related

constraints implemented in Triggers and replication

Hi Friends,
I have some problems with my merge replication because i have the
constraints created with Erwin implemented in triggers not like foreign
keys, i'm looking for a tool or strategy for converting this constraints to
FK's.
Please help.
Hi Paul,
My problem is in my merge replication. I have'nt Foreign Keys in then BD,
when the replication run, the insert or update or delete statment cause error
because dont have a order of insertion or update or delete because in our
application we have a order.
think you.
Please help.
"Paul Ibison" wrote:

> What problems are you seeing from the triggers? If you
> don't want them to fire as a result of the replication
> process, you can specify NOT FOR REPLICATION on the
> trigger definition. If the problem is that the triggers
> are causing recursion, you can investigate
> sp_check_for_sync_trigger.
> HTH,
> Paul Ibison (SQL Server MVP)
>
>
|||Chouaib,
using NOT FOR REPLICATION in the trigger definition will
mean that the triggers which you have to check
Referential Integrity will not fire during the
replication process. This would seem to fix your issue?
Rgds,
Paul Ibison (SQL Server MVP)
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||thank you paul,
i try that but i have more that one thousands of triggers.
regards.
"Paul Ibison" wrote:

> Chouaib,
> using NOT FOR REPLICATION in the trigger definition will
> mean that the triggers which you have to check
> Referential Integrity will not fire during the
> replication process. This would seem to fix your issue?
> Rgds,
> Paul Ibison (SQL Server MVP)
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
|||If you script them out into a single file using EM, you
might be asble to replace 'AS' with 'NOT FOR REPLICATION
AS' then run the script.
Rgds,
Paul Ibison (SQL Server MVP)
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||THANK YOU PAUL'
I DO THAT.
"Paul Ibison" wrote:

> If you script them out into a single file using EM, you
> might be asble to replace 'AS' with 'NOT FOR REPLICATION
> AS' then run the script.
> Rgds,
> Paul Ibison (SQL Server MVP)
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>

constraints for e-mail

Is it possible to write a check constraint for a column in a table which says that the entry must contain a '@.' and a '.'? (It's an email address field)
I'm guessing this is the best way to do it anyway,
Thanks in advance, newbie!USE Northwind
GO

CREATE TABLE myTable99(
Col1 varchar(255)
CONSTRAINT myTable99_ck1
CHECK ( LEN(Col1)=LEN(REPLACE(Col1,'@.',''))+1
AND LEN(Col1)>LEN(REPLACE(Col1,'.',''))
)
)

INSERT INTO myTable99(Col1) SELECT 'brett.kaiser@.somewhere.com'
INSERT INTO myTable99(Col1) SELECT 'brettkaiser@.somewhere'
INSERT INTO myTable99(Col1) SELECT 'brett.kaiser somewhere.com'

SELECT * FROM myTable99
GO

DROP TABLE myTable99
GO|||What? You don't like it?

:-o|||How about

check (col1 like '%@.%.%')|||Originally posted by MCrowley
How about

check (col1 like '%@.%.%')

That'll allow more than 1 @. sign...

CREATE TABLE myTable99(
Col1 varchar(255)
CONSTRAINT myTable99_ck1
CHECK ( (col1 like '%@.%.%')
)
)

INSERT INTO myTable99(Col1) SELECT 'brett.kaiser@.somewhere.com'
INSERT INTO myTable99(Col1) SELECT 'brett@.kaiser@.somewhere.com'
INSERT INTO myTable99(Col1) SELECT 'brettkaiser@.somewhere'
INSERT INTO myTable99(Col1) SELECT 'brett.kaiser somewhere.com'

SELECT * FROM myTable99
GO

DROP TABLE myTable99
GO|||thanks guys thats great! :)

sorry i took so long to get back|||While i'm on..

could anyone help me with this trigger!!

Create Trigger Maintain_employeedata
On payroll_tbl
After update on employee_tbl
For Each Row
Begin
Insert into payroll_tbl
(payroll_id, employee_id)
Values
(:New.payroll_id, :Old.employee_id);
End;

This does not seem to work, it produces the following errors:

Server: Msg 156, Level 15, State 1, Procedure Maintain_SurveyorData, Line 3
Incorrect syntax near the keyword 'on'.
Server: Msg 170, Level 15, State 1, Procedure Maintain_SurveyorData, Line 9
Line 9: Incorrect syntax near ':'.

Basically when I add a new employee to my employee table it should add this employee to the payroll table via their employee_id.. and subsequently create a new payroll_id to match. the payroll_id is the primary key of the table and should be an increment of the last one (eg if we have got up to P0007, it should create P0008 for the new person).

Is this actually possible? its really bugging me!|||First it looks like you have an Oracle background...

Second, you're inserting in to the same table, after an update to the sane table...I don;t understand this..

Third SQL does not have a FOR EACH ROW syntax, you have to join to the virtual table "inserted" (oracles new) and "deleted (oracles old)..

fourth, the error message is for something else..

CREATE TRIGGER <triiger_name> ON Table

is correct

AFTER UPDATE (which isn't required, is the default) doesn't use the ON Table syntax...|||ah, well actually I don't have a background in any form! that was just syntax i picked up, and i'm only working on MS SQL server using the query analyzer :|

I have these ammendments, but I don't think ive quite grasped what youre saying?

Create Trigger Maintain_employeedata on payroll_tbl
After update on employee_tbl
For Each Row
Begin
Insert into payroll_tbl
(payroll_id, employee_id)
Values
(:inserted.employee_id, :deleted.payroll_id);
End;|||I don't pretend to understand what you're trying to do...

but at least this should compile...

You need to make sure you identify the key of the row...is it employeeId?

CREATE TRIGGER Maintain_employeedata ON Employee_tbl
FOR UPDATE
BEGIN
INSERT INTO payroll_tbl (payroll_id, employee_id)
SELECT i.employee_id, d.payroll_id
FROM inserted i
INNER JOIN deleted d
ON i.key of the row = d.key of the row
END
GO|||yeah ive altered the keys so that they are right. i.employee_id and d.payroll_id

but i have

Server: Msg 156, Level 15, State 1, Procedure Maintain_employeedata, Line 3
Incorrect syntax near the keyword 'BEGIN'.|||I forgot the AS...place in a line before the BEGIN|||It's actually easier to help if you post the DDL of the table, and some sample data, and some sample DML (The Updates statements)

Sample data should look like

INSERT INTO myTable99(col1,col2,col3,ect)
SELECT 'a',1','x',ect UINION ALL
SELECT 'a',1','x',ect UINION ALL
SELECT 'a',1','x',ect UINION ALL
SELECT 'a',1','x',ect UINION ALL
SELECT 'a',1','x',ect UINION ALL
ect

DDL looks like

CREATE TABEL mtTable99 (Col1, char(1), col2 int, ect...

You'll get answers that are correct, and fatse that way...

MOO

Constraints Error

Hopefully somebody here can help me, I am fairly new to SQL Server. I have
SQL Server 2000 running on a Windows 2000 Server. I have a table called
StudentInfo with a field called Date. I want the Date field to only accept
the current date. This field gets updated by teachers every day when they do
their attendance. In Access I used a validation rule of date(). In SQL I
created a contraint on the StudentInfo table and in the constraint
expression I put Date = date(). I have unchecked "validate existing data"
but left the other 2 boxes checked. However, I get the following error -
Error validating check constraint. I think I probably have the wrong syntax
in my contraint expression, can anybody help?
Thanks.
Kevin> Hopefully somebody here can help me, I am fairly new to SQL Server. I have
> SQL Server 2000 running on a Windows 2000 Server. I have a table called
> StudentInfo with a field called Date. I want the Date field to only accept
> the current date. This field gets updated by teachers every day when they
do
> their attendance. In Access I used a validation rule of date(). In SQL I
> created a contraint on the StudentInfo table and in the constraint
> expression I put Date = date(). I have unchecked "validate existing data"
> but left the other 2 boxes checked. However, I get the following error -
> Error validating check constraint. I think I probably have the wrong
syntax
> in my contraint expression, can anybody help?
Set the following constraint:
Date = GETDATE()
sincerely,
--
Sebastian K. Zaklada
Skilled Software
http://www.skilledsoftware.com
This posting is provided "AS IS" with no warranties, and confers no rights.|||Thank You.
Kevin
"Sebastian K. Zaklada" <szaklada-dont-like-spam@.skilledsoftware.com> wrote
in message news:%23tJ18zy7DHA.3860@.tk2msftngp13.phx.gbl...
> > Hopefully somebody here can help me, I am fairly new to SQL Server. I
have
> > SQL Server 2000 running on a Windows 2000 Server. I have a table called
> > StudentInfo with a field called Date. I want the Date field to only
accept
> > the current date. This field gets updated by teachers every day when
they
> do
> > their attendance. In Access I used a validation rule of date(). In SQL I
> > created a contraint on the StudentInfo table and in the constraint
> > expression I put Date = date(). I have unchecked "validate existing
data"
> > but left the other 2 boxes checked. However, I get the following error -
> > Error validating check constraint. I think I probably have the wrong
> syntax
> > in my contraint expression, can anybody help?
> Set the following constraint:
> Date = GETDATE()
> sincerely,
> --
> Sebastian K. Zaklada
> Skilled Software
> http://www.skilledsoftware.com
> This posting is provided "AS IS" with no warranties, and confers no
rights.
>

Constraints Error

Hopefully somebody here can help me, I am fairly new to SQL Server. I have
SQL Server 2000 running on a Windows 2000 Server. I have a table called
StudentInfo with a field called Date. I want the Date field to only accept
the current date. This field gets updated by teachers every day when they do
their attendance. In Access I used a validation rule of date(). In SQL I
created a contraint on the StudentInfo table and in the constraint
expression I put Date = date(). I have unchecked "validate existing data"
but left the other 2 boxes checked. However, I get the following error -
Error validating check constraint. I think I probably have the wrong syntax
in my contraint expression, can anybody help?
Thanks.
Kevin> Hopefully somebody here can help me, I am fairly new to SQL Server. I have
> SQL Server 2000 running on a Windows 2000 Server. I have a table called
> StudentInfo with a field called Date. I want the Date field to only accept
> the current date. This field gets updated by teachers every day when they
do
> their attendance. In Access I used a validation rule of date(). In SQL I
> created a contraint on the StudentInfo table and in the constraint
> expression I put Date = date(). I have unchecked "validate existing data"
> but left the other 2 boxes checked. However, I get the following error -
> Error validating check constraint. I think I probably have the wrong
syntax
> in my contraint expression, can anybody help?
Set the following constraint:
Date = GETDATE()
sincerely,
--
Sebastian K. Zaklada
Skilled Software
http://www.skilledsoftware.com
This posting is provided "AS IS" with no warranties, and confers no rights.|||Thank You.
Kevin
"Sebastian K. Zaklada" <szaklada-dont-like-spam@.skilledsoftware.com> wrote
in message news:%23tJ18zy7DHA.3860@.tk2msftngp13.phx.gbl...
have
accept
they
> do
data"
> syntax
> Set the following constraint:
> Date = GETDATE()
> sincerely,
> --
> Sebastian K. Zaklada
> Skilled Software
> http://www.skilledsoftware.com
> This posting is provided "AS IS" with no warranties, and confers no
rights.
>