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 and Foreign Keys....

Hi,
I am currently in the design phase. I have a question about the possibility
of creating a certain type of constraint. So far, I don't think its
possible.
For the sake of the example say I have two entities, Trees and Cars. I keep
track of entities in another entity called Entity Types. This would contain
car and tree as a record. Then I have another list of entities called
characteristics. Certain charateristics will only apply to the car, others
to the tree and some to both. Example characteristics would be model,
manufacturer, species, and name.
So I created an Entity Assignable Charateristics. This will hold which
charateristics can be applied to which entity. Some of those will be
required, others won't. I could add an attribute "Required" to keep track o
f
this.
Charateristics for instances cars or trees will be stored in either Entity
Charateristics or in Car Charateristics and Tree Charateristics.
Here's my question. If in Entity Assignable Charateristics there are
indicated certain required charateristics for trees, is there a way I can
enforce that in Entity Charateristics or Tree Charateristics.
Thanks in advance.
Pete>> I have two entities, Trees and Cars. I keep track of entities in another
entity called Entity Types. <<
This is an OO approach to the world and NOT an RDBMS. Separate types
of entieis have their own tables; there is no such monster as a
"magical, vague, general thingie" set. To be is to be something in
particular, not everythign in general.|||Start by googling for "EAV design flaws".
Inability to impose appropriate integrity constraints is the primary
drawback of approaches similar to the one you propose.
Anith|||Hi,
Thanks for the response. I just want to make sure I understand you
correctly. You think that I should have Trees and Cars. Then you think I
should have Tree Assignable Charaterisics and Car Assignable Charateristics.
These hold all the possible charateristics for a tree or car respectively.
This makes sense.
Now if I create a Tree Charateristics entity, which holds all the
charateristics for each instance of a tree, how can I make sure that the
charateristics that are placed into this entity come from the Tree Assignabl
e
Charateristics subset or Charateristics?
Additionally, how would required charateristics play into this?
Thanks again.
Pete
"--CELKO--" wrote:

> This is an OO approach to the world and NOT an RDBMS. Separate types
> of entieis have their own tables; there is no such monster as a
> "magical, vague, general thingie" set. To be is to be something in
> particular, not everythign in general.
>|||Hi,
Thanks for the comment. The reason I have the entities I do is so people
can easily create a new tree or car or charateristic. However, one person
may wish to make the charaterisic model for a car required, another may not.
Since you seem to think I have a flaw in my design, could you suggest anothe
r
way I might accomplish what it is I am trying to do? Thanks again.
Peter
"Anith Sen" wrote:

> Start by googling for "EAV design flaws".
> Inability to impose appropriate integrity constraints is the primary
> drawback of approaches similar to the one you propose.
> --
> Anith
>
>|||>> The reason I have the entities I do is so people can easily create a new
Without a comprehensive knowledge of your business model, others cannot
possibly determine the characteristics of an entity of your enterprise.
Rather than thinking about trees, cars and characteristics, think of trees
and cars as a set of entities having their own attributes. In loose terms,
each form an entity type & therefore belong to separate tables.
Think of each table as a set of propositions, or statement of facts.
In a nutshell, with a relational database, rows represent facts about real
world entities, column values represent their properties and relational
operations answer the questions about them.
Anith|||Thanks for the comments. Let me start by saying I agree with your statement
.
However, it is a business requirement that a customer can create custom
attributes. That is in essence what I need to figure out.
I certainly do not want to give the user the ability to add a column to a
table, so it seems as though I need to use this EAV design. I am troubled b
y
it since I seem to be losing the ability to put certain restraints on the
data. Do you know of another way to handle custom attributes?
Thanks again.
"Anith Sen" wrote:

> Without a comprehensive knowledge of your business model, others cannot
> possibly determine the characteristics of an entity of your enterprise.
> Rather than thinking about trees, cars and characteristics, think of trees
> and cars as a set of entities having their own attributes. In loose terms,
> each form an entity type & therefore belong to separate tables.
>
> Think of each table as a set of propositions, or statement of facts.
> In a nutshell, with a relational database, rows represent facts about real
> world entities, column values represent their properties and relational
> operations answer the questions about them.
> --
> Anith
>
>|||Pete,
On what basis does a customer create those attributes? How does one make
sure the attributes are appropriate to the entity that is being represented
in the table? How does one constrain the values to be drawn from a domain
specific to that attribute?
It sure sounds like a serious misunderstanding of business requirements.
There is no such thing as a custom attribute. Either an entity has an
attribute or it does not. It is up to the database designer to determine all
the applicable attributes of relevance pertaining to an entity in the
conceptual model, before starting logical design. In general, when one
chooses to ignore this exercise, often he is forced to compensate by forcing
the user to share the burden of creating tables, columns, constraints etc.
Generally, the database design flows from conceptual -> logical -> physical
levels.
-- Understand the business model thoroughly so that you will know the
entities, relationships among them and the attributes involved.
-- From this business model ( also known as conceptual schema ) applying
relational principles, map them relational tables. Use proven design
guidelines like normalization, predicate uniqueness principle etc. to
eliminate data redundancy. A good relational design allows the data to be
represented without application or task bias.
-- Based on the capabilities of the SQL DBMS, tune up the physical model so
the data can be efficiently manipulated and retrieved.
Many a time, designers introduce complexity at the logical level in an
attempt to compensate for an incomplete business model. Similarly complexity
is often introduced at the physical level when trying to compensate for a
mediocre logical model as well.
Anith|||Yes, it is the same. Initially, they called it New Design Priciple and later
renamed to the Principle of Orthogonal design.
Anith|||Thanks for the response. These are all questions I am grappling with. The
example that I started with illustrates this point well. I need to create
new trees, new cars and new charateristics that could be applied to either.
We are an ISV so customers are supposed to be able to create their own
characteristics (i.e. attributes). If you know of another way to handle thi
s
(without changing the table structure), please let me know. Any thoughts
would be great.
Thanks again.
"Anith Sen" wrote:

> Pete,
>
> On what basis does a customer create those attributes? How does one make
> sure the attributes are appropriate to the entity that is being represente
d
> in the table? How does one constrain the values to be drawn from a domain
> specific to that attribute?
> It sure sounds like a serious misunderstanding of business requirements.
>
> There is no such thing as a custom attribute. Either an entity has an
> attribute or it does not. It is up to the database designer to determine a
ll
> the applicable attributes of relevance pertaining to an entity in the
> conceptual model, before starting logical design. In general, when one
> chooses to ignore this exercise, often he is forced to compensate by forci
ng
> the user to share the burden of creating tables, columns, constraints etc.
> Generally, the database design flows from conceptual -> logical -> physica
l
> levels.
> -- Understand the business model thoroughly so that you will know the
> entities, relationships among them and the attributes involved.
> -- From this business model ( also known as conceptual schema ) applying
> relational principles, map them relational tables. Use proven design
> guidelines like normalization, predicate uniqueness principle etc. to
> eliminate data redundancy. A good relational design allows the data to be
> represented without application or task bias.
> -- Based on the capabilities of the SQL DBMS, tune up the physical model s
o
> the data can be efficiently manipulated and retrieved.
> Many a time, designers introduce complexity at the logical level in an
> attempt to compensate for an incomplete business model. Similarly complexi
ty
> is often introduced at the physical level when trying to compensate for a
> mediocre logical model as well.
> --
> Anith
>
>

constraint expression for unique keys

if i have a table which defines a rule as "combination of two field
must be unique", how can I write this in a constraint expression
section?
i started learning more about ms sql side to handle all the necessary
rules in back-end instead of front-end.
also any good learning links, references, or book recommandations?
thanksan excerpt from BOL:

"C. Using UNIQUE constraints
UNIQUE constraints are used to enforce uniqueness on nonprimary key
columns. The following example enforces a restriction that the Name
column of the Product table must be unique.

Copy Code
Name nvarchar(100) NOT NULL
UNIQUE NONCLUSTERED

"|||can you explain what "NONCLUSTERED" is doing there? is that for
non-relation to a field in other table?

Alexander Kuznetsov wrote:
> an excerpt from BOL:
> "C. Using UNIQUE constraints
> UNIQUE constraints are used to enforce uniqueness on nonprimary key
> columns. The following example enforces a restriction that the Name
> column of the Product table must be unique.
> Copy Code
> Name nvarchar(100) NOT NULL
> UNIQUE NONCLUSTERED
> "|||SQL Server implicitly creates an index to implement a uinque
constraint. In this case NONCLUSTERED means the index will be
non-clustered.|||HandersonVA (handersonva@.hotmail.com) writes:
> if i have a table which defines a rule as "combination of two field
> must be unique", how can I write this in a constraint expression
> section?

CONSTRAINT u_tbl UNIQUE (col1, col2)

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||>> if i have a table which defines a rule as "combination of two field must be unique", how can I write this in a constraint expression section? <<

CONSTRAINT unique_location UNIQUE (x, y)
>> any good learning links, references, or book recommandations? <<

I recommend buying all of my books :)