Constraint - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Constraint

Author

Topic

SamC
White Water Yakist

3467 Posts

Posted - 2004-01-23 : 11:24:17

CREATE TABLE Clients (ClientID INT, ....)
CREATE TABLE Users (ClientID INT, CenterID INT, .....)
CREATE TABLE Centers (CenterID INT, ClientID INT, ....)

I need ClientID on all 3 tables, but I need to constrain table Users so CenterID doesn't point to a CenterID belonging to any Client other than the User's Client.

Is it possible to do this with a constraint or is a trigger needed?

Sam

nr
SQLTeam MVY

12543 Posts

Posted - 2004-01-23 : 11:58:28

Not sure this makes sense - is there a UserID somewhere?
Users and Centers seem very similar here.
Maybe just a foreign key from Centers to Users?

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

SamC
White Water Yakist

3467 Posts

Posted - 2004-01-23 : 12:04:25

I omitted all the other columns for brevity with ",....".

ClientID is a PK in Clients
CenterID is a PK in Centers

Users and Centers point to Clients
Users point to Centers

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2004-01-23 : 12:14:47

Do you have a table of centers? just making sure.

just put a FK constraint on the Users table on both key columns, and make it a 1-1 with both key columns on centers.

- Jeff

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2004-01-23 : 12:15:44

Do you have a table out there with a PK of CenterID?

anyway, if I'm hearing you correctly, just put a FK constraint on the Users table on both key columns, and make it a 1-1 with both key columns on the centers table.

- Jeff

SamC
White Water Yakist

3467 Posts

Posted - 2004-01-23 : 12:19:55

quote:
CREATE TABLE Clients (ClientID INT, ....)
CREATE TABLE Users (ClientID INT, CenterID INT, .....)
CREATE TABLE Centers (CenterID INT, ClientID INT, ....)

quote:
ClientID is a PK in Clients
CenterID is a PK in Centers

Users and Centers point to Clients
Users point to Centers

Not sure how to put a FK ON users to that Users.CenterID points to a *valid* Centers row for that Client.

e.g. I want to avoid having a User for client "A" point to a CenterID which belongs to client "B".

Help with this?

setbasedisthetruepath
Used SQL Salesman

992 Posts

Posted - 2004-01-23 : 14:02:53

Aren't you just saying that you have a 2 column (centerID,clientID) FK to centers?

Jonathan
Gaming will never be the same

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2004-01-23 : 14:26:35

that's what it looks like to me .....

- Jeff

SamC
White Water Yakist

3467 Posts

Posted - 2004-01-23 : 16:06:48

OK, OK. So Users(clientid, centerid) must be FK'ed to Center's (clientid, centerid). I admit, I didn't note the ClientID relationship there since CenterID was the usual JOIN.

But I don't understand how a FK could be setup to ensure that:

Users.ClientID = Centers.ClientID

for all

Users.CenterID = Centers.CenterID

Don't FK's simply ensure that the key exists in the table ? This isn't enough. I want to ensure that

User.CenterID

only points to rows in table "Centers" with matching ClientIDs (Users.ClientID = Centers.ClientID)

If this can be done with FKs, could you show a sample FK declaration?

Sam

setbasedisthetruepath
Used SQL Salesman

992 Posts

Posted - 2004-01-23 : 16:11:36

Not sure where the disconnect here is.

A center can only have 1 client, according to the data model you've shared, so matching on both center and client suffices. If centers can have more than 1 client, than your argument holds.

Jonathan
Gaming will never be the same

SamC
White Water Yakist

3467 Posts

Posted - 2004-01-23 : 16:40:57

ALTER TABLE dbo.Users ALTER COLUMN CenterID WITH CHECK

ADD CONSTRAINT MyConstraint FOREIGN KEY (CenterID) REFERENCES dbo.Centers(CenterID)

-- So far, this has added a constraint such that Users(CenterID) contains a value found in Centers(CenterID)

Here's some sample data:

Table Clients
ClientID (PK), ClientName
100, 'IBM'
200, 'Microsoft'

Table Users
ClientID, CenterID, Username
100, 2000, 'John'
200, 2000, 'Ringo'
200, 3000, 'Paul'

Table Centers
CenterID (PK), ClientID, Centername
2000, 100, 'New York'
3000, 200, 'Seattle'

John is valid - under Client IBM, Center 'New York'
Ringo is invalid - under Client Microsoft, Center 'New York'

But the row for Ringo in Users satisfies the FK on CenterID. That is, CenterID = 2000 meets the FK constraint.

Would the following work?

ALTER TABLE dbo.Users ALTER COLUMN CenterID WITH CHECK

ADD CONSTRAINT constraint_name CHECK (logical_expression)

quote:
CHECK

Is a constraint that enforces domain integrity by limiting the possible values that can be entered into a column or columns.

logical_expression

Is a logical expression used in a CHECK constraint and returns TRUE or FALSE. Logical_expression used with CHECK constraints cannot reference another table but can reference other columns in the same table for the same row.

setbasedisthetruepath
Used SQL Salesman

992 Posts

Posted - 2004-01-23 : 16:45:18

quote:
But the row for Ringo in Users satisfies the FK on CenterID. That is, CenterID = 2000 meets the FK constraint.

No, it doesn't. Your FK is on BOTH centerID and clientID.

The "Ringo" row requires a row in centers with centerID 2000 and clientID 200, there isn't, so it fails.

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2004-01-23 : 18:02:14

Sam, I think you want:

ADD CONSTRAINT MyConstraint FOREIGN KEY (ClientID, CenterID) REFERENCES dbo.Centers(ClientID, CenterID)

- Jeff

SamC
White Water Yakist

3467 Posts

Posted - 2004-01-23 : 21:49:33

[code]
CREATE TABLE dbo.[Centers] (
[CenterID] [int] IDENTITY (1011, 100) NOT NULL ,
[ClientID] [int] NOT NULL ,
[CenterName] [nvarchar] (200) NOT NULL ,
[Inactive] [bit] NOT NULL DEFAULT (0),
PRIMARY KEY CLUSTERED ([CenterID], [ClientID]) WITH FILLFACTOR = 80 ON [PRIMARY] ,
CONSTRAINT sCenterFK FOREIGN KEY ([ClientID]) REFERENCES [Clients] ([ClientID])
)
GO
[/code]

Now executing the following

[code]
ALTER TABLE dbo.Users
ADD CONSTRAINT SamTest FOREIGN KEY (CenterID, ClientID) REFERENCES dbo.sCenters(CenterID, ClientID)
[/code]

Results in the following error:

Server: Msg 547, Level 16, State 1, Line 1
ALTER TABLE statement conflicted with TABLE FOREIGN KEY constraint 'SamTest'. The conflict occurred in database 'nimc', table 'Centers'.

setbasedisthetruepath
Used SQL Salesman

992 Posts

Posted - 2004-01-24 : 09:55:55

[code]PRIMARY KEY CLUSTERED ([CenterID], [ClientID]) WITH FILLFACTOR = 80 ON [PRIMARY][/code]

I asked earlier if centerID and clientID were 1-to-1. From this key declaration they are not. The primary key should only contain centerID. This needs to be changed for my proposed solution to work.

Next, you haven't included the NOCHECK clause in the ALTER TABLE so the FK addition will fail if data exists which violates it.

Jonathan
Gaming will never be the same

setbasedisthetruepath
Used SQL Salesman

992 Posts

Posted - 2004-01-26 : 17:17:15

Did that help you out Sam?

Jonathan
Gaming will never be the same

SamC
White Water Yakist

3467 Posts

Posted - 2004-01-26 : 17:29:39

Thanks for your help, I'll make another post here when I've had a chance to return to this problem. I've been working all weekend on another web matter.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources