CODEWITHSUNDEEP
sqlsql-server
user6229886
user6229886

Reputation:

Can a value be a primary and a foreign key?

I have the following tables in my SQL Server database.

Customer is a subtype of User thus I made a reference from Customer to User.

I also want to link my customer to my reservation. I'd like to use the Foreign key in the Customer table as a PK for this relation. When I write this out in SQL Server, I get this error under "(userid)":

Invalid column 'userid'

How can I make this relation?

Create table [dbo].[User]
(
    [id] int PRIMARY KEY IDENTITY (1,1) NOT NULL,
    [name] varchar(50) NOT NULL,
    [password] nvarchar(100) NOT NULL, 
)

Create table [dbo].[Customer]
(
    [userid] int FOREIGN KEY REFERENCES [dbo].[User](id) NOT NULL,
    [street] varchar(40) NOT NULL,
    [housenumber] varchar(10) NOT NULL,
    [postalcode] varchar(10) NOT NULL,
    [phonenumber] varchar(20) NOT NULL,
    [email] varchar(30) NOT NULL,
)

Create table [dbo].[Reservation]
(
    [id] int PRIMARY KEY IDENTITY (1,1) NOT NULL,
    [datum] date NOT NULL,
    [prijs] decimal NOT NULL,
    [levertijd] datetime NOT NULL,
    [ophaaltijd] datetime NOT NULL,
    [leverAdres] varchar(60) NOT NULL,
    [klantId] int FOREIGN KEY REFERENCES [dbo].[Customer](userid) NOT NULL
)

Upvotes: 3

Views: 69

Answers (1)

GuidoG
GuidoG

Reputation: 12059

yes this is possible, try it like this

Create table [dbo].[Customer]
(
  [userid] int not null,
  [street] varchar(40) NOT NULL,
  [housenumber] varchar(10) NOT NULL,
  [postalcode] varchar(10) NOT NULL,
  [phonenumber] varchar(20) NOT NULL,
  [email] varchar(30) NOT NULL,

  constraint PK_UserID primary key ([userid]),
  constraint FK_Customer_User foreign key (userid) references [User] (id)
)

But I have to say that userid seems like an odd primary key for a table called Customer

I would so something like this :

Create table [dbo].[Customer]
(
  [CustomerID] int not null identity,
  [userid] int not null,
  [street] varchar(40) NOT NULL,
  [housenumber] varchar(10) NOT NULL,
  [postalcode] varchar(10) NOT NULL,
  [phonenumber] varchar(20) NOT NULL,
  [email] varchar(30) NOT NULL,

  constraint PK_CustomerID primary key ([CustomerID]),
  constraint FK_Customer_User foreign key (userid) references [User] (id)
)


Create table [dbo].[Reservation]
(
  [id] int PRIMARY KEY IDENTITY (1,1) NOT NULL,
  [datum] date NOT NULL,
  [prijs] decimal NOT NULL,
  [levertijd] datetime NOT NULL,
  [ophaaltijd] datetime NOT NULL,
  [leverAdres] varchar(60) NOT NULL,
  [klantId] int FOREIGN KEY REFERENCES [dbo].[Customer](customerid) NOT NULL

)

Upvotes: 0

Related Questions