CODEWITHSUNDEEP
sql-servert-sql
DirtyCode
DirtyCode

Reputation: 23

How to make a single key that is both primary and foreign in t-sql

The code is what I tried previously the 

create table DroppedPatients
(
    PatientKey nchar(15) not null,
    primary key (PatientKey),
    PatientKey nchar(15) not null references Patient(PatientKey)
)

However, I don't know how to make a single key as shown in the picture?

enter image description here

I am very new to SQL so I haven't tried much except for the normal reference / foreign key

Upvotes: 1

Views: 46

Answers (2)

Zohar Peled
Zohar Peled

Reputation: 82474

You have two mistakes in the code you've posted.

The first one is that you've specified the PatientKey column twice - basically that's telling SQL Server that you are attempting to create a table with two columns that have the same name - naturally that's impossible to do.

Your second mistake is syntactical - you're missing some keywords.

Here's a revised version of your code:

CREATE TABLE DroppedPatients
(
    PatientKey NCHAR(15) NOT NULL,
    DateDropped DATETIME2 NOT NULL CONSTRAINT DF_DroppedPatients_DateDropped DEFAULT SYSDATETIME(),
    ReasonDropped NVARCHAR(200) NULL,
    CONSTRAINT PK_DroppedPatients PRIMARY KEY (PatientKey),
    CONSTRAINT FK_DroppedPatients_Patient FOREIGN KEY (PatientKey) REFERENCES Patient(PatientKey)
)

Please note it's best practice to always name your constraints.

Upvotes: 3

Mukesh Arora
Mukesh Arora

Reputation: 1813

Yes it is possible - try below query to get the desired result -

create table DroppedPatients
(
    PatientKey nchar(15) not null  primary key  foreign key  references Patient(PatientKey)
)

Upvotes: 0

Related Questions