March 7, 2012 at 3:19 am
Hello!
To begin with, I have little idea of programming in SQL so I use SQL Server Management Studio 2008.
I need to do the following:
I have a table with companies with these columns: NAME | CREATED_BY_USER | MODIFIED_BY_USER | DATE_CREATION | DATE_MODIFICATION
E.g.: SQLSERVERCENTRAL | rsherbatsky | tmosby | 2012/03/01 | 2012/03/06
And there is another table where data from users is stored.
E.g.:
USER | NAME | SURNAME
rsherbatsky | Robin | Sherbatsky
tmosby | Ted | Mosby
What I want to to is to restrict the content of CREATED_BY_USER and MODIFIED_BY_USER columns from the first table to the USER column from the second table.
If I create just one relationship between USER (As primary key) and CREATED_BY_USER (As Foreign Key), everything's fine. But I need to create the other one: USER (As primary key) and MODIFIED_BY_USER (As Foreign Key). SQL Server 2008 Express won't let me because it says: "The ALTER TABLE statement conflicted with the FOREIGN KEY constraint "FK_Companies_Users1". The conflict occurred in database "CRM", table "dbo.Users", column 'ID'."
How can I solve this problem? Can this be made in other way?
Thanks a lot!
March 7, 2012 at 3:26 am
Please will you post DDL for the two tables, as well as (or including) the DDL for the foreign key constraints?
Thanks
John
March 7, 2012 at 3:59 am
I haven't used triggers to do this. I just built it on the diagram of the database. As I said before, i know very little of programming, so I use Sql Server Management Studio to avoid it.:crying:
I don't even know how to extract that DDL you are talking about. There's nothing in the Triggers folder from the Companies table though.
March 7, 2012 at 4:04 am
a.penasco (3/7/2012)
I haven't used triggers to do this. I just built it on the diagram of the database. As I said before, i know very little of programming, so I use Sql Server Management Studio to avoid it.:crying:
I didn't mention triggers.
I don't even know how to extract that DDL you are talking about. There's nothing in the Triggers folder from the Companies table though.
Right-click on the table, choose Script Table as -> CREATE To -> Clipboard.
John
March 7, 2012 at 4:11 am
Companies:
USE [CRM]
GO
/****** Object: Table [dbo].[Companies] Script Date: 03/07/2012 12:07:01 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Companies](
[ID] [nvarchar](255) NOT NULL,
[Date Creation] [datetime] NULL,
[Date Modification] [datetime] NULL,
[>Created By Users Name] [nvarchar](255) NULL,
[>Modified By Users Name] [nvarchar](255) NULL,
CONSTRAINT [PK_Companies] PRIMARY KEY CLUSTERED
(
[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Companies] WITH CHECK ADD CONSTRAINT [FK_Companies_Users] FOREIGN KEY([>Created By Users Name])
REFERENCES [dbo].[Users] ([ID])
ON UPDATE CASCADE
ON DELETE CASCADE
GO
ALTER TABLE [dbo].[Companies] CHECK CONSTRAINT [FK_Companies_Users]
GO
Users:
USE [CRM]
GO
/****** Object: Table [dbo].[Users] Script Date: 03/07/2012 12:09:03 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Users](
[ID] [nvarchar](255) NOT NULL,
[Branch] [nvarchar](255) NULL,
[IsManager] [bit] NULL,
[Active] [bit] NULL,
[LastSyncDate] [nvarchar](255) NULL,
[LastSyncTime] [datetime] NULL,
[LastUpdDate] [datetime] NULL,
[LastUpdTime] [datetime] NULL,
[Team] [nvarchar](255) NULL,
[MyManager] [nvarchar](255) NULL,
[Surname1] [nvarchar](255) NULL,
[PersonalName] [nvarchar](255) NULL,
[ActiveLicense] [bit] NULL,
[DB_Code] [nvarchar](255) NULL,
[DateCreation] [nvarchar](255) NULL,
[RegisterNumber] [nvarchar](255) NULL,
[Telephone] [nvarchar](255) NULL,
[Celular] [nvarchar](255) NULL,
[nvarchar](255) NULL,
[profile] [nvarchar](255) NULL,
[Dpto] [nvarchar](255) NULL,
CONSTRAINT [PK_Users] PRIMARY KEY CLUSTERED
(
[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Users] ADD CONSTRAINT [DF_Users_IsManager] DEFAULT ((0)) FOR [IsManager]
GO
ALTER TABLE [dbo].[Users] ADD CONSTRAINT [DF_Users_Active] DEFAULT ((0)) FOR [Active]
GO
ALTER TABLE [dbo].[Users] ADD CONSTRAINT [DF_Users_ActiveLicense] DEFAULT ((0)) FOR [ActiveLicense]
GO
Thanks a lot John!!
March 7, 2012 at 4:33 am
It worked when I tried it, but then I'm not using SQL Server Express. It's very fiddly and prone to error if you try to do stuff like this through the GUI. I recommend that you take the time to learn how the DDL works - specifically CREATE TABLE and ALTER TABLE statements. In order to create the second foreign key, all you have to do is modify the DDL for the first one. See the statement that starts "ALTER TABLE [dbo].[Companies] WITH CHECK ADD CONSTRAINT"? Just change it so that it uses the correct columns, give it its own name, and run it. Simple as that. If you want the existing data to be checked for violations then you will also need to modify and run the statement after it.
John
March 7, 2012 at 4:37 am
Could I write something like this?
ALTER TABLE [dbo].[Companies] WITH CHECK ADD CONSTRAINT [FK_Companies_Users] FOREIGN KEY([>Created By Users Name], [>Modified By Users Name])
REFERENCES [dbo].[Users] ([ID])
ON UPDATE CASCADE
ON DELETE CASCADE
Thanks.
March 7, 2012 at 4:45 am
Not quite. FK_Companies_Users is the name of the foreign key constraint, so you need to change that - you can't have two objects with the same name. [>Modified By Users Name] is the column on which you place the constraint, so you need to change that to the new column, not add it as an extra column.
Another thing I would advise you to do is read up on naming conventions. There are no hard and fast rules, but most people would agree that using only alphanumeric characters and underscores in object names is good practice.
John
March 7, 2012 at 4:59 am
[>Modified By Users Name] is the column on which you place the constraint, so you need to change that to the new column, not add it as an extra column.
If I add a new constraint, then SQL server complains, and tells me again: The ALTER TABLE statement conflicted with the FOREIGN KEY constraint "FK_Companies_Users1"
Another thing I would advise you to do is read up on naming conventions. There are no hard and fast rules, but most people would agree that using only alphanumeric characters and underscores in object names is good practice.
I definitely agree with you LOL. But I can't do that right now because we need those names to do a massive check.
Thank you very much, I really appreciate your help.
March 7, 2012 at 5:05 am
Please will you post the statement you're trying to run?
Thanks
John
March 7, 2012 at 6:20 am
This is the statement I wrote in the query for the second foreign key (The first, Created_By_User, was already created in the database as you can see in the code I pasted the first time you asked :-)):
ALTER TABLE [dbo].[Companies] WITH CHECK ADD CONSTRAINT [FK_Companies_Users_Modified] FOREIGN KEY([>Modified By Users Name])
REFERENCES [dbo].[Users] ([ID])
This is the error message:
The ALTER TABLE statement conflicted with the FOREIGN KEY constraint "FK_Companies_Users_Modified". The conflict occurred in database "CRM", table "dbo.Users", column 'ID'.
March 7, 2012 at 6:29 am
Well, I don't know why it's working for me but not for you. Please will you run this and post the results:
SELECT
name
,OBJECT_NAME(parent_object_id) AS fk_table
,create_date
,modify_date
FROM
sys.foreign_keys
John
March 7, 2012 at 6:38 am
This is what I get:
FK_Companies_UsersCompanies2012-03-07 14:11:14.8572012-03-07 14:11:14.857
So, this seem to confirm that there's already one foreign key pointing from Users (ID) to Companies (Created_by_user). But now, I need to create the second foreign key: Users (ID, ¡again!) to Companies (Modified_by_User).
And then it's when the problem arrives.
March 7, 2012 at 6:46 am
Sorry John, I found the bug. I did the second foreign key with a different column. Instead of Modified_by_Users I used another one called Manager_users, which also refer to the ID from Users table, and it works perfectly. I've check the Modified_By_users column and detected that several fields weren't filled properly.
Thank you so much, you've been very kind.
March 7, 2012 at 7:32 am
Ok. I was able to create the two foreign keys, but: what if the ID (primary key) from the user changes (say, from tmosby to tedmosby)? I need all the foreign keys to change, and sql server only allows one of the foreign keys to have the sentences "ON UPDATE CASCADE" and "ON DELETE CASCADE" in its code. Will all the columns from Companies refering to User.ID change when the User.ID changes?
Thanks!!
Viewing 15 posts - 1 through 15 (of 19 total)
You must be logged in to reply to this topic. Login to reply