about sql database

Question

Q ) can I use primary key and forign key in same table

Gnanavel Sekar · Answer

we can but we can't keep the same id(column) as primary and foreign key in same table. here is the tutorial for sqlserver constraints. https://www.techonthenet.com/sql_server/

Nilesh Shah · Answer

@Gnanavel Sekar You are wrong to say  we can but we can't keep the same id(column) as primary and foreign key in same table. You can have primary key and foreign key on same column. Run below script it is possible. Enter some data and you will see. CREATE TABLE [dbo].[Table_1]( [ID] [ int ] NOT NULL  CONSTRAINT [PK_Table_1] 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 CREATE TABLE [dbo].[Table_2]( [ID] [ int ] NOT NULL  CONSTRAINT [PK_Table_2] 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].[Table_2] WITH CHECK ADD CONSTRAINT [FK_Table_2_Table_1] FOREIGN KEY ([ID]) REFERENCES [dbo].[Table_1] ([ID]) GO ALTER TABLE [dbo].[Table_2] CHECK CONSTRAINT [FK_Table_2_Table_1] GO

Nitin Sontakke · Answer

Sure why not? The classical example is employee table with manager Id in the same table. Looks much like an Interview question to me!

Gnanavel Sekar · Answer

Thanks  @Nilesh Shah i got it...Thank you so much for correction

Ben Chris · Answer

you can. Here is example of how to do it http://stackoverflow.com/questions/25878192/how-to-add-a-foreign-key-referring-to-itself-in-sql-server-2008