about sql database

Murari Jha

9y903Learn iOS Programming

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

5 Replies

Know the answer? Post it — somebody with the same question will find it here.

Gnanavel Sekar9y ago
Accepted 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/
0
Gnanavel Sekar9y ago
Thanks , @Nilesh Shah i got it...Thank you so much for correction
+1
Nilesh Shah9y ago
@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
+1
Asma Khalid9y ago
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
+1
Nitin Sontakke9y ago
Sure why not?

The classical example is employee table with manager Id in the same table.

Looks much like an Interview question to me!
+2