August 11, 2010 at 10:19 am
Add this to the thousands of FK questions that have been asked!
Here's the deal. I have an existing table that acts as my primary key, and an empty table with an FK relation to the first table. I'm trying to figure out how to programatically populate the second table.
Naturally, I'm getting an FK constraint error.
Here's where I'm getting stumped. I've read solutions that involve making the FK nullable. However, that's not an option here. Reason -- the FK is also part of a composite primary key.
Any ideas as to how to get around this?
Edit: As promised, sample data
CREATE TABLE #Names (
[ID] [int] IDENTITY(100,1) NOT NULL,
[Name] [nvarchar](50) NOT NULL,
CONSTRAINT [PK_Names] PRIMARY KEY CLUSTERED
(
[ID] ASC
)
insert into #Names (Name) values ('John')
insert into #Names (Name) values ('Jacob')
insert into #Names (Name) values ('Jingleheimer')
insert into #Names (Name) values ('Schmidt')
CREATE TABLE #Services(
[ID] [int] NOT NULL,
[ServiceID] [int] NOT NULL,
[ServiceName] [nvarchar](50) NULL,
CONSTRAINT [PK_Services] PRIMARY KEY CLUSTERED
(
[ID] ASC,
[ServiceID] ASC,
)
ALTER TABLE #Services WITH CHECK ADD CONSTRAINT [FK_Services_Names] FOREIGN KEY([ID])
REFERENCES #Names ([ID])
+--------------------------------------------------------------------------------------+
Check out my blog at https://pianorayk.wordpress.com/
August 11, 2010 at 10:34 am
Umm, remove the PK or FK and populate the table. Then restore the original PK or FK attributes.
August 11, 2010 at 10:40 am
jerry-621596 (8/11/2010)
Umm, remove the PK or FK and populate the table. Then restore the original PK or FK attributes.
Would prefer not to do that, if I can at all avoid it. This needs to happen at run time. I'm working on writing a stored procedure to populate the table.
+--------------------------------------------------------------------------------------+
Check out my blog at https://pianorayk.wordpress.com/
August 11, 2010 at 10:59 am
OK, so not for initial load then?
August 11, 2010 at 11:09 am
The [Services].[ID] column is part of your PK, and you havn't made it clear why you need to insert the table while leaving that column NULL. From what I see, [ID] is actually the foreign key of a person contained in the [Names] table. If it's logically possible for a service to have a temporary status of unassigned, then [ID] doesn't belong in the PK of the [Services] table, it's optional. It seems to me that [ServiceID] should contain a unique value and serve alone as the PK. I would suggest a clustered PK on [ServiceID] and create a non-clustered non-unique index on [ID]. You could also add a record in the [Names] table called "Unassigned" and stub it's ID in the [Services] table for the initial insert, but really it seems you should just leave it NULL and out of the PK.
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
August 11, 2010 at 11:11 am
Stop the presses. I figured out the problem.
It was a simple syntax error. I inadvertently used the wrong variable name in my code. Once I named it to the correct name, it worked like a charm.
Sorry for wasting everyone's bandwidth!
+--------------------------------------------------------------------------------------+
Check out my blog at https://pianorayk.wordpress.com/
August 11, 2010 at 11:25 am
I guess I don't understand the problem. I used the following and inserted two rows into the second (services) table.
INSERT INTO Services(ID, ServiceID, ServiceName)
VALUES(100, 1, 'Cleaning'),
(101, 2, 'Repair')
As far as I can tell, you only need to make sure the ID you use for the second table exists in the Names table.
Maybe you could post the code for your proc. That may help us understand the actual problem you are trying to solve.
August 11, 2010 at 11:28 am
jerry-621596 (8/11/2010)
I guess I don't understand the problem. I used the following and inserted two rows into the second (services) table.
INSERT INTO Services(ID, ServiceID, ServiceName)VALUES(100, 1, 'Cleaning'),
(101, 2, 'Repair')
As far as I can tell, you only need to make sure the ID you use for the second table exists in the Names table.
Maybe you could post the code for your proc. That may help us understand the actual problem you are trying to solve.
Read my last reply. I figured out what the problem was, and it turned out to be something really stupid! :blush:
Thanks for the assist, anyway!
+--------------------------------------------------------------------------------------+
Check out my blog at https://pianorayk.wordpress.com/
August 11, 2010 at 11:29 am
Yeah, saw that after posting. Keep getting side tracked by work.
August 11, 2010 at 11:31 am
jerry-621596 (8/11/2010)
Yeah, saw that after posting. Keep getting side tracked by work.
I know the feeling. Damned work stuff always seems to get in the way! :hehe:
+--------------------------------------------------------------------------------------+
Check out my blog at https://pianorayk.wordpress.com/
August 11, 2010 at 11:52 am
Ray K (8/11/2010)
jerry-621596 (8/11/2010)
Yeah, saw that after posting. Keep getting side tracked by work.I know the feeling. Damned work stuff always seems to get in the way! :hehe:
It's good that you figured out. It is nice to see somebody own up to it and also to post back that the answer was found.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
August 11, 2010 at 12:10 pm
CELKO (8/11/2010)
Those magical generic and vague data element names along with the use of the IDENTITY property as a key are really bad even in skeleton sample tables. Both your (non-)tables have the same magical, generic "id" ! Does it also identify squids and automobile? See how sill that is? Let's fix the skeletons:CREATE TABLE Providers
(provider_id INTEGER NOT NULL PRIMARY KEY,
provider_name VARCHAR(50) NOT NULL);
INSERT INTO Providers (provider_id, provider_name)
VALUES (1, 'John'),
(2, 'Jacob'),
(3, 'Jingleheimer'),
(4, 'Schmidt');
Why is the service_id not the key of the following table? Why do you think the count of the PHYSICAL insertion attempts in another table is an attribute here? Since the Provider is a totally different entity from Services, it is not an attribute of Services. Is your mechanic part of your automobile?
CREATE TABLE Services
(service_id INTEGER NOT NULL PRIMARY KEY,
service_name VARCHAR(50) NOT NULL);
There needs to a relationship table:
CREATE TABLE ProviderServices
(provider_id INTEGER NOT NULL
REFERENCES Providers(provider_id)
ON UPDATE CASCADE
ON DELETE CASCADE,
service_id INTEGER NOT NULL
REFERENCES services(service_id)
ON UPDATE CASCADE,
PRIMARY KEY (provider_id, service_id));
See how a valid design fixes the problems improper design create?
CELKO, you are obviously very intelligent, but some of your responses can be insulting. If you lack the patience to kindly help people here, maybe you could find a less frustrating use of your time.
The greatest enemy of knowledge is not ignorance, it is the illusion of knowledge. - Stephen Hawking
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply