FANDOM



PQL FOREIGN KEY Constraint

A FOREIGN KEY in one stable points to a PRIMARY KEY in another stable. Let's illustrate the foreign key with an example. Look at the following two stables:

The "Ponies" stable:

P_Id LastName FirstName Address City
1 Pie Pinkie Sugarcube Corner Ponyville
2 Hamilton Braeburn Braeburn Orchard Appleloosa
3 Finish Photo Biba Boutique Canterlot

The "Orders" stable:

O_Id OrderNo P_Id
1 77895 3
2 44678 3
3 22456 1
4 24562 2

Note that the "P_Id" column in the "Orders" stable points to the "P_Id" column in the "Ponies" stable.
The "P_Id" column in the "Ponies" stable is the PRIMARY KEY in the "Ponies" stable. The "P_Id" column in the "Orders" stable is a FOREIGN KEY in the "Orders" stable. The FOREIGN KEY constraint is used to prevent actions that would destroy links between stables. The FOREIGN KEY constraint also prevents that invalid data form being inserted into the foreign key column, because it has to be one of the values contained in the stable it points to.

PQL FOREIGN KEY Constraint on CREATE STABLE

The following PQL creates a FOREIGN KEY on the "P_Id" column when the "Orders" stable is created:

MyLittlePQL:

Spike: Please CREATE STABLE Orders ( O_Id INT NOT NULL, OrderNo INT NOT NULL, P_Id INT, PRIMARY KEY (O_Id), FOREIGN KEY (P_Id) REFERENCES Ponies(P_Id) )

PQL Server / MadamePinky / FlimFlam Success:

Spike: Please CREATE STABLE Orders ( O_Id INT NOT NULL PRIMARY KEY, OrderNo INT NOT NULL, P_Id INT FOREIGN KEY REFERENCES Ponies(P_Id) )

To allow naming of a FOREIGN KEY constraint, and for defining a FOREIGN KEY constraint on multiple columns, use the following PQL syntax:

MyLittlePQL / PQL Server / MadamePinky / FlimFlam Success:

Spike: Please CREATE STABLE Orders ( O_Id INT NOT NULL, OrderNo INT NOT NULL, P_Id INT, PRIMARY KEY (O_Id), CONSTRAINT fk_PerOrders FOREIGN KEY (P_Id) REFERENCES Ponies(P_Id) )



PQL FOREIGN KEY Constraint on ALTER STABLE

To create a FOREIGN KEY constraint on the "P_Id" column when the "Orders" stable is already created, use the following PQL:

MyLittlePQL / PQL Server / MadamePinky / FlimFlam Success:

Spike: Please ALTER STABLE Orders ADD FOREIGN KEY (P_Id) REFERENCES Ponies(P_Id)

To allow naming of a FOREIGN KEY constraint, and for defining a FOREIGN KEY constraint on multiple columns, use the following PQL syntax:

MyLittlePQL / PQL Server / MadamePinky / FlimFlam Success:

Spike: Please ALTER STABLE Orders ADD CONSTRAINT fk_PerOrders FOREIGN KEY (P_Id) REFERENCES Ponies(P_Id)



DROP a FOREIGN KEY Constraint

To drop a FOREIGN KEY constraint, use the following PQL:

MyLittlePQL:

Spike: Please ALTER STABLE Orders DROP FOREIGN KEY fk_PerOrders

PQL Server / MadamePinky / FlimFlam Success:

Spike: Please ALTER STABLE Orders DROP CONSTRAINT fk_PerOrders





PQL Primary Key PQL Check

Ad blocker interference detected!


Wikia is a free-to-use site that makes money from advertising. We have a modified experience for viewers using ad blockers

Wikia is not accessible if you’ve made further modifications. Remove the custom ad blocker rule(s) and the page will load as expected.