[RESOLVED] Explanation

[scottybwoy]

Hi could any one give me an explanation of this query please.

CONSTRAINT [PK__client__7C8480AE] PRIMARY KEY CLUSTERED
(
[clientId] ASC
) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO
SET ANSI_PADDING OFF
GO
ALTER TABLE [dbo].[client] WITH NOCHECK ADD CONSTRAINT [cust_client] FOREIGN KEY([custId])
REFERENCES [dbo].[customers] ([custId])
GO
ALTER TABLE [dbo].[client] CHECK CONSTRAINT [cust_client]
GO
ALTER TABLE [dbo].[client] WITH NOCHECK ADD CONSTRAINT [staff_client] FOREIGN KEY([userId])
REFERENCES [dbo].[users] ([userId])
GO
ALTER TABLE [dbo].[client] CHECK CONSTRAINT [staff_client]

Does it make clientId a Primary Key and build relationships between 2 other tables?
What is happening is that when I go to insert a record it gives me an error of :

violation of PRIMARY KEY constraint 'PK__client__7C8480AE'. Cannot insert duplicate key in object 'client'.

However the data I am inserting does have a duplicate of custId and userId but unique clientId. The relationships I want are one to many from the other tables to this one. Does the info above show this? If not how do I specify to allow duplicates on custId and userId? Thanks in advance

[russell]

the data u are addding either has a clientId that already exists in the table or there are duplicate clientIds in the data u are trying to add.

the first bit of code is the PK definition.

the rest simply creates 2 foreign keys. so u got it right. the FKs are created with NO CHECK which means "don't enforce it for existing data" then the constraint is turned on after creation in the CHECK CONSTRAINT lines. this is kind of silly to do it that way. i'd do it like this

ALTER TABLE [dbo].[client] ADD CONSTRAINT [cust_client] FOREIGN KEY([custId])
REFERENCES [dbo].[customers] ([custId])
GO

then dont need the check constraint statements

[scottybwoy]

Hmm the data I'm entering does have unique clientId, thanks for explaining the statement, however i did nt do it, it was the automated generation from MSSQL 2005 under script table. What I was wonderin is if the Unique keys were referenced from the other tables as in a 1 to 1 relationship, cos thats not what I want. Any other ideas as to y my data won't insert without the error?

[russell]

how are u adding the data? from another table?

[scottybwoy]

Data being entered via php from a form. But is still in develop stage so i can see that the client id is unique

[russell]

i hope this doesn't sound argumentative, but either they are trying to insert a dupe, or u r looking at the wrong table. pk violation is usually an easy one to troubleshoot.

try this, in query analyzer or ssms (not EM), rdouble-click table to expand it. expand constraints. look for the one called PK__client__7C8480AE, right-click that and choose script to new window.

if it is exactly what u posted, then next, have developer (is that you?) go into the PHP and just b4 executing echo the query
echo $sql;
then go back to sql and select client_id from Client where client_id = <whatever id was passed in>
finally, right-click the table and script that to new window as create. post the entire table definition back here. we'll get a closer look

[scottybwoy]

Hi Under the constraints there is no entry at all. Although it is indexed as so :

/****** Object: Index [PK__client__7C8480AE] Script Date: 01/24/2007 12:44:25 ******/
ALTER TABLE [dbo].[client] ADD CONSTRAINT [PK__client__7C8480AE] PRIMARY KEY CLUSTERED
(
[clientId] ASC
) ON [PRIMARY]


This is the insert query :
INSERT INTO client (clientId, userId, name, dEmail, dTel, custId, subscribe) VALUES ('TE00001', 3, 'BIL', 'bil@bo.com', 01234567890, 'TES000', 0)

select clientId from Client where clientId = 'TE00001' : returns no result, as expected.

This is the table query :

/****** Object: Table [dbo].[client] Script Date: 01/24/2007 15:54:28 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[client](
[custId] [varchar](6) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[clientId] [varchar](6) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[userId] [smallint] NOT NULL,
[name] [varchar](40) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[position] [varchar](20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[dEmail] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[dTel] [varchar](32) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[dFax] [varchar](32) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[mobile] [varchar](32) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[lastCont] [timestamp] NOT NULL,
[followDate] [datetime] NULL,
[subscribe] [bit] NOT NULL,
[follow] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[notes] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
CONSTRAINT [PK__client__7C8480AE] PRIMARY KEY CLUSTERED
(
[clientId] ASC
) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO
SET ANSI_PADDING OFF
GO
ALTER TABLE [dbo].[client] WITH NOCHECK ADD CONSTRAINT [cust_client] FOREIGN KEY([custId])
REFERENCES [dbo].[customers] ([custId])
GO
ALTER TABLE [dbo].[client] CHECK CONSTRAINT [cust_client]
GO
ALTER TABLE [dbo].[client] WITH NOCHECK ADD CONSTRAINT [staff_client] FOREIGN KEY([userId])
REFERENCES [dbo].[users] ([userId])
GO
ALTER TABLE [dbo].[client] CHECK CONSTRAINT [staff_client]


Can you tell if the Database is trying to generate the ID itself, as I don't want it to do that, and I suppose that may be a reason why this occurs. I have another table with pretty much exactly the same process with no problems at all.

Thanks for your time ;)

[russell]

no it isn't trying to auto-generate the id.

any chance there are two tables (or a view) called client but with different owner?

if u execute that insert in ssms does it work?

[scottybwoy]

Thanks finally, I found what was wrong. I executed the query in ssms (schoolboy error not to have done it before) and it gave me a different error.

Msg 8152, Level 16, State 9, Line 1
String or binary data would be truncated.

And realised that the ClientId my program generated was 1 char too long, after fixing that part of the code it inserted like a dream. Thanks for your time.