Contraints

What is the difference between the following two definitions
CREATE TABLE Test (
TEST_ID int IDENTITY(1,1) PRIMARY KEY CLUSTERED
)
and
CREATE TABLE Test (
TEST_ID int IDENTITY(1,1) CONSTRAINT PK_TEST_ID PRIMARY KEY CLUSTERED
)
Generally what is the difference between explicitelly define the contraints
with the CONTRAINT keyword or not.
Regards
DimitrisThe latter gives you the name you define (PK_TEST_ID).
The former lets the system define the constraint name for you, and it is
usually much less meaningful.
Many people swear by naming your constraints in order to have control
(admittedly, it does lend to better self-documentation of the schema).
Personally, I'm indifferent.
"Dimitris Milonas" <gnout@.hotmail.com> wrote in message
news:ehWAU6HSGHA.5500@.TK2MSFTNGP12.phx.gbl...
> What is the difference between the following two definitions
> CREATE TABLE Test (
> TEST_ID int IDENTITY(1,1) PRIMARY KEY CLUSTERED
> )
> and
> CREATE TABLE Test (
> TEST_ID int IDENTITY(1,1) CONSTRAINT PK_TEST_ID PRIMARY KEY CLUSTERED
> )
> Generally what is the difference between explicitelly define the
> contraints
> with the CONTRAINT keyword or not.
> Regards
> Dimitris
>

Contraints

Hi,
I have this (syntax not verified):
CREATE TABLE Colors (
Id INT PRIMARY KEY,
Color VARCHAR(20) NOT NULL
)
GO
INSERT INTO Colors VALUES (1, 'red')
GO
INSERT INTO Colors VALUES (2, 'green')
GO
INSERT INTO Colors VALUES (3, 'blue')
GO
CREATE TABLE Candy (
Id INT PRIMARY KEY,
Name VARCHAR(20) NOT NULL,
ColorId INT REFERENCES Colors(Id)
)
GO
INSERT INTO Candy VALUES (1, 'VanillaRocks', 1)
GO
INSERT INTO Candy VALUES (2, 'VanillaRocks', 3)
GO
INSERT INTO Candy VALUES (3, 'SweetMama', 2)
GO
CREATE TABLE CandyPacks (
PackId INT NOT NULL,
CandyId INT REFERENCES Candy(Id)
)
Now, you can put all kinds of candy in a pack:
INSERT INTO CandyPacks VALUES(1, 1)
GO
INSERT INTO CandyPacks VALUES(1, 1)
GO
INSERT INTO CandyPacks VALUES(1, 3)
GO
This puts 2 red colored VanillaRocks and 1 green colored SweetMama in bag 1.
Now, I want to make sure that no bag contains more than 2 candies of the
same kind and same color.
So the following should return ZERO records:
SELECT * FROM
CandyPacks P1, Candy C1, Colors CLR1,
CandyPacks P2, Candy C2, Colors CLR2
WHERE
C1.Id = P1.CandyId
AND CLR1.Id = C1.ColorId
AND C2.Id = P2.CandyId
AND CLR2.Id = C2.ColorId
AND CLR1.Id = CLR2.Id -- SHOULD NOT HAPPEN!
Now, perhaps this query sucks.. please correct, but my point should be
clear.
This is only an example for similar situations. Perhaps I could use primary
key constraints or something, but in other situations I have some 'complex
query' which should not yield results..
How can I turn a query like the above into a constraint on the CandyPacks
table?
LisaHi Lisa
Please consider using INSTEAD OF INSERT Trigger on the Table.
If the condition is satisified, insert the data
hope the problem is solved? If there are any more issues, do not hesitate to
revert back
thanks and regards
Chandra
"Lisa Pearlson" wrote:

> Hi,
> I have this (syntax not verified):
> CREATE TABLE Colors (
> Id INT PRIMARY KEY,
> Color VARCHAR(20) NOT NULL
> )
> GO
> INSERT INTO Colors VALUES (1, 'red')
> GO
> INSERT INTO Colors VALUES (2, 'green')
> GO
> INSERT INTO Colors VALUES (3, 'blue')
> GO
>
> CREATE TABLE Candy (
> Id INT PRIMARY KEY,
> Name VARCHAR(20) NOT NULL,
> ColorId INT REFERENCES Colors(Id)
> )
> GO
> INSERT INTO Candy VALUES (1, 'VanillaRocks', 1)
> GO
> INSERT INTO Candy VALUES (2, 'VanillaRocks', 3)
> GO
> INSERT INTO Candy VALUES (3, 'SweetMama', 2)
> GO
> CREATE TABLE CandyPacks (
> PackId INT NOT NULL,
> CandyId INT REFERENCES Candy(Id)
> )
> Now, you can put all kinds of candy in a pack:
> INSERT INTO CandyPacks VALUES(1, 1)
> GO
> INSERT INTO CandyPacks VALUES(1, 1)
> GO
> INSERT INTO CandyPacks VALUES(1, 3)
> GO
>
> This puts 2 red colored VanillaRocks and 1 green colored SweetMama in bag
1.
> Now, I want to make sure that no bag contains more than 2 candies of the
> same kind and same color.
> So the following should return ZERO records:
> SELECT * FROM
> CandyPacks P1, Candy C1, Colors CLR1,
> CandyPacks P2, Candy C2, Colors CLR2
> WHERE
> C1.Id = P1.CandyId
> AND CLR1.Id = C1.ColorId
> AND C2.Id = P2.CandyId
> AND CLR2.Id = C2.ColorId
> AND CLR1.Id = CLR2.Id -- SHOULD NOT HAPPEN!
> Now, perhaps this query sucks.. please correct, but my point should be
> clear.
> This is only an example for similar situations. Perhaps I could use primar
y
> key constraints or something, but in other situations I have some 'complex
> query' which should not yield results..
> How can I turn a query like the above into a constraint on the CandyPacks
> table?
> Lisa
>
>|||I would think that the best way to accomplish this would be using a trigger.
I would also consider changing the structure of the tables such that a
CandyPacks record contians the packid, a colorid, and a candyid. You may
find it easier to enforce the constraint with the data structured this way.
If you leave it as you have it you will have to write a more complex query i
n
the trigger.
"Chandra" wrote:
> Hi Lisa
> Please consider using INSTEAD OF INSERT Trigger on the Table.
> If the condition is satisified, insert the data
> hope the problem is solved? If there are any more issues, do not hesitate
to
> revert back
> thanks and regards
> Chandra
>
> "Lisa Pearlson" wrote:
>|||Try,
CREATE TABLE CandyPacks (
PackId INT NOT NULL,
CandyId INT not null REFERENCES Candy(Id),
quantity int not null default(1) check(quantity = 1 or quantity = 2),
constraint pk_CandyPacks primary key (PackId, CandyId)
)
AMB
"Lisa Pearlson" wrote:

> Hi,
> I have this (syntax not verified):
> CREATE TABLE Colors (
> Id INT PRIMARY KEY,
> Color VARCHAR(20) NOT NULL
> )
> GO
> INSERT INTO Colors VALUES (1, 'red')
> GO
> INSERT INTO Colors VALUES (2, 'green')
> GO
> INSERT INTO Colors VALUES (3, 'blue')
> GO
>
> CREATE TABLE Candy (
> Id INT PRIMARY KEY,
> Name VARCHAR(20) NOT NULL,
> ColorId INT REFERENCES Colors(Id)
> )
> GO
> INSERT INTO Candy VALUES (1, 'VanillaRocks', 1)
> GO
> INSERT INTO Candy VALUES (2, 'VanillaRocks', 3)
> GO
> INSERT INTO Candy VALUES (3, 'SweetMama', 2)
> GO
> CREATE TABLE CandyPacks (
> PackId INT NOT NULL,
> CandyId INT REFERENCES Candy(Id)
> )
> Now, you can put all kinds of candy in a pack:
> INSERT INTO CandyPacks VALUES(1, 1)
> GO
> INSERT INTO CandyPacks VALUES(1, 1)
> GO
> INSERT INTO CandyPacks VALUES(1, 3)
> GO
>
> This puts 2 red colored VanillaRocks and 1 green colored SweetMama in bag
1.
> Now, I want to make sure that no bag contains more than 2 candies of the
> same kind and same color.
> So the following should return ZERO records:
> SELECT * FROM
> CandyPacks P1, Candy C1, Colors CLR1,
> CandyPacks P2, Candy C2, Colors CLR2
> WHERE
> C1.Id = P1.CandyId
> AND CLR1.Id = C1.ColorId
> AND C2.Id = P2.CandyId
> AND CLR2.Id = C2.ColorId
> AND CLR1.Id = CLR2.Id -- SHOULD NOT HAPPEN!
> Now, perhaps this query sucks.. please correct, but my point should be
> clear.
> This is only an example for similar situations. Perhaps I could use primar
y
> key constraints or something, but in other situations I have some 'complex
> query' which should not yield results..
> How can I turn a query like the above into a constraint on the CandyPacks
> table?
> Lisa
>
>|||You could use a check constraint to see wheter how many candy are in the
package:
CHECK(Your Select statement)
Further help, just raise a hand.
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--
"Lisa Pearlson" <no@.spam.plz> schrieb im Newsbeitrag
news:%23aTIuu%23SFHA.2996@.TK2MSFTNGP15.phx.gbl...
> Hi,
> I have this (syntax not verified):
> CREATE TABLE Colors (
> Id INT PRIMARY KEY,
> Color VARCHAR(20) NOT NULL
> )
> GO
> INSERT INTO Colors VALUES (1, 'red')
> GO
> INSERT INTO Colors VALUES (2, 'green')
> GO
> INSERT INTO Colors VALUES (3, 'blue')
> GO
>
> CREATE TABLE Candy (
> Id INT PRIMARY KEY,
> Name VARCHAR(20) NOT NULL,
> ColorId INT REFERENCES Colors(Id)
> )
> GO
> INSERT INTO Candy VALUES (1, 'VanillaRocks', 1)
> GO
> INSERT INTO Candy VALUES (2, 'VanillaRocks', 3)
> GO
> INSERT INTO Candy VALUES (3, 'SweetMama', 2)
> GO
> CREATE TABLE CandyPacks (
> PackId INT NOT NULL,
> CandyId INT REFERENCES Candy(Id)
> )
> Now, you can put all kinds of candy in a pack:
> INSERT INTO CandyPacks VALUES(1, 1)
> GO
> INSERT INTO CandyPacks VALUES(1, 1)
> GO
> INSERT INTO CandyPacks VALUES(1, 3)
> GO
>
> This puts 2 red colored VanillaRocks and 1 green colored SweetMama in bag
> 1.
> Now, I want to make sure that no bag contains more than 2 candies of the
> same kind and same color.
> So the following should return ZERO records:
> SELECT * FROM
> CandyPacks P1, Candy C1, Colors CLR1,
> CandyPacks P2, Candy C2, Colors CLR2
> WHERE
> C1.Id = P1.CandyId
> AND CLR1.Id = C1.ColorId
> AND C2.Id = P2.CandyId
> AND CLR2.Id = C2.ColorId
> AND CLR1.Id = CLR2.Id -- SHOULD NOT HAPPEN!
> Now, perhaps this query sucks.. please correct, but my point should be
> clear.
> This is only an example for similar situations. Perhaps I could use
> primary key constraints or something, but in other situations I have some
> 'complex query' which should not yield results..
> How can I turn a query like the above into a constraint on the CandyPacks
> table?
> Lisa
>|||*raises hand*
My actual table is:
CREATE TABLE MatchResults
(
ParentId INT NOT NULL REFERENCES Bedrijven(Id),
DatMatch DATETIME NOT NULL,
Id INT NOT NULL REFERENCES Bedrijven(Id),
PRIMARY KEY(ParentId,DatMatch,Id),
Updated DATETIME DEFAULT GETDATE(),
Deleted BIT DEFAULT 0
)
And no data may be added to this table that would return any records in this
query:
SELECT COUNT(*)
FROM MatchResults M, Bedrijven B1, Bedrijven B2
WHERE M.Deleted!=1
AND B1.Id = M.ParentId
AND B2.Id = M.Id
AND (B1.ProfielId!=3 OR B2.ProfielId=3)
So the above query should always yield 0.
So I want to add a contraint to the above table so that the query below
always is 0.
I don't want to use INSTEAD OF INSERT trigger...
How do I turn it into a table constraint?
Thanks,
Lisa
"Jens Smeyer" <Jens@.Remove_this_For_Contacting.sqlserver2005.de> wrote in
message news:ePt3i5%23SFHA.3980@.TK2MSFTNGP12.phx.gbl...
> You could use a check constraint to see wheter how many candy are in the
> package:
> CHECK(Your Select statement)
> Further help, just raise a hand.
> HTH, Jens Suessmeyer.
> --
> http://www.sqlserver2005.de
> --
> "Lisa Pearlson" <no@.spam.plz> schrieb im Newsbeitrag
> news:%23aTIuu%23SFHA.2996@.TK2MSFTNGP15.phx.gbl...
>|||On Wed, 4 May 2005 02:50:10 +0200, Lisa Pearlson wrote:

>*raises hand*
>My actual table is:
>CREATE TABLE MatchResults
>(
>ParentId INT NOT NULL REFERENCES Bedrijven(Id),
>DatMatch DATETIME NOT NULL,
>Id INT NOT NULL REFERENCES Bedrijven(Id),
> PRIMARY KEY(ParentId,DatMatch,Id),
>Updated DATETIME DEFAULT GETDATE(),
>Deleted BIT DEFAULT 0
> )
>
>And no data may be added to this table that would return any records in thi
s
>query:
>SELECT COUNT(*)
>FROM MatchResults M, Bedrijven B1, Bedrijven B2
>WHERE M.Deleted!=1
>AND B1.Id = M.ParentId
>AND B2.Id = M.Id
>AND (B1.ProfielId!=3 OR B2.ProfielId=3)
>So the above query should always yield 0.
>So I want to add a contraint to the above table so that the query below
>always is 0.
>I don't want to use INSTEAD OF INSERT trigger...
>How do I turn it into a table constraint?
>Thanks,
>Lisa
Hi Lisa,
Impossible with your current design, since a CHECK constraint can't use
any other data than the data in the same row.
The workaround is to replace the Bedrijven table with two tables: one
for the bedrijven with profiel equal to 3 and one for the bedrijven with
profiel unequal to 3:
CREATE TABLE BedrijvenType3
(Id INT NOT NULL,
ProfielID INT NOT NULL,
... other columns,
Deleted BIT NOT NULL DEFAULT 0,
PRIMARY KEY (Id),
CHECK (ProfielId = 3)
)
CREATE TABLE BedrijvenOverig
(Id INT NOT NULL,
ProfielID INT NOT NULL,
... other columns,
Deleted BIT NOT NULL DEFAULT 0,
PRIMARY KEY (Id),
CHECK (ProfielId <> 3)
)
CREATE TABLE MatchResults
(ParentId INT NOT NULL REFERENCES BedrijvenType3(Id),
DatMatch DATETIME NOT NULL,
Id INT NOT NULL REFERENCES BedrijvenOverig(Id),
PRIMARY KEY (ParentId, DatMatch, Id),
Updated DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
Deleted BIT NOT NULL DEFAULT 0
)
Note that I also included a Deleted column in the two bedrijven tables,
because the referential integrity can't be made dependant of the Deleted
column in the MatchResults. If you do want to really remove rows from
the bedrijven tables (or if I somehow misunderstood your requirements),
you'll have to use a different technique: make a redundant copy of the
ProfielId for both Id and ParentId (and possibly add a redundant UNIQUE
constraint to the Bedrijven table, so that the data stays synch'ed):
ALTER TABLE Bedrijven
ADD UNIQUE (Id, ProfielId)
CREATE TABLE MatchResults
(ParentId INT NOT NULL,
ParentIdProfiel INT NOT NULL,
FOREIGN KEY (ParentId, ParentIdProfiel)
REFERENCES Bedrijven (Id, ProfielId),
DatMatch DATETIME NOT NULL,
Id INT NOT NULL,
IdProfiel INT NOT NULL,
FOREIGN KEY (Id, IdProfiel)
REFERENCES Bedrijven (Id, ProfielId),
PRIMARY KEY (ParentId, DatMatch, Id),
Updated DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
Deleted BIT NOT NULL DEFAULT 0,
CHECK (Deleted = 1 OR (ParentIdProfiel = 3 AND IdProfiel <> 3)
)
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Why do you believe that every table has magical, universal column
called "id"? In an RDBMS, there are lots of kinds of identifiers,
not like a 1950's file system record number. And have you ever
researched for industry standards, like the Land color number or
Pantone numbers? Let's clean up the sample DDL:
CREATE TABLE Colors
(pantone_nbr INTEGER PRIMARY KEY,
pantone_description VARCHAR(20) NOT NULL);
CREATE TABLE Candies
(upc CHAR(13) NOT NULL PRIMARY KEY,
candy_name VARCHAR(20) NOT NULL,
pantone_nbr INTEGER REFERENCES Colors(pantone_nbr));
CREATE TABLE CandyPacks
(pack_upc CHAR(13) NOT NULL,
candy_upc CHAR(13) NOT NULL
REFERENCES Candies(upc),
PRIMARY KEY (pack_upc, candy_upc)
);
same kind AND same color. <<
The first condition is enforced by a PRIMARY KEY. In full SQL-92 you
can put this into a CHECK();
CHECK(1 = ALL (SELECT COUNT(C1.pantone_nbr)
FROM Candies AS C1
WHERE C1.upc = CandyPacks.candyupc))
In SQL server, you will need to use a trigger on the packages table.

CONTAINS

Hi,
I have a table like this:
CREATE TABLE T1(
C1 int identity(1,1) PRIMARY KEY,
C2 NVARCHAR(50))
INSERT T1(C2) VALUES('test')
INSERT T1(C2) VALUES('Xtest')
INSERT T1(C2) VALUES('testX')
Assuming that C2 is enabled for FTS, this query does not return "Xtest":
SELECT * FROM T1 WHERE CONTAINS (*,'"*test*"')
It returns only "test" and "testX". How can I do that?
Thanks in advance,
Leila
"Leila" <Leilas@.hotpop.com> wrote in message
news:O7ujWH2iFHA.3300@.TK2MSFTNGP10.phx.gbl...
> Hi,
> I have a table like this:
> CREATE TABLE T1(
> C1 int identity(1,1) PRIMARY KEY,
> C2 NVARCHAR(50))
> INSERT T1(C2) VALUES('test')
> INSERT T1(C2) VALUES('Xtest')
> INSERT T1(C2) VALUES('testX')
> Assuming that C2 is enabled for FTS, this query does not return "Xtest":
> SELECT * FROM T1 WHERE CONTAINS (*,'"*test*"')
> It returns only "test" and "testX". How can I do that?
You can't, unless you use "Xtest" or "Xtest*". FTS doesn't handle suffix
searches, and so the leading * is ignored. Another option would be to fall
back to LIKE when a leading * is used.
Dan
|||I think you mean prefix (comes before) which SQL FTS does not support. SQL
FTS does support suffix (comes at the end) type searches when you use the
wildcard operator in the Contains predicate.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Daniel Crichton" <msnews@.worldofspack.co.uk> wrote in message
news:e1DACM3iFHA.3216@.TK2MSFTNGP10.phx.gbl...
> "Leila" <Leilas@.hotpop.com> wrote in message
> news:O7ujWH2iFHA.3300@.TK2MSFTNGP10.phx.gbl...
> You can't, unless you use "Xtest" or "Xtest*". FTS doesn't handle suffix
> searches, and so the leading * is ignored. Another option would be to fall
> back to LIKE when a leading * is used.
> Dan
>
|||"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:%23RPqSH4iFHA.3064@.TK2MSFTNGP15.phx.gbl...
>I think you mean prefix (comes before) which SQL FTS does not support. SQL
> FTS does support suffix (comes at the end) type searches when you use the
> wildcard operator in the Contains predicate.
Possibly, I can't remember which way around they go. If it refers to the
string part, it's suffix that isn't supported as the wildcard will be the
prefix. Thanks for pointing out my error though
Dan
|||Thank you all :-)
Then LIKE will be the only poosible way?
"Daniel Crichton" <msnews@.worldofspack.co.uk> wrote in message
news:ek0j$34iFHA.3216@.TK2MSFTNGP10.phx.gbl...[vbcol=seagreen]
> "Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
> news:%23RPqSH4iFHA.3064@.TK2MSFTNGP15.phx.gbl...
SQL[vbcol=seagreen]
the
> Possibly, I can't remember which way around they go. If it refers to the
> string part, it's suffix that isn't supported as the wildcard will be the
> prefix. Thanks for pointing out my error though
> Dan
>
|||Unfortunately so.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Leila" <Leilas@.hotpop.com> wrote in message
news:ehN0lR%23iFHA.576@.tk2msftngp13.phx.gbl...[vbcol=seagreen]
> Thank you all :-)
> Then LIKE will be the only poosible way?
>
> "Daniel Crichton" <msnews@.worldofspack.co.uk> wrote in message
> news:ek0j$34iFHA.3216@.TK2MSFTNGP10.phx.gbl...
> SQL
> the
the
>

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

Constraint Question

Is it possible to set an index of no duplicates on a column other than the primary key of a table? If yes, how is this done?

Yes, you can create unique index. Here is an example for creating one on a username column in Sql Server.

CREATE UNIQUE NONCLUSTERED INDEX [IX_USERNAME] ON [dbo].[USER]
(
[USERNAME] ASC
)

|||

U can use unique index. But u should not forget that unique index allows null values which we can treat them as dup values

Thank u

Baba

Please remember to click "Mark as Answer" on this post if it helped you.

constraint problem

Hi everybody

we have the following tables

1)

Country

Countryid CountyNAme
IN India
MY Malaysia
UK UnitedKingdom
here Countryid is the primary key.

2) MainDept

DeptID Deptname Countryid
CM CashManagement IN
CB ConsumerBanking MY
CS Customer Support IN
IB InternetBank IN
here deptid is the primary key

3) UserMaster

Uid Uname Deptid Countryid
001 Chris CM IN
002 Raja CS IN
003 Ram CB MY
here Uid is the primary key.

The problem is when i change the countryid from one country to another for a deptname.
THe change is not reflected in the usermaster table as it still shows the previous countryid.
For eg. user Chris belongs to dept Cash management which is situated in india.
Now if i change in mainDept table the cash management from india(IN) to say malaysia(MY).the corresponding change is not reflected in usermaster table.it still shows india. So when i query for chris in usermaster i get an error
as i am searching in india for cash mangement.
i tried using on update cascade but here it did not work as i have to make DeptID & countryID in MainDept table as composite key & use Deptid & countryid in usermaster as refernce key.
Since i have 20-25 tables also referencing the above 2 tables i have to set reference key in all these tables & these tables are in turn referenced elsewhere in other tables. Thus i end up creating a large no. of composite keys.
IS there any other way to solve this problem?
note : In sqlserver we can give on update cascade still it has the above problem
but in Oracle on update cascade is not possible

Can anybody suggest a solution for this in both sql server and in oracle

Thanks u verymuchhai saj,

How abt using a trigger in maindept table, which will update all the countryids in usermaster table for the corresponding deptid. ?

But in my opinion the countryid column in usermaster table looks like a redundant data. Since u have the deptid in usermaster table u can always get the countryid by the referencing the corresponding column in maindept (i am assuming that none of ur other tables r using ur usermaster table)

with regards
Sudar

Constraint or Index?

Suppose I have a table called "Languages" with two fields. One field is an Identity field that acts as the Primary Key. The second field is "LanguageName" which would naturally be "English", "Spanish", etc. What I want to do is put a constraint on the LanguageName field so that someone cannot enter the same name twice.

Is it better to create an Index --> Create UNIQUE and use:
a) Constraint?
b) Index with Ignore duplicate key checked?

Is there any benefit of one over the other for my purpose? Thanks.The Database Engine automatically creates a UNIQUE index to enforce the uniqueness requirement of the UNIQUE constraint. The underlying changes are actually the same :)|||Ah-ha. Thanks!|||I'd use a constraint because it better reflects what your intentions for that field are. An index will do the job of course but and index is more of an implementation thing than a data/business rule thing.|||One field is an Identity field that acts as the Primary Key. The second field is "LanguageName" which would naturally be "English", "Spanish", etc.

Isn't language name a lovely natural key?