Is it possible to create referring column / foreign keys in same table-CodePudding

I hope I explain this as clear as needed. I would like to create a column referring to a different column in the same table, while both these columns aren't PK's and the first(existing column) isn't a FK yet, just a column. The second one does not exist. The table already exist and has data.

Existing situation example:
TABLE Equipment
PK 'EquipmentID'
Column 'External ID' (nvarchar(50) null)

Desired situation example:
TABLE Equipment
PK EquipmentID
Column 'ExternalID' (nvarchar(50) null)
Column 'Partof 'ExternalID''

Thanks in advance!

Literal example: Equipment ID : 1
Equipment Description : washing machine
External ID : ABC

Equipment ID : 2
Equipment Description : Pump
External ID : NULL
PartofExternalID : ABC

CodePudding user response：

From what I get, you want a foreign key referencing a unique column in the table itself.

CREATE TABLE equipment
             (equipmentid integer,
              externalid nvarchar(50),
              partof nvarchar(50),
              PRIMARY KEY (equipmentid),
              UNIQUE (externalid),
              FOREIGN KEY (partof)
                          REFERENCES equipment
                                     (externalid));

Though I wonder why you want to do that via that "external" ID? Consider just using the "internal" one.

CodePudding user response：

1- To set an existing column as PK, it's data must be unique and not null, so in table designer you can set it as PK and save it, it's done if that condition satisfied.

2- Again, to set an existing column as referring (FK) to another column, its data must be a subset of the reference column. You can easily set the relation in designer and save it, then see the result.

CodePudding user response：

You can't refer or create foreign keys in same table -

A Foreign Key is a database key that is used to link two tables together