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

Asma Khalid · 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