Cleanup / Foreign key

I have an address table I set up so that I can start our address campaign.
There is a field (open entry) called Business Type that I allowed the works
to enter to describe the business type. Now that the entry is complete ,
I'm in cleanup phase. Now, my question is: I know I need to make Business
Type a foreign key and select distinct to insert them into their own table,
but should I do that before I clean up the inconsistencies or after. There
are about 2000 different business types, some are minor like - Car Sales -
Used and Car Sales _ Used. I'm creating a system that allows users to
search based on business type.
Before or after, why?Before!
Why? Do not allow bad data into the database in the first place, it is a
bitch getting it out.
Jay
<programmingcodeATjards.com> wrote in message
news:uVedQl9%23HHA.320@.TK2MSFTNGP04.phx.gbl...
>I have an address table I set up so that I can start our address campaign.
>There is a field (open entry) called Business Type that I allowed the works
>to enter to describe the business type. Now that the entry is complete ,
>I'm in cleanup phase. Now, my question is: I know I need to make Business
>Type a foreign key and select distinct to insert them into their own table,
>but should I do that before I clean up the inconsistencies or after. There
>are about 2000 different business types, some are minor like - Car Sales -
>Used and Car Sales _ Used. I'm creating a system that allows users to
>search based on business type.
> Before or after, why?
>sqlsql

cleaning wrong PK FK

Hello

my 2 tables in MS SQL 2000

Report :
Report_id (PK)
name

Product :
Product_id (PK)
Report_id (FK)
name

the Foreign Key and Primary Key have been added later (when the tables were allready full)
product has a few millions of lines and report a few 10.thousand

now I want to clean the 2 tables and remove all the lines which are not conected by PK > FK or FK > PK

i am trying :

DELETE FROM Product WHERE (Product.Report_id NOT IN (SELECT Report.Report_id FROM Report))

DELETE FROM Report WHERE (Report.Report_id NOT IN (SELECT Product.Report_id FROM Product))

but the database crash : time overflow !

how can I do it ?

thank youHere are two alternate methods:--Method #1: EXISTS
delete
from Product
where not exists (select * from Report where Report.Report_id = Product.Report_id)

--Method #2: LEFT OUTER JOIN
delete
from Product
left outer join Report on Product.Report_id = Report.Report_id
where Report.Report_id is null
In either method, make sure Report_id is indexed in both tables.|||thank you BlindMan

on the 2nd method i am getting :
Incorrect syntax near the keyword 'left'.|||Post your code.|||that one

delete
from Product
left outer join Report on Product.Report_id = Report.Report_id
where Report.Report_id is null

Incorrect syntax near the keyword 'left'

you said : in either method, make sure Report_id is indexed in both tables.

they are PK to FK but they are not indexed
how can I do it when the tables are allready full

ALTER TABLE create index ?

thank's a lot|||I'm assuming that you defined the PK and FK in your head, but haven't done anything with the database. A PK that doesn't exist using a PRIMARY KEY definition is only a good intention from my perspective. ;) I would suggest using something like:CREATE INDEX dropme01 ON Report (Report_Id)
CREATE INDEX dropme02 ON Product (Report_Id)

DELETE FROM Report
WHERE NOT EXISTS (SELECT *
FROM Product
WHERE Product.Report_Id = Report.Report_Id)

DELETE FROM Product
WHERE NOT EXISTS (SELECT *
FROM Report
WHERE Report.Report_id = Product.Report_Id)

DROP INDEX Report.dropme01
DROP INDEX Product.dropme02

ALTER TABLE Report
ADD CONSTRAINT XPKReport
PRIMARY KEY (Report_Id)

ALTER TABLE Product
ADD CONSTRAINT XPKProduct
PRIMARY KEY (Product_Id)

ALTER TABLE Product
ADD CONSTRAINT XFK01Report
FOREIGN KEY (Report_Id)
REFERENCES Report (Report_Id)-PatP|||Pat FK and PK are allready in the tables|||Pat FK and PK are allready in the tables
Are you having any problem now?|||with your first method it works very well
i was just wondering why it doesnt with the second method

but it works ...

thanks a lot|||Public kya Time pass karne aati hai kya idhar?|||Public kya Time pass karne aati hai kya idhar?

No Hindi man, I think English would be more appropiate to express anything that you post here.

He was telling ," Do the people come here only to pass time?"|||No Hindi man, I think English would be more appropiate to express anything that you post here.

He was telling ," Do the people come here only to pass time?"Lol - well I think we all know the answer to that.

Joydeep - you are becoming the SQL Server Forum Official Translator (Asian Languages Division) :)|||Lol - well I think we all know the answer to that.

Joydeep - you are becoming the SQL Server Forum Official Translator (Asian Languages Division) :)
........;)|||Sorry. There was a syntax error in my second example. This should work:
delete Product
from Product
left outer join Report on Product.Report_id = Report.Report_id
where Report.Report_id is null|||i try it thank you

Circular FK Constraints

Hi.
My system includes Clients and Contacts in a many-to-many relationship
handled in the usual way using a link table with a primary key of Client key
plus Contact key and foreign key constraints against Client and Contact
tables. Each Client may have a Main Contact, which is handled at present wit
h
a 'Main Contact' column in the Client table, which for referential integrity
has a foreign key constraint against the link table. These circular FK
constraints are a nuisance when it comes to deleting a client. I suppose I
could use a trigger to enforce referential integrity, but I don't want to -
the Client table already has a rather complicated trigger.
Can anyone think of a better way?
Thanks.
--
Peter HyssettPeter Hyssett wrote:
> Hi.
> My system includes Clients and Contacts in a many-to-many relationship
> handled in the usual way using a link table with a primary key of Client k
ey
> plus Contact key and foreign key constraints against Client and Contact
> tables. Each Client may have a Main Contact, which is handled at present w
ith
> a 'Main Contact' column in the Client table, which for referential integri
ty
> has a foreign key constraint against the link table. These circular FK
> constraints are a nuisance when it comes to deleting a client. I suppose I
> could use a trigger to enforce referential integrity, but I don't want to
-
> the Client table already has a rather complicated trigger.
> Can anyone think of a better way?
> Thanks.
Well, in my mind, your main contact fk would be better off referencing
the contact table instead of the link table.
When deleting a contact, you would obviously need to either set the
client.main contact column to a valid id from the contact table first,
or set it to null whichever is appropriate.
Then you should be fine.
JB|||Thanks. I'm afraid the FK is against the link table because the main contact
must be a contact already linked to the client, which an FK against the
Contact table would not enforce. Nowadays I do set the Main Contact column t
o
NULL before deleting (the link table rows being deleted first), but I would
prefer not to.
--
Peter Hyssett
"John B" wrote:

> Peter Hyssett wrote:
> Well, in my mind, your main contact fk would be better off referencing
> the contact table instead of the link table.
> When deleting a contact, you would obviously need to either set the
> client.main contact column to a valid id from the contact table first,
> or set it to null whichever is appropriate.
> Then you should be fine.
> JB
>|||Hello, Peter
As I understand this, your DDL is (or should be) something like this:
CREATE TABLE Contacts (
ContactID int PRIMARY KEY,
FirstName varchar(30) NOT NULL,
LastName varchar(20) NOT NULL,
--other columns...
UNIQUE (FirstName, LastName)
)
CREATE TABLE Clients (
ClientID int PRIMARY KEY,
ClientName varchar(50) NOT NULL UNIQUE,
--other columns...
MainContactID int NULL
)
CREATE TABLE ClientContacts (
ClientID int REFERENCES Clients ON DELETE CASCADE,
ContactID int REFERENCES Contacts ON DELETE CASCADE,
PRIMARY KEY (ClientID, ContactID)
)
ALTER TABLE Clients ADD CONSTRAINT TheFK
FOREIGN KEY (ClientID, MainContactID)
REFERENCES ClientContacts (ClientID, ContactID)
Assuming the following sample data:
SET NOCOUNT ON
INSERT INTO Clients (ClientID, ClientName)
VALUES (100, 'Big Company, Inc.')
INSERT INTO Contacts VALUES (1, 'John', 'Smith')
INSERT INTO Contacts VALUES (2, 'Mary', 'Smith')
INSERT INTO ClientContacts VALUES (100, 1)
INSERT INTO ClientContacts VALUES (100, 2)
UPDATE Clients SET MainContactID=1 WHERE ClientID=100
INSERT INTO Clients (ClientID, ClientName)
VALUES (200, 'Another Company, Ltd.')
INSERT INTO Contacts VALUES (3, 'John', 'Doe')
INSERT INTO Contacts VALUES (4, 'Jane', 'Doe')
INSERT INTO ClientContacts VALUES (200, 2)
INSERT INTO ClientContacts VALUES (200, 3)
INSERT INTO ClientContacts VALUES (200, 4)
UPDATE Clients SET MainContactID=3 WHERE ClientID=200
SET NOCOUNT OFF
Let's suppose you want to delete the client with the ClientID=100.
Using a simple "DELETE Clients WHERE ClientID=100" works (without any
error): the client is deleted and it's links with the contacts, too
(but the contacts themselves remain). If you want to also delete the
contacts (the ones that are not linked with any other client), you can
use the following trigger:
CREATE TRIGGER Clients_DeleteContacts ON Clients
INSTEAD OF DELETE
AS
IF @.@.ROWCOUNT>0 BEGIN
SET NOCOUNT ON
UPDATE Clients SET MainContactID=NULL
WHERE ClientID IN (SELECT ClientID FROM deleted)
DELETE Contacts WHERE ContactID IN (
SELECT x.ContactID FROM ClientContacts x
WHERE x.ClientID IN (SELECT ClientID FROM deleted)
AND NOT EXISTS (
SELECT * FROM ClientContacts y
WHERE x.ContactID=y.ContactID
AND y.ClientID NOT IN (SELECT ClientID FROM deleted)
)
)
DELETE Clients
WHERE ClientID IN (SELECT ClientID FROM deleted)
END
Razvan|||On Thu, 16 Jun 2005 16:19:05 -0700, Peter Hyssett wrote:

>Hi.
>My system includes Clients and Contacts in a many-to-many relationship
>handled in the usual way using a link table with a primary key of Client ke
y
>plus Contact key and foreign key constraints against Client and Contact
>tables. Each Client may have a Main Contact, which is handled at present wi
th
>a 'Main Contact' column in the Client table, which for referential integrit
y
>has a foreign key constraint against the link table. These circular FK
>constraints are a nuisance when it comes to deleting a client. I suppose I
>could use a trigger to enforce referential integrity, but I don't want to -
>the Client table already has a rather complicated trigger.
>Can anyone think of a better way?
>Thanks.
Hi Peter,
I'd consider something like this (stealing lots from Razvan Socol's
post)
CREATE TABLE Contacts (
ContactID int NOT NULL PRIMARY KEY,
FirstName varchar(30) NOT NULL,
LastName varchar(20) NOT NULL,
--other columns--
UNIQUE (FirstName, LastName)
)
CREATE TABLE Clients (
ClientID int NOT NULL PRIMARY KEY,
ClientName varchar(50) NOT NULL UNIQUE,
--other columns--
)
CREATE TABLE ClientContacts (
ClientID int NOT NULL REFERENCES Clients ON DELETE CASCADE,
ContactID int NOT NULL REFERENCES Contacts ON DELETE CASCADE,
Priority smallint NOT NULL CHECK Priority > 0,
PRIMARY KEY (ClientID, ContactID),
UNIQUE (ClientID, Priority)
)
CREATE VIEW PrimaryContact
AS
SELECT cc.ClientID, cc.ContactID
FROM (SELECT ClientID, MIN(Priority) AS MinPrio
FROM ClientContacts
GROUP BY ClientID) AS d
INNER JOIN ClientContacts AS cc
ON cc.ClientID = d.ClientID
AND cc.Priority = d.MinPrio
go
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Peter Hyssett wrote:
> Thanks. I'm afraid the FK is against the link table because the main conta
ct
> must be a contact already linked to the client,
Ah, that makes sense
> which an FK against the
> Contact table would not enforce. Nowadays I do set the Main Contact column
to
> NULL before deleting (the link table rows being deleted first), but I woul
d
> prefer not to.
Why would you prefer not to?
Cascade delete _might_ set it to null but Im not sure.
If you wished to keep the MainContactId, then it would be a referential
break as the actual contact would have been deleted.
You could do this by removing the FK constraint though.
JB|||Thanks, Razvan. The ON DELETE CASCADE construct is what I wanted - I just
hadn't come across it before.
Cheers,
Peter.
Peter Hyssett
"Razvan Socol" wrote:

> Hello, Peter
> As I understand this, your DDL is (or should be) something like this:
> CREATE TABLE Contacts (
> ContactID int PRIMARY KEY,
> FirstName varchar(30) NOT NULL,
> LastName varchar(20) NOT NULL,
> --other columns...
> UNIQUE (FirstName, LastName)
> )
> CREATE TABLE Clients (
> ClientID int PRIMARY KEY,
> ClientName varchar(50) NOT NULL UNIQUE,
> --other columns...
> MainContactID int NULL
> )
> CREATE TABLE ClientContacts (
> ClientID int REFERENCES Clients ON DELETE CASCADE,
> ContactID int REFERENCES Contacts ON DELETE CASCADE,
> PRIMARY KEY (ClientID, ContactID)
> )
> ALTER TABLE Clients ADD CONSTRAINT TheFK
> FOREIGN KEY (ClientID, MainContactID)
> REFERENCES ClientContacts (ClientID, ContactID)
>
> Assuming the following sample data:
> SET NOCOUNT ON
> INSERT INTO Clients (ClientID, ClientName)
> VALUES (100, 'Big Company, Inc.')
> INSERT INTO Contacts VALUES (1, 'John', 'Smith')
> INSERT INTO Contacts VALUES (2, 'Mary', 'Smith')
> INSERT INTO ClientContacts VALUES (100, 1)
> INSERT INTO ClientContacts VALUES (100, 2)
> UPDATE Clients SET MainContactID=1 WHERE ClientID=100
>
> INSERT INTO Clients (ClientID, ClientName)
> VALUES (200, 'Another Company, Ltd.')
> INSERT INTO Contacts VALUES (3, 'John', 'Doe')
> INSERT INTO Contacts VALUES (4, 'Jane', 'Doe')
> INSERT INTO ClientContacts VALUES (200, 2)
> INSERT INTO ClientContacts VALUES (200, 3)
> INSERT INTO ClientContacts VALUES (200, 4)
> UPDATE Clients SET MainContactID=3 WHERE ClientID=200
> SET NOCOUNT OFF
>
> Let's suppose you want to delete the client with the ClientID=100.
> Using a simple "DELETE Clients WHERE ClientID=100" works (without any
> error): the client is deleted and it's links with the contacts, too
> (but the contacts themselves remain). If you want to also delete the
> contacts (the ones that are not linked with any other client), you can
> use the following trigger:
> CREATE TRIGGER Clients_DeleteContacts ON Clients
> INSTEAD OF DELETE
> AS
> IF @.@.ROWCOUNT>0 BEGIN
> SET NOCOUNT ON
> UPDATE Clients SET MainContactID=NULL
> WHERE ClientID IN (SELECT ClientID FROM deleted)
> DELETE Contacts WHERE ContactID IN (
> SELECT x.ContactID FROM ClientContacts x
> WHERE x.ClientID IN (SELECT ClientID FROM deleted)
> AND NOT EXISTS (
> SELECT * FROM ClientContacts y
> WHERE x.ContactID=y.ContactID
> AND y.ClientID NOT IN (SELECT ClientID FROM deleted)
> )
> )
> DELETE Clients
> WHERE ClientID IN (SELECT ClientID FROM deleted)
> END
> Razvan
>

Circular FK Constraints

Hi.
My system includes Clients and Contacts in a many-to-many relationship
handled in the usual way using a link table with a primary key of Client key
plus Contact key and foreign key constraints against Client and Contact
tables. Each Client may have a Main Contact, which is handled at present with
a 'Main Contact' column in the Client table, which for referential integrity
has a foreign key constraint against the link table. These circular FK
constraints are a nuisance when it comes to deleting a client. I suppose I
could use a trigger to enforce referential integrity, but I don't want to -
the Client table already has a rather complicated trigger.
Can anyone think of a better way?
Thanks.
--
Peter HyssettPeter Hyssett wrote:
> Hi.
> My system includes Clients and Contacts in a many-to-many relationship
> handled in the usual way using a link table with a primary key of Client key
> plus Contact key and foreign key constraints against Client and Contact
> tables. Each Client may have a Main Contact, which is handled at present with
> a 'Main Contact' column in the Client table, which for referential integrity
> has a foreign key constraint against the link table. These circular FK
> constraints are a nuisance when it comes to deleting a client. I suppose I
> could use a trigger to enforce referential integrity, but I don't want to -
> the Client table already has a rather complicated trigger.
> Can anyone think of a better way?
> Thanks.
Well, in my mind, your main contact fk would be better off referencing
the contact table instead of the link table.
When deleting a contact, you would obviously need to either set the
client.main contact column to a valid id from the contact table first,
or set it to null whichever is appropriate.
Then you should be fine.
JB|||Thanks. I'm afraid the FK is against the link table because the main contact
must be a contact already linked to the client, which an FK against the
Contact table would not enforce. Nowadays I do set the Main Contact column to
NULL before deleting (the link table rows being deleted first), but I would
prefer not to.
--
Peter Hyssett
"John B" wrote:
> Peter Hyssett wrote:
> > Hi.
> > My system includes Clients and Contacts in a many-to-many relationship
> > handled in the usual way using a link table with a primary key of Client key
> > plus Contact key and foreign key constraints against Client and Contact
> > tables. Each Client may have a Main Contact, which is handled at present with
> > a 'Main Contact' column in the Client table, which for referential integrity
> > has a foreign key constraint against the link table. These circular FK
> > constraints are a nuisance when it comes to deleting a client. I suppose I
> > could use a trigger to enforce referential integrity, but I don't want to -
> > the Client table already has a rather complicated trigger.
> >
> > Can anyone think of a better way?
> >
> > Thanks.
> Well, in my mind, your main contact fk would be better off referencing
> the contact table instead of the link table.
> When deleting a contact, you would obviously need to either set the
> client.main contact column to a valid id from the contact table first,
> or set it to null whichever is appropriate.
> Then you should be fine.
> JB
>|||Hello, Peter
As I understand this, your DDL is (or should be) something like this:
CREATE TABLE Contacts (
ContactID int PRIMARY KEY,
FirstName varchar(30) NOT NULL,
LastName varchar(20) NOT NULL,
--other columns...
UNIQUE (FirstName, LastName)
)
CREATE TABLE Clients (
ClientID int PRIMARY KEY,
ClientName varchar(50) NOT NULL UNIQUE,
--other columns...
MainContactID int NULL
)
CREATE TABLE ClientContacts (
ClientID int REFERENCES Clients ON DELETE CASCADE,
ContactID int REFERENCES Contacts ON DELETE CASCADE,
PRIMARY KEY (ClientID, ContactID)
)
ALTER TABLE Clients ADD CONSTRAINT TheFK
FOREIGN KEY (ClientID, MainContactID)
REFERENCES ClientContacts (ClientID, ContactID)
Assuming the following sample data:
SET NOCOUNT ON
INSERT INTO Clients (ClientID, ClientName)
VALUES (100, 'Big Company, Inc.')
INSERT INTO Contacts VALUES (1, 'John', 'Smith')
INSERT INTO Contacts VALUES (2, 'Mary', 'Smith')
INSERT INTO ClientContacts VALUES (100, 1)
INSERT INTO ClientContacts VALUES (100, 2)
UPDATE Clients SET MainContactID=1 WHERE ClientID=100
INSERT INTO Clients (ClientID, ClientName)
VALUES (200, 'Another Company, Ltd.')
INSERT INTO Contacts VALUES (3, 'John', 'Doe')
INSERT INTO Contacts VALUES (4, 'Jane', 'Doe')
INSERT INTO ClientContacts VALUES (200, 2)
INSERT INTO ClientContacts VALUES (200, 3)
INSERT INTO ClientContacts VALUES (200, 4)
UPDATE Clients SET MainContactID=3 WHERE ClientID=200
SET NOCOUNT OFF
Let's suppose you want to delete the client with the ClientID=100.
Using a simple "DELETE Clients WHERE ClientID=100" works (without any
error): the client is deleted and it's links with the contacts, too
(but the contacts themselves remain). If you want to also delete the
contacts (the ones that are not linked with any other client), you can
use the following trigger:
CREATE TRIGGER Clients_DeleteContacts ON Clients
INSTEAD OF DELETE
AS
IF @.@.ROWCOUNT>0 BEGIN
SET NOCOUNT ON
UPDATE Clients SET MainContactID=NULL
WHERE ClientID IN (SELECT ClientID FROM deleted)
DELETE Contacts WHERE ContactID IN (
SELECT x.ContactID FROM ClientContacts x
WHERE x.ClientID IN (SELECT ClientID FROM deleted)
AND NOT EXISTS (
SELECT * FROM ClientContacts y
WHERE x.ContactID=y.ContactID
AND y.ClientID NOT IN (SELECT ClientID FROM deleted)
)
)
DELETE Clients
WHERE ClientID IN (SELECT ClientID FROM deleted)
END
Razvan|||On Thu, 16 Jun 2005 16:19:05 -0700, Peter Hyssett wrote:
>Hi.
>My system includes Clients and Contacts in a many-to-many relationship
>handled in the usual way using a link table with a primary key of Client key
>plus Contact key and foreign key constraints against Client and Contact
>tables. Each Client may have a Main Contact, which is handled at present with
>a 'Main Contact' column in the Client table, which for referential integrity
>has a foreign key constraint against the link table. These circular FK
>constraints are a nuisance when it comes to deleting a client. I suppose I
>could use a trigger to enforce referential integrity, but I don't want to -
>the Client table already has a rather complicated trigger.
>Can anyone think of a better way?
>Thanks.
Hi Peter,
I'd consider something like this (stealing lots from Razvan Socol's
post)
CREATE TABLE Contacts (
ContactID int NOT NULL PRIMARY KEY,
FirstName varchar(30) NOT NULL,
LastName varchar(20) NOT NULL,
--other columns--
UNIQUE (FirstName, LastName)
)
CREATE TABLE Clients (
ClientID int NOT NULL PRIMARY KEY,
ClientName varchar(50) NOT NULL UNIQUE,
--other columns--
)
CREATE TABLE ClientContacts (
ClientID int NOT NULL REFERENCES Clients ON DELETE CASCADE,
ContactID int NOT NULL REFERENCES Contacts ON DELETE CASCADE,
Priority smallint NOT NULL CHECK Priority > 0,
PRIMARY KEY (ClientID, ContactID),
UNIQUE (ClientID, Priority)
)
CREATE VIEW PrimaryContact
AS
SELECT cc.ClientID, cc.ContactID
FROM (SELECT ClientID, MIN(Priority) AS MinPrio
FROM ClientContacts
GROUP BY ClientID) AS d
INNER JOIN ClientContacts AS cc
ON cc.ClientID = d.ClientID
AND cc.Priority = d.MinPrio
go
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Peter Hyssett wrote:
> Thanks. I'm afraid the FK is against the link table because the main contact
> must be a contact already linked to the client,
Ah, that makes sense
> which an FK against the
> Contact table would not enforce. Nowadays I do set the Main Contact column to
> NULL before deleting (the link table rows being deleted first), but I would
> prefer not to.
Why would you prefer not to?
Cascade delete _might_ set it to null but Im not sure.
If you wished to keep the MainContactId, then it would be a referential
break as the actual contact would have been deleted.
You could do this by removing the FK constraint though.
JB|||Thanks, Razvan. The ON DELETE CASCADE construct is what I wanted - I just
hadn't come across it before.
Cheers,
Peter.
--
Peter Hyssett
"Razvan Socol" wrote:
> Hello, Peter
> As I understand this, your DDL is (or should be) something like this:
> CREATE TABLE Contacts (
> ContactID int PRIMARY KEY,
> FirstName varchar(30) NOT NULL,
> LastName varchar(20) NOT NULL,
> --other columns...
> UNIQUE (FirstName, LastName)
> )
> CREATE TABLE Clients (
> ClientID int PRIMARY KEY,
> ClientName varchar(50) NOT NULL UNIQUE,
> --other columns...
> MainContactID int NULL
> )
> CREATE TABLE ClientContacts (
> ClientID int REFERENCES Clients ON DELETE CASCADE,
> ContactID int REFERENCES Contacts ON DELETE CASCADE,
> PRIMARY KEY (ClientID, ContactID)
> )
> ALTER TABLE Clients ADD CONSTRAINT TheFK
> FOREIGN KEY (ClientID, MainContactID)
> REFERENCES ClientContacts (ClientID, ContactID)
>
> Assuming the following sample data:
> SET NOCOUNT ON
> INSERT INTO Clients (ClientID, ClientName)
> VALUES (100, 'Big Company, Inc.')
> INSERT INTO Contacts VALUES (1, 'John', 'Smith')
> INSERT INTO Contacts VALUES (2, 'Mary', 'Smith')
> INSERT INTO ClientContacts VALUES (100, 1)
> INSERT INTO ClientContacts VALUES (100, 2)
> UPDATE Clients SET MainContactID=1 WHERE ClientID=100
>
> INSERT INTO Clients (ClientID, ClientName)
> VALUES (200, 'Another Company, Ltd.')
> INSERT INTO Contacts VALUES (3, 'John', 'Doe')
> INSERT INTO Contacts VALUES (4, 'Jane', 'Doe')
> INSERT INTO ClientContacts VALUES (200, 2)
> INSERT INTO ClientContacts VALUES (200, 3)
> INSERT INTO ClientContacts VALUES (200, 4)
> UPDATE Clients SET MainContactID=3 WHERE ClientID=200
> SET NOCOUNT OFF
>
> Let's suppose you want to delete the client with the ClientID=100.
> Using a simple "DELETE Clients WHERE ClientID=100" works (without any
> error): the client is deleted and it's links with the contacts, too
> (but the contacts themselves remain). If you want to also delete the
> contacts (the ones that are not linked with any other client), you can
> use the following trigger:
> CREATE TRIGGER Clients_DeleteContacts ON Clients
> INSTEAD OF DELETE
> AS
> IF @.@.ROWCOUNT>0 BEGIN
> SET NOCOUNT ON
> UPDATE Clients SET MainContactID=NULL
> WHERE ClientID IN (SELECT ClientID FROM deleted)
> DELETE Contacts WHERE ContactID IN (
> SELECT x.ContactID FROM ClientContacts x
> WHERE x.ClientID IN (SELECT ClientID FROM deleted)
> AND NOT EXISTS (
> SELECT * FROM ClientContacts y
> WHERE x.ContactID=y.ContactID
> AND y.ClientID NOT IN (SELECT ClientID FROM deleted)
> )
> )
> DELETE Clients
> WHERE ClientID IN (SELECT ClientID FROM deleted)
> END
> Razvan
>

Circular FK Constraints

Hi.
My system includes Clients and Contacts in a many-to-many relationship
handled in the usual way using a link table with a primary key of Client key
plus Contact key and foreign key constraints against Client and Contact
tables. Each Client may have a Main Contact, which is handled at present with
a 'Main Contact' column in the Client table, which for referential integrity
has a foreign key constraint against the link table. These circular FK
constraints are a nuisance when it comes to deleting a client. I suppose I
could use a trigger to enforce referential integrity, but I don't want to -
the Client table already has a rather complicated trigger.
Can anyone think of a better way?
Thanks.
Peter Hyssett
Peter Hyssett wrote:
> Hi.
> My system includes Clients and Contacts in a many-to-many relationship
> handled in the usual way using a link table with a primary key of Client key
> plus Contact key and foreign key constraints against Client and Contact
> tables. Each Client may have a Main Contact, which is handled at present with
> a 'Main Contact' column in the Client table, which for referential integrity
> has a foreign key constraint against the link table. These circular FK
> constraints are a nuisance when it comes to deleting a client. I suppose I
> could use a trigger to enforce referential integrity, but I don't want to -
> the Client table already has a rather complicated trigger.
> Can anyone think of a better way?
> Thanks.
Well, in my mind, your main contact fk would be better off referencing
the contact table instead of the link table.
When deleting a contact, you would obviously need to either set the
client.main contact column to a valid id from the contact table first,
or set it to null whichever is appropriate.
Then you should be fine.
JB
|||Thanks. I'm afraid the FK is against the link table because the main contact
must be a contact already linked to the client, which an FK against the
Contact table would not enforce. Nowadays I do set the Main Contact column to
NULL before deleting (the link table rows being deleted first), but I would
prefer not to.
Peter Hyssett
"John B" wrote:

> Peter Hyssett wrote:
> Well, in my mind, your main contact fk would be better off referencing
> the contact table instead of the link table.
> When deleting a contact, you would obviously need to either set the
> client.main contact column to a valid id from the contact table first,
> or set it to null whichever is appropriate.
> Then you should be fine.
> JB
>
|||Hello, Peter
As I understand this, your DDL is (or should be) something like this:
CREATE TABLE Contacts (
ContactID int PRIMARY KEY,
FirstName varchar(30) NOT NULL,
LastName varchar(20) NOT NULL,
--other columns...
UNIQUE (FirstName, LastName)
)
CREATE TABLE Clients (
ClientID int PRIMARY KEY,
ClientName varchar(50) NOT NULL UNIQUE,
--other columns...
MainContactID int NULL
)
CREATE TABLE ClientContacts (
ClientID int REFERENCES Clients ON DELETE CASCADE,
ContactID int REFERENCES Contacts ON DELETE CASCADE,
PRIMARY KEY (ClientID, ContactID)
)
ALTER TABLE Clients ADD CONSTRAINT TheFK
FOREIGN KEY (ClientID, MainContactID)
REFERENCES ClientContacts (ClientID, ContactID)
Assuming the following sample data:
SET NOCOUNT ON
INSERT INTO Clients (ClientID, ClientName)
VALUES (100, 'Big Company, Inc.')
INSERT INTO Contacts VALUES (1, 'John', 'Smith')
INSERT INTO Contacts VALUES (2, 'Mary', 'Smith')
INSERT INTO ClientContacts VALUES (100, 1)
INSERT INTO ClientContacts VALUES (100, 2)
UPDATE Clients SET MainContactID=1 WHERE ClientID=100
INSERT INTO Clients (ClientID, ClientName)
VALUES (200, 'Another Company, Ltd.')
INSERT INTO Contacts VALUES (3, 'John', 'Doe')
INSERT INTO Contacts VALUES (4, 'Jane', 'Doe')
INSERT INTO ClientContacts VALUES (200, 2)
INSERT INTO ClientContacts VALUES (200, 3)
INSERT INTO ClientContacts VALUES (200, 4)
UPDATE Clients SET MainContactID=3 WHERE ClientID=200
SET NOCOUNT OFF
Let's suppose you want to delete the client with the ClientID=100.
Using a simple "DELETE Clients WHERE ClientID=100" works (without any
error): the client is deleted and it's links with the contacts, too
(but the contacts themselves remain). If you want to also delete the
contacts (the ones that are not linked with any other client), you can
use the following trigger:
CREATE TRIGGER Clients_DeleteContacts ON Clients
INSTEAD OF DELETE
AS
IF @.@.ROWCOUNT>0 BEGIN
SET NOCOUNT ON
UPDATE Clients SET MainContactID=NULL
WHERE ClientID IN (SELECT ClientID FROM deleted)
DELETE Contacts WHERE ContactID IN (
SELECT x.ContactID FROM ClientContacts x
WHERE x.ClientID IN (SELECT ClientID FROM deleted)
AND NOT EXISTS (
SELECT * FROM ClientContacts y
WHERE x.ContactID=y.ContactID
AND y.ClientID NOT IN (SELECT ClientID FROM deleted)
)
)
DELETE Clients
WHERE ClientID IN (SELECT ClientID FROM deleted)
END
Razvan
|||On Thu, 16 Jun 2005 16:19:05 -0700, Peter Hyssett wrote:

>Hi.
>My system includes Clients and Contacts in a many-to-many relationship
>handled in the usual way using a link table with a primary key of Client key
>plus Contact key and foreign key constraints against Client and Contact
>tables. Each Client may have a Main Contact, which is handled at present with
>a 'Main Contact' column in the Client table, which for referential integrity
>has a foreign key constraint against the link table. These circular FK
>constraints are a nuisance when it comes to deleting a client. I suppose I
>could use a trigger to enforce referential integrity, but I don't want to -
>the Client table already has a rather complicated trigger.
>Can anyone think of a better way?
>Thanks.
Hi Peter,
I'd consider something like this (stealing lots from Razvan Socol's
post)
CREATE TABLE Contacts (
ContactID int NOT NULL PRIMARY KEY,
FirstName varchar(30) NOT NULL,
LastName varchar(20) NOT NULL,
--other columns--
UNIQUE (FirstName, LastName)
)
CREATE TABLE Clients (
ClientID int NOT NULL PRIMARY KEY,
ClientName varchar(50) NOT NULL UNIQUE,
--other columns--
)
CREATE TABLE ClientContacts (
ClientID int NOT NULL REFERENCES Clients ON DELETE CASCADE,
ContactID int NOT NULL REFERENCES Contacts ON DELETE CASCADE,
Priority smallint NOT NULL CHECK Priority > 0,
PRIMARY KEY (ClientID, ContactID),
UNIQUE (ClientID, Priority)
)
CREATE VIEW PrimaryContact
AS
SELECT cc.ClientID, cc.ContactID
FROM (SELECT ClientID, MIN(Priority) AS MinPrio
FROM ClientContacts
GROUP BY ClientID) AS d
INNER JOIN ClientContacts AS cc
ON cc.ClientID = d.ClientID
AND cc.Priority = d.MinPrio
go
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)
|||Peter Hyssett wrote:
> Thanks. I'm afraid the FK is against the link table because the main contact
> must be a contact already linked to the client,
Ah, that makes sense
> which an FK against the
> Contact table would not enforce. Nowadays I do set the Main Contact column to
> NULL before deleting (the link table rows being deleted first), but I would
> prefer not to.
Why would you prefer not to?
Cascade delete _might_ set it to null but Im not sure.
If you wished to keep the MainContactId, then it would be a referential
break as the actual contact would have been deleted.
You could do this by removing the FK constraint though.
JB
|||Thanks, Razvan. The ON DELETE CASCADE construct is what I wanted - I just
hadn't come across it before.
Cheers,
Peter.
Peter Hyssett
"Razvan Socol" wrote:

> Hello, Peter
> As I understand this, your DDL is (or should be) something like this:
> CREATE TABLE Contacts (
> ContactID int PRIMARY KEY,
> FirstName varchar(30) NOT NULL,
> LastName varchar(20) NOT NULL,
> --other columns...
> UNIQUE (FirstName, LastName)
> )
> CREATE TABLE Clients (
> ClientID int PRIMARY KEY,
> ClientName varchar(50) NOT NULL UNIQUE,
> --other columns...
> MainContactID int NULL
> )
> CREATE TABLE ClientContacts (
> ClientID int REFERENCES Clients ON DELETE CASCADE,
> ContactID int REFERENCES Contacts ON DELETE CASCADE,
> PRIMARY KEY (ClientID, ContactID)
> )
> ALTER TABLE Clients ADD CONSTRAINT TheFK
> FOREIGN KEY (ClientID, MainContactID)
> REFERENCES ClientContacts (ClientID, ContactID)
>
> Assuming the following sample data:
> SET NOCOUNT ON
> INSERT INTO Clients (ClientID, ClientName)
> VALUES (100, 'Big Company, Inc.')
> INSERT INTO Contacts VALUES (1, 'John', 'Smith')
> INSERT INTO Contacts VALUES (2, 'Mary', 'Smith')
> INSERT INTO ClientContacts VALUES (100, 1)
> INSERT INTO ClientContacts VALUES (100, 2)
> UPDATE Clients SET MainContactID=1 WHERE ClientID=100
>
> INSERT INTO Clients (ClientID, ClientName)
> VALUES (200, 'Another Company, Ltd.')
> INSERT INTO Contacts VALUES (3, 'John', 'Doe')
> INSERT INTO Contacts VALUES (4, 'Jane', 'Doe')
> INSERT INTO ClientContacts VALUES (200, 2)
> INSERT INTO ClientContacts VALUES (200, 3)
> INSERT INTO ClientContacts VALUES (200, 4)
> UPDATE Clients SET MainContactID=3 WHERE ClientID=200
> SET NOCOUNT OFF
>
> Let's suppose you want to delete the client with the ClientID=100.
> Using a simple "DELETE Clients WHERE ClientID=100" works (without any
> error): the client is deleted and it's links with the contacts, too
> (but the contacts themselves remain). If you want to also delete the
> contacts (the ones that are not linked with any other client), you can
> use the following trigger:
> CREATE TRIGGER Clients_DeleteContacts ON Clients
> INSTEAD OF DELETE
> AS
> IF @.@.ROWCOUNT>0 BEGIN
> SET NOCOUNT ON
> UPDATE Clients SET MainContactID=NULL
> WHERE ClientID IN (SELECT ClientID FROM deleted)
> DELETE Contacts WHERE ContactID IN (
> SELECT x.ContactID FROM ClientContacts x
> WHERE x.ClientID IN (SELECT ClientID FROM deleted)
> AND NOT EXISTS (
> SELECT * FROM ClientContacts y
> WHERE x.ContactID=y.ContactID
> AND y.ClientID NOT IN (SELECT ClientID FROM deleted)
> )
> )
> DELETE Clients
> WHERE ClientID IN (SELECT ClientID FROM deleted)
> END
> Razvan
>

Chosen datatype for Primary key field and performance questions?

Hi there,

I have been hired for a couple of weeks to investigate the performance of a sql server 2000 system.

One of the things that strikes me is that all the Primary key (identity field) fileds uses an decimal(18,0) as it's datatype.

An decimal with a precision of 18,0 takes 9 bytes for each column, while an int takes only 4 bytes and and bigint 8 bytes.

Many tables aren't that big, so the values will fit in an int datatype.

1. Is iot a good option to change the decimals columns to an int column ?

2. Many of these columns are indexed by a clustered index. Can the decimal datatype be a performance issue ?

3. sometimes they have deadlocks due page splits. Can this by reduced by changing the data types, while more data fit's into an page?

Thanks in advance,

Greetz,

Patrick de Jong

To me decimal (18,0) does not make sense at all... if at all u need to store larger number u could have gone for BigInt. Ofcourse the index size increases. To reduce the page split the change of datatype may not be sufficient. you many need to re-look your fillfactor for the index.

Madhu

|||

Using int for an indentity column is the most common solution, but just be aware of the roughly 2.1 billion upper limit for that data type. If 2.1 billion is not large enough, most people use bigint.

Changing your fillfactor can help minimize page splits, at the cost of making your index larger. Your deadlocks are probably not caused by page splits. Its more likely that they are caused by lack of, or improper indexes. You might try running this query to see if you are seeing any blocking:

-- Detect blocking

SELECT blocked_query.session_id AS blocked_session_id,

blocking_query.session_id AS blocking_session_id,

sql_text.text AS blocked_text,

sql_btext.text AS blocking_text,

waits.wait_type AS blocking_resource

FROM sys.dm_exec_requests AS blocked_query

INNER JOIN sys.dm_exec_requests AS blocking_query

ON blocked_query.blocking_session_id = blocking_query.session_id

CROSS APPLY

(SELECT *

FROM sys.dm_exec_sql_text(blocking_query.sql_handle)

) sql_btext

CROSS APPLY

(SELECT *

FROM sys.dm_exec_sql_text(blocked_query.sql_handle)

) sql_text

INNER JOIN sys.dm_os_waiting_tasks AS waits

ON waits.session_id = blocking_query.session_id

Checking Foreign Keys in script

I have the following in an update script.
ALTER TABLE [dbo].[SoftwareRentalMaster] ADD
CONSTRAINT [FK_SoftwareRentalMaster_ProductLookup] FOREIGN KEY
(
[ProductCode]
) REFERENCES [dbo].[ProductLookup] (
[ProductCode]
)
GO
I would like to check if the FOREIGN KEY exists and delete or use an if
statement to prevent the following error messages
Server: Msg 2714, Level 16, State 4, Line 2
There is already an object named 'FK_SoftwareRentalMaster_ProductLookup' in
the database.
Server: Msg 1750, Level 16, State 1, Line 2
Could not create constraint. See previous errors.
How can i do this
Regards
Jeff
--
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.691 / Virus Database: 452 - Release Date: 26/05/2004Hi,
Use the below sample of script. Replace the table name, constraint name and
column names based on ur requirement.
if exists(select constraint_name from
INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS where constraint_name ='fk_i')
Begin
select 'Foreign Key exists'
select 'dropping the Foreign Key constraint'
alter table x_2 drop constraint fk_i
end
select 'Creating the Foreign key constraint back to table'
Alter table x_2 add constraint fK_i foreign key (i) references x_1(i)
Thanks
Hari
MCDBA
"Jeff Williams" <jeff.williams@.hardsoft.com.au> wrote in message
news:uKPPPvFREHA.3728@.TK2MSFTNGP10.phx.gbl...
> I have the following in an update script.
> ALTER TABLE [dbo].[SoftwareRentalMaster] ADD
> CONSTRAINT [FK_SoftwareRentalMaster_ProductLookup] FOREIGN KEY
> (
> [ProductCode]
> ) REFERENCES [dbo].[ProductLookup] (
> [ProductCode]
> )
> GO
> I would like to check if the FOREIGN KEY exists and delete or use an if
> statement to prevent the following error messages
> Server: Msg 2714, Level 16, State 4, Line 2
> There is already an object named 'FK_SoftwareRentalMaster_ProductLookup'
in
> the database.
> Server: Msg 1750, Level 16, State 1, Line 2
> Could not create constraint. See previous errors.
> How can i do this
> Regards
> Jeff
>
> --
> Outgoing mail is certified Virus Free.
> Checked by AVG anti-virus system (http://www.grisoft.com).
> Version: 6.0.691 / Virus Database: 452 - Release Date: 26/05/2004
>

Checking for row existence with secondary key

Hi -
I'm no SQL wizard (obviously). I have a table (conceivably very
large (500k+rows)) with a non-unique secondary index. I need an
efficient query to check for row existence using that secondary index.
Any ideas will be appreciated...
Thanks,
BryanIF EXISTS (SELECT * FROM yourTable AS a WHERE a.Col = YourCondition)
-- do your stuff here
Andrew J. Kelly SQL MVP
"Bryan" <bryan@.newsgroups.nospam> wrote in message
news:bijqk11p4ls4fq0dem7gnfee1n6e8vd8d6@.
4ax.com...
> Hi -
> I'm no SQL wizard (obviously). I have a table (conceivably very
> large (500k+rows)) with a non-unique secondary index. I need an
> efficient query to check for row existence using that secondary index.
> Any ideas will be appreciated...
> Thanks,
> Bryan|||Bryan,
DDL would help but try:
SELECT ID
FROM TABLE1
WHERE NOT EXISTS(SELECT * FROM TABLE2 WHERE TABLE2.ID = TABLE1.ID)
HTH
Jerry
"Bryan" <bryan@.newsgroups.nospam> wrote in message
news:bijqk11p4ls4fq0dem7gnfee1n6e8vd8d6@.
4ax.com...
> Hi -
> I'm no SQL wizard (obviously). I have a table (conceivably very
> large (500k+rows)) with a non-unique secondary index. I need an
> efficient query to check for row existence using that secondary index.
> Any ideas will be appreciated...
> Thanks,
> Bryan

Checking for Primary Key Dependencies

Anybody know if there is a system function that can be used from a
stored procedure that determines if a given primary key has existing
dependencies? I want to make a check for this and if there are none, I
will delete the record. If there are, I will change a field called
bitStatus from 1 to 0. Enterprise Mgr. does something like this under
All Tasks, Display Dependencies. The normal way I do it is to manually
check for the existance of the primary key in every dependent table.

SQL 2000 serverI don't know about dependancies, but iof you use
EXEC sp_primarykeys (check books for parameter)
This will give you a list of the PK

--
__________________________________________________ _________________
Remotely manage MS SQL db with SQLdirector - www.ciquery.com/tools/sqldirector/

"Dan Hartshorn" <dharts@.yahoo.com> wrote in message news:8ce6b687.0308281418.12df77dc@.posting.google.c om...
> Anybody know if there is a system function that can be used from a
> stored procedure that determines if a given primary key has existing
> dependencies? I want to make a check for this and if there are none, I
> will delete the record. If there are, I will change a field called
> bitStatus from 1 to 0. Enterprise Mgr. does something like this under
> All Tasks, Display Dependencies. The normal way I do it is to manually
> check for the existance of the primary key in every dependent table.
>
> SQL 2000 server|||John Bell (jbellnewsposts@.hotmail.com) writes:
> How about sp_depends?
> If you want different output look at the source in master.

Not sure what you are thinking of, but it does not seem to me that
SQL Server saves any of this kind of information on sysdepends. It
saves CHECK constraints, but not FOREIGN KEY constraints. Presumably,
because CHECK constraints have code, while FK constraints have not.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Dan,

It sounds like you are asking this:

How can I find out if a particular primary key value
exists in the foreign key column of any dependent table?

Or for example, does customer number #324989 have any
entries in the Orders table, the BadChecks table, or the
ReturnedMerchandise table?

The dependencies displayed in Enterprise Manager are
dependencies of database objects (tables, columns,
views, etc.), not of single rows of data, so I don't think
sp_depends or Enterprise Manager is going to have anything.

And I don't think there is simple function to do this,
but there are solutions to the problem. This
is something like a garbage collection problem for
pointers in C - how do we know we've deleted the last pointer
to allocated data?

In the context of PK/FK relationships, you could
keep a master count of referring rows. Keep an integer,
in the main table - not just a bit, and that count will
be at zero when there are no references. It would have to
be updated by triggers on the dependent tables.

Let us know if this is what you are asking about.

-- Steve Kass
-- Drew University
-- Ref: 5D4089BF-0B8A-4D45-B820-923A2D6886EE

Dan Hartshorn wrote:
> Anybody know if there is a system function that can be used from a
> stored procedure that determines if a given primary key has existing
> dependencies? I want to make a check for this and if there are none, I
> will delete the record. If there are, I will change a field called
> bitStatus from 1 to 0. Enterprise Mgr. does something like this under
> All Tasks, Display Dependencies. The normal way I do it is to manually
> check for the existance of the primary key in every dependent table.
> SQL 2000 server

Checking for duplicates

Hi,
I have to run through a list of tables and check for duplicates. The
tables have no primary key setup but we expect that there should be only
1 record for a combination of fields.
So for example if I keep it simple and imagine there is a table called
customers with loads of fields and in here the uniqueness of each row is
defined by the fields salesrepid and customerid so there should be only
1 occurrence of eg salesrep xyz123 and customer abc123
What I need to do is somehow look at the table and check to make sure
that the above combination of salesrep xyz123 and customer abc123 only
occurs once and if it does oocur more than once then note it / report on it.
Any ideas on how to approach it? I imagine I could have a static table
containing the tables to be checked plus the fields of each table that
make up the uniquness. So if the tables for checking where in a table
called checkthese then in there I could have a field called tablename,
and then further rows called pkf1 (primary key field 1), pkf2, pkf3 and
so on and for the customers table the pkf1 would have value salesrepid
and pkf2 would have value of customerid.
I've probably not made much sense up there but basically what I need to
do is read a table that contains table names and fields that make up the
uniqueness of the table and then go and check those tables to make sure
that there are no more than one of each record that makes up the uniqueness.
any ideas?
tia,
toeYou could store the table names and column names in a table...or you could
just put them within a script or stored procedure.
To check for uniqueness on one table you could do something like this:
SELECT TheFirstCol, TheSecondCol.... (repeat as needed),
COUNT(*) AS TheDuplicateCount
FROM YourTable
GROUP BY TheFirstCol, TheSecondCol.... (repeat as needed)
HAVING COUNT(*) > 1
You could also do something like this
IF EXISTS (
SELECT TheFirstCol, TheSecondCol.... (repeat as needed),
COUNT(*) AS TheDuplicateCount
FROM YourTable
GROUP BY TheFirstCol, TheSecondCol.... (repeat as needed)
HAVING COUNT(*) > 1)
BEGIN
PRINT 'Duplicate found in table xyz'
END
...repeat
--
Keith Kratochvil
"toedipper" <send_rubbish_here734@.hotmail.com> wrote in message
news:0z0fg.5150$O7.3856@.newsfe5-win.ntli.net...
> Hi,
> I have to run through a list of tables and check for duplicates. The
> tables have no primary key setup but we expect that there should be only 1
> record for a combination of fields.
> So for example if I keep it simple and imagine there is a table called
> customers with loads of fields and in here the uniqueness of each row is
> defined by the fields salesrepid and customerid so there should be only 1
> occurrence of eg salesrep xyz123 and customer abc123
> What I need to do is somehow look at the table and check to make sure that
> the above combination of salesrep xyz123 and customer abc123 only occurs
> once and if it does oocur more than once then note it / report on it.
> Any ideas on how to approach it? I imagine I could have a static table
> containing the tables to be checked plus the fields of each table that
> make up the uniquness. So if the tables for checking where in a table
> called checkthese then in there I could have a field called tablename, and
> then further rows called pkf1 (primary key field 1), pkf2, pkf3 and so on
> and for the customers table the pkf1 would have value salesrepid and pkf2
> would have value of customerid.
> I've probably not made much sense up there but basically what I need to do
> is read a table that contains table names and fields that make up the
> uniqueness of the table and then go and check those tables to make sure
> that there are no more than one of each record that makes up the
> uniqueness.
> any ideas?
> tia,
> toe|||Thanks Keith.
If I where to store the table name and columns to check in a table then
have you any pointers On how I would the go to the table(s) and check
that there is only 1 occurrence for each set of columns?
So if I have the table called 'checkthese' with the fields and data below
Fields Data
tablename: customers
pkf1: salesrepid
pkf2: customerid
How would I actaully look that up and then go to the customers table to
make sure there is only one row of each?
Cheers,
toe
Keith Kratochvil wrote:
> You could store the table names and column names in a table...or you could
> just put them within a script or stored procedure.
> To check for uniqueness on one table you could do something like this:
> SELECT TheFirstCol, TheSecondCol.... (repeat as needed),
> COUNT(*) AS TheDuplicateCount
> FROM YourTable
> GROUP BY TheFirstCol, TheSecondCol.... (repeat as needed)
> HAVING COUNT(*) > 1
> You could also do something like this
> IF EXISTS (
> SELECT TheFirstCol, TheSecondCol.... (repeat as needed),
> COUNT(*) AS TheDuplicateCount
> FROM YourTable
> GROUP BY TheFirstCol, TheSecondCol.... (repeat as needed)
> HAVING COUNT(*) > 1)
> BEGIN
> PRINT 'Duplicate found in table xyz'
> END
> ...repeat|||One method would be to cursor through the data within checkthese and build
and execute the appropriate sql statement.
You could also write a sql statement that would create the appropriate T-SQL
commands that you would have to execute on your own.
I will let you explore the cursor option a bit. The other option would look
something like this:
--your table
create table #checkthese (TableName varchar(128), PKcols varchar(2000))
insert into #checkthese (TableName, PKcols) VALUES ('customers',
'salesrepid, customerid')
insert into #checkthese (TableName, PKcols) VALUES ('SalesRep',
'salesrepid')
GO
--the select (run the output)
SELECT 'IF EXISTS (SELECT ' + PKcols + ' , COUNT(*) FROM ' + TableName + '
GROUP BY ' + PKcols + ' HAVING COUNT(*) > 1 )
BEGIN
PRINT ''Duplicates found within '' + TableName
END' + char(13) + char(10) + 'GO'
from #checkthese
Keith Kratochvil
"toedipper" <send_rubbish_here734@.hotmail.com> wrote in message
news:447CBDE8.9030902@.hotmail.com...
> Thanks Keith.
> If I where to store the table name and columns to check in a table then
> have you any pointers On how I would the go to the table(s) and check that
> there is only 1 occurrence for each set of columns?
> So if I have the table called 'checkthese' with the fields and data below
> Fields Data tablename: customers
> pkf1: salesrepid
> pkf2: customerid
> How would I actaully look that up and then go to the customers table to
> make sure there is only one row of each?
> Cheers,
> toe
>
> Keith Kratochvil wrote: