October 1, 2003 at 10:06 am
I have two tables:
USER and COMPONENTS
USER.User_ID is a primary key
COMPONENTS.User_ID is part of combined primary key in COMPONENTS table:
User_ID
Org_ID
Component_ID
I try to cascade all the deletions
to COMPONENTS table and for that reason
I make an attempt to create FOREIGN KEY
in COMPONENTS.
It gives me an error:
"The columns in table ;User' do not match
an existing Primary Key or UNIQUE constrains'
I deleted all records form COMPONENTS
that do not have a match in USER table.
It did not help.
October 1, 2003 at 10:18 am
It sounds like only one column references the Users table, so the foreign key would also be only one column, e.g.:
CREATE TABLE Users(
UserId int PRIMARY KEY,
UName varchar(40))
CREATE TABLE Components(
UserId int FOREIGN KEY REFERENCES Users,
OrgId int,
ComponentId int,
PRIMARY KEY (UserId, OrgId, ComponentId))
--Jonathan
--Jonathan
October 1, 2003 at 10:23 am
Hi Jonathan!
Thanks for quick response.
That's exactly what I try to do
only in GUI
I create a RELATIONSHIP
USER.UserID -- COMPONENTS.UserID
What would be ALTER TABLE
script for COMPONENTS table?
I'll try it in Query Analyzer
October 1, 2003 at 10:45 am
quote:
Hi Jonathan!Thanks for quick response.
That's exactly what I try to do
only in GUI
I create a RELATIONSHIP
USER.UserID -- COMPONENTS.UserID
What would be ALTER TABLE
script for COMPONENTS table?
I'll try it in Query Analyzer
ALTER TABLE Components ADD FOREIGN KEY (UserID) REFERENCES Users ON DELETE CASCADE
Please don't use reserved words (User, User_ID) for identifiers.
--Jonathan
--Jonathan
October 1, 2003 at 11:11 am
Jonathan,
I ran your ALTER TABLE
and got this error:
Server: Msg 1778, Level 16, State 1, Line 1
Column 'User.Username' is not the same data type as referencing column 'ComponentsCompleted.User_ID' in foreign key 'FK__Component__User___332B7579'.
October 1, 2003 at 1:18 pm
quote:
Jonathan,I ran your ALTER TABLE
and got this error:
Server: Msg 1778, Level 16, State 1, Line 1
Column 'User.Username' is not the same data type as referencing column 'ComponentsCompleted.User_ID' in foreign key 'FK__Component__User___332B7579'.
Sounds like you made UserName the primary key on the User table. That's probably a mistake because you earlier wrote that User_ID is the primary key.
Find the name of the erroneous constraint; I use sp_help:
sp_help [User]
Then you can drop the wrong primary key and create the correct one, e.g.:
ALTER TABLE [User] DROP CONSTRAINT pk_Users_UserName
ALTER TABLE [User] ADD PRIMARY KEY ([User_ID])
Then you should be able to add the foreign key constraint to the Components table.
This stuff is probably easier to do with Enterprise Manager, but my skills aren't very good with the GUI utilities.
--Jonathan
--Jonathan
October 1, 2003 at 1:23 pm
Thank you Jonathan!
You helped me to discover
a very stupid thing
in our database design.
Somebody else created this database
and I'm responsible for
maintaining the ASP Application
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply