December 26, 2010 at 5:43 am
USE [SSISDB]
GO
/****** Object: Table [dbo].[Table_2] Script Date: 12/26/2010 18:10:23 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Table_2](
[col3] [int] NOT NULL,
[col4] [int] NOT NULL,
CONSTRAINT [PK_Table_2] PRIMARY KEY CLUSTERED
(
[col3] 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].[Table_2] WITH CHECK ADD CONSTRAINT [FK_Table_2_table1] FOREIGN KEY([col4])
REFERENCES [dbo].[table1] ([col1])
GO
ALTER TABLE [dbo].[Table_2] CHECK CONSTRAINT [FK_Table_2_table1]
GO
USE [SSISDB]
GO
/****** Object: Table [dbo].[table1] Script Date: 12/26/2010 18:10:12 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[table1](
[col1] [int] NOT NULL,
[col2] [int] NOT NULL,
CONSTRAINT [PK_table1] PRIMARY KEY CLUSTERED
(
[col1] 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].[table1] WITH CHECK ADD CONSTRAINT [FK_table1_Table_2] FOREIGN KEY([col2])
REFERENCES [dbo].[Table_2] ([col3])
GO
ALTER TABLE [dbo].[table1] CHECK CONSTRAINT [FK_table1_Table_2]
GO
Hi,
These are the two tables which reference the relation ship with each other.
then how can i insert data to this tables.
Malleswarareddy
I.T.Analyst
MCITP(70-451)
December 26, 2010 at 7:16 am
You’ll have to disable one of the constraints in order to insert the first record (alter it using the NOCHECK key word). After you’ll insert the matching record into the other table you can enable it again. Notice that you’ll have to do this each time that you’ll insert a new per of records. Can you pleas explain why do you want both tables to reference each other?
Adi
--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
December 26, 2010 at 8:41 am
My suggestion will be an outline of what could be done rather than specific T-SQL.
1. Create Table_2 without a primary key.
2. Start the T-SQL to perform the insert into table1 with a BEGIN TRANSACTION.
3. Wrap the insert into Table1 in a TRY CATCH block, and if an there is an error (Duplicate primary key) ROLL BACK THE TRANSACTION and exit the T-SQL
4a. If it is crucial that Table_2 NOT have duplicate values in Col3 then code an IF EXISTS statement to test if the value inserted into Table1 Col2 exists in Table_2 col3. Again using a TRY CATCH block,
4b. If it does exist ROLL BACK THE TRANSACTION, and exit. if it does not exist insert the value in Table_2 col3.
Seriously review the requirement and change the database tables design.
December 27, 2010 at 12:37 am
Hi its an interview question in IBM.But the guy told that with 3 dml staetments we can do this.
But when we diable using alter its as DDL .Please try to solve this problem.I really tried but due conflict in primary and foreing key i cant directly enter the data into any table.
I know this design is not good.But this is an intervirew question. and i want to write what ever he wants.So please do not hesitate on me.
Malleswarareddy
I.T.Analyst
MCITP(70-451)
December 28, 2010 at 12:56 pm
malleswarareddy_m,
I keep several folders inside my SQL tools folder just for trivia that I don't think I will ever use. In fact, the solution to your interviewer's problem may very will be in there somewhere. I don't bother reading that trivia because I doubt seriously that I'll ever use any of it in a real production environment. I keep it just in case.
If an interviewer were to ask me this question I would ask him if it was expected of me to know a lot of stuff that will never be used so I could win a SQL trivia contest or if I was expected to know what is needed in a real production environment.
I tried and I couldn't come up with any valid reason for the table design in the problem. If I ever came across such a silly design in a production environment the problem would have been solved before I got there by the designers.
Todd Fifield
December 28, 2010 at 1:14 pm
malleswarareddy_m (12/27/2010)
Hi its an interview question in IBM.But the guy told that with 3 dml staetments we can do this.But when we diable using alter its as DDL .Please try to solve this problem.I really tried but due conflict in primary and foreing key i cant directly enter the data into any table.
I know this design is not good.But this is an intervirew question. and i want to write what ever he wants.So please do not hesitate on me.
It's entirely possible that DB2 (the IBM relational database product) would allow this kind of insert. I wouldn't know.
SQL Server doesn't.
I'd tell the interviewer, "Okay, I'll bite. How do you do that?" These kind of questions aren't about technical proficiency. They're about problem-solving technique. Basically, translate it to, "So, how do you go about dealing with a problem that seems to have no solution?" In your case, apparently, you go to an online forum and ask for help. That's not a bad way to go about it. Tell him exactly that.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
December 28, 2010 at 1:37 pm
GSquared - 12/28/2010
I'd tell the interviewer, "Okay, I'll bite. How do you do that?" These kind of questions aren't about technical proficiency. They're about problem-solving technique. Basically, translate it to, "So, how do you go about dealing with a problem that seems to have no solution?" In your case, apparently, you go to an online forum and ask for help. That's not a bad way to go about it. Tell him exactly that.
Good advice Gus. I sometimes get a little uppity when asked trick questions.
Todd Fifield
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply