The LIST command is used to display information about a database. Additional parameters to display specific constraint information include:
•LIST CONSTRAINTS
•LIST PKEYS
•LIST FKEYS
•LIST UKEYS
•LIST INDEXES
•LIST CASCADE
LIST CONSTRAINTS shows primary key, foreign key, unique key, and Not NULL constraints. The constraint ID, the type of constraint and if it is referenced, the table name, and table references if the key was a foreign key are displayed. Unique indexes are listed next with the index name, table and column names. Not null constraints are listed last with the table and column names.
R>LIST CONSTRAINTS
Id Type Table Name References
------ --------------------------- ----------------- --------------
#31 PRIMARY KEY REFERENCED Customer
#33 PRIMARY KEY REFERENCED Component
#41 FOREIGN KEY SalesBonus Employee
#43 FOREIGN KEY CompUsed Component
#42 FOREIGN KEY CompUsed Product
#44 FOREIGN KEY ProdLocation Product
#45 FOREIGN KEY Levels Product
#34 PRIMARY KEY Levels
#35 PRIMARY KEY REFERENCED Product
#47 FOREIGN KEY InvoiceHeader Customer
#46 FOREIGN KEY InvoiceHeader Employee
#36 PRIMARY KEY REFERENCED InvoiceHeader
#49 FOREIGN KEY InvoiceDetail Product
#48 FOREIGN KEY InvoiceDetail InvoiceHeader
#51 FOREIGN KEY ContactCallNotes Contact
#50 FOREIGN KEY ContactCallNotes Employee
#52 FOREIGN KEY Contact Customer
#37 PRIMARY KEY REFERENCED Contact
#38 PRIMARY KEY REFERENCED Titles
#53 FOREIGN KEY Employee Titles
#39 PRIMARY KEY REFERENCED Employee
#40 PRIMARY KEY StateAbr
Unique Indexes:
Index Name Table Name Column Name
------------------ -------------------- ---------------------------
UI_Comp Component CompDesc
NOT NULL Constraints:
Table Name Column Name
------------------ ------------------
Customer CustID
Component CompID
ProdLocation OnHand
Levels ModLevel
Model
Product Model
InvoiceHeader TransID
ContactCallNotes CallTime
Contact ContID
Titles EmpTID
Employee EmpID
StateAbr State
LIST PKEYS shows only primary keys. The constraint ID, the type of constraint and if it is referenced, and the table name.
R>LIST PKEYS
Id Type Table Name References
------ --------------------------- ------------------ ------------
#31 PRIMARY KEY REFERENCED Customer
#33 PRIMARY KEY REFERENCED Component
#34 PRIMARY KEY Levels
#35 PRIMARY KEY REFERENCED Product
#36 PRIMARY KEY REFERENCED InvoiceHeader
#37 PRIMARY KEY REFERENCED Contact
#38 PRIMARY KEY REFERENCED Titles
#39 PRIMARY KEY REFERENCED Employee
#40 PRIMARY KEY StateAbr
LIST FKEYS shows only foreign keys. The constraint ID, the type of constraint, the table name, and table references are displayed. LIST UKEYS displays similar results.
R>LIST FKEYS
Id Type Table Name References
------ ------------------------- ------------------ ---------------
#41 FOREIGN KEY SalesBonus Employee
#43 FOREIGN KEY CompUsed Component
#42 FOREIGN KEY CompUsed Product
#44 FOREIGN KEY ProdLocation Product
#45 FOREIGN KEY Levels Product
#47 FOREIGN KEY InvoiceHeader Customer
#46 FOREIGN KEY InvoiceHeader Employee
#49 FOREIGN KEY InvoiceDetail Product
#48 FOREIGN KEY InvoiceDetail InvoiceHeader
#51 FOREIGN KEY ContactCallNotes Contact
#50 FOREIGN KEY ContactCallNotes Employee
#52 FOREIGN KEY Contact Customer
#53 FOREIGN KEY Employee Titles
LIST INDEXES will display all database indexes, and notes if a unique index is defined with (U) to the left of the index name.
R>LIST INDEXES
Number of Indexes in Database RRBYW20 is 54.
Index Name Table Name Column Name
-------------------- ------------------- --------------------
CustState Customer CustState
(U)UI_Comp Component CompDesc
LIST CASCADE displays tables with CASCADE, and whether UPDATE, DELETE, or BOTH is enabled.
R>LIST CASCADE
Tables with CASCADE flag in the Database RRBYW20
Name Cascade Type
----------------- ---------------------------------
Customer CASCADE BOTH
Employee CASCADE BOTH
Titles CASCADE BOTH
The LIST command can also be used to display constraints for a single table. When specifying an individual table, the referenced column names are also displayed.
R>LIST CONSTRAINTS FOR Employee
Table Name: Employee
Id Type Column Name(s) Ref Table Name Ref Column Name(s)
------ ------------- --------------- --------------- ------------------
#53 FOREIGN KEY EmpTID Titles EmpTID
#39 PRIMARY KEY EmpID
NOT NULL Constraints:
Table Name Column Name
------------------ ------------------
Employee EmpID
R>LIST FKEYS FOR InvoiceHeader
Table Name: InvoiceHeader
Id Type Column Name(s) Ref Table Name Ref Column Name(s)
------ -------------- --------------- --------------- ------------------
#47 FOREIGN KEY CustID Customer CustID
#46 FOREIGN KEY EmpID Employee EmpID
