How to find Primary keys in tables

Question: How do I determine if a table has a primary key and if it has one, how do I determine what columns are in the primary key?

Answer: You can retrieve primary key information with the following SQL statement:  (See below for reference to all_constraints table)

SELECT cols.table_name, cols.column_name, cols.position, cons.status, cons.owner
FROM all_constraints cons, all_cons_columns cols
WHERE cons.constraint_type = 'P'
AND cons.constraint_name = cols.constraint_name
AND cons.owner = cols.owner
ORDER BY cols.table_name, cols.position;

If you knew the table name that you were looking for, you could modify the SQL as follows:

SELECT cols.table_name, cols.column_name, cols.position, cons.status, cons.owner
FROM all_constraints cons, all_cons_columns cols
WHERE cols.table_name = 'TABLE_NAME'
AND cons.constraint_type = 'P'
AND cons.constraint_name = cols.constraint_name
AND cons.owner = cols.owner
ORDER BY cols.table_name, cols.position;

Make sure to type the table_name in uppercase, as Oracle stores all table names in uppercase.

Let's quickly explain the output from this query.

table_name

The name of the table (stored in uppercase).

column_name

The name of the column that is a part of the primary key. (also stored in uppercase)

position

The position in the primary key. A primary key can contain more than one column, so understanding the order of the columns in the primary key is very important.

status

Indicates whether the primary key is currently enabled or disabled.

owner

Indicates the schema that owns the table.

http://www.techonthenet.com/oracle/questions/find_pkeys.php
http://docs.oracle.com/cd/B19306_01/server.102/b14237/statviews_1037.htm#i1576022

ALL_CONSTRAINTS

ALL_CONSTRAINTS describes constraint definitions on tables accessible to the current user.

Related Views

• DBA_CONSTRAINTS describes all constraint definitions in the database.
• USER_CONSTRAINTS describes constraint definitions on tables in the current user's schema.

Column	Datatype	NULL	Description
OWNER	VARCHAR2(30)	NOT NULL	Owner of the constraint definition
CONSTRAINT_NAME	VARCHAR2(30)	NOT NULL	Name of the constraint definition
CONSTRAINT_TYPE	VARCHAR2(1)		Type of constraint definition: C (check constraint on a table) P (primary key) U (unique key) R (referential integrity) V (with check option, on a view) O (with read only, on a view)
TABLE_NAME	VARCHAR2(30)	NOT NULL	Name associated with the table (or view) with constraint definition
SEARCH_CONDITION	LONG		Text of search condition for a check constraint
R_OWNER	VARCHAR2(30)		Owner of table referred to in a referential constraint
R_CONSTRAINT_NAME	VARCHAR2(30)		Name of the unique constraint definition for referenced table
DELETE_RULE	VARCHAR2(9)		Delete rule for a referential constraint (CASCADE or NO ACTION)
STATUS	VARCHAR2(8)		Enforcement status of constraint (ENABLED or DISABLED)
DEFERRABLE	VARCHAR2(14)		Whether the constraint is deferrable
DEFERRED	VARCHAR2(9)		Whether the constraint was initially deferred
VALIDATED	VARCHAR2(13)		Whether all data obeys the constraint (VALIDATED or NOT VALIDATED)
GENERATED	VARCHAR2(14)		Whether the name of the constraint is user or system generated
BAD	VARCHAR2(3)		A YES value indicates that this constraint specifies a century in an ambiguous manner. To avoid errors resulting from this ambiguity, rewrite the constraint using the TO_DATEfunction with a four-digit year. See Also: the TO_DATE function in Oracle Database SQL Reference and Oracle Database Application Developer's Guide - Fundamentals
RELY	VARCHAR2(4)		Whether an enabled constraint is enforced or unenforced. See Also: the constraints in Oracle Database SQL Reference
LAST_CHANGE	DATE		When the constraint was last enabled or disabled
INDEX_OWNER	VARCHAR2(30)		Name of the user owning the index
INDEX_NAME	VARCHAR2(30)		Name of the index (only shown for unique and primary-key constraints)
INVALID	VARCHAR2(7)		Whether the constraint is invalid
VIEW_RELATED	VARCHAR2(14)		Whether the constraint depends on a view

Can one reference non primary key as a foreign key

http://stackoverflow.com/questions/18435065/foreign-key-to-non-primary-key

Foreign Key to Non Primary key

I have a table which holds data, and one of those rows needs to exist in another table. So, I want a foreign key to maintain referential integrity. CREATE TABLE table1 ( ID INT NOT NULL IDENTITY(1,1) PRIMARY KEY, AnotherID INT NOT NULL, SomeData VARCHAR(100) NOT NULL ) CREATE TABLE table2 ( ID INT NOT NULL IDENTITY(1,1) PRIMARY KEY, AnotherID INT NOT NULL, MoreData VARCHAR(30) NOT NULL, CONSTRAINT fk_table2_table1 FOREIGN KEY (AnotherID) REFERENCES table1 (AnotherID) ) However, as you can see, the table I foreign key to, the column isn't the PK. Is there a way to create this foreign key, or maybe a better way to maintain this referential integrity? sql sql-server shareedit asked Aug 26 '13 at 0:26 Craig 3,124124494

It makes not much sense to do that. Why not refer to table1.ID? –  zerkms Aug 26 '13 at 0:28 it is definitive that if your AnothidID is not a primary key it should be a ForeignKey, so being a ForeignKey, your table2 should points to the same table (possible table3) –  Rogerio Barretto Aug 26 '13 at 0:49 add a comment

3 Answers

activeoldestvotes

up vote25down voteaccepted

If you really want to create a foreign key to a non-primary key, it MUST be a column that has a unique constraint on it. From Books Online: A FOREIGN KEY constraint does not have to be linked only to a PRIMARY KEY constraint in another table; it can also be defined to reference the columns of a UNIQUE constraint in another table. So in your case if you make AnotherID unique, it will be allowed. If you can't apply a unique constraint you're out of luck, but this really does make sense if you think about it. Although, as has been mentioned, if you have a perfectly good primary key as a candidate key, why not use that? shareedit answered Aug 26 '13 at 0:36 Ian Preston 20k32637

add a comment

up vote1down vote

As others have pointed out, ideally, the foreign key would be created as a reference to a primary key (usually an IDENTITY column). However, we don't live in an ideal world, and sometimes even a "small" change to a schema can have significant ripple effects to the application logic. Consider the case of a Customer table with a SSN column (and a dumb primary key), and a Claim table that also contains a SSN column (populated by business logic from the Customer data, but no FK exists). The design is flawed, but has been in use for several years, and three different applications have been built on the schema. It should be obvious that ripping out Claim.SSN and putting in a real PK-FK relationship would be ideal, but would also be a significant overhaul. On the other hand, putting a UNIQUE constraint on Customer.SSN, and adding a FK on Claim.SSN, could provide referential integrity, with little or no impact on the applications. Don't get me wrong, I'm all for normalization, but sometimes pragmatism wins over idealism. If a mediocre design can be helped with a band-aid, surgery might be avoided. shareedit answered May 15 '14 at 20:47 EJSawyer 112

add a comment

up vote0down vote

Necromancing. I assume when somebody lands here, he needs a foreign key to column in a table that contains non-unique keys. The problem is, that if you have that problem, the database-schema is denormalized.  You're for example keeping rooms in a table, with a room-uid primary key, a DateFrom and a DateTo field, and another uid, here RM_ApertureID to keep track of the same room, and a soft-delete field, like RM_Status, where 99 means 'deleted', and <> 99 means 'active'. So when you create the first room, you insert RM_UID and RM_ApertureID as the same value as RM_UID. Then, when you terminate the room to a date, and re-establish it with a new date range, RM_UID is newid(), and the RM_ApertureID from the previous entry becomes the new RM_ApertureID. So, if that's the case, RM_ApertureID is a non-unique field, and so you can't set a foreign-key in another table. And there is no way to set a foreign key to a non-unique column/index, e.g. in T_ZO_REM_AP_Raum_Reinigung (WHERE RM_UID is actually RM_ApertureID). But to prohibit invalid values, you need to set a foreign key, otherwise, data-garbage is the result sooner rather than later... Now what you can do in this case (short of rewritting the entire application) is inserting a CHECK-constraint, with a scalar function checking the presence of the key: IF EXISTS (SELECT * FROM sys.check_constraints WHERE object_id = OBJECT_ID(N'[dbo].[Check_RM_ApertureIDisValid_T_ZO_REM_AP_Raum_Reinigung]') AND parent_object_id = OBJECT_ID(N'[dbo].[T_ZO_REM_AP_Raum_Reinigung]')) ALTER TABLE dbo.T_ZO_REM_AP_Raum_Reinigung DROP CONSTRAINT [Check_RM_ApertureIDisValid_T_ZO_REM_AP_Raum_Reinigung] GO IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[fu_Constaint_ValidRmApertureId]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT')) DROP FUNCTION [dbo].[fu_Constaint_ValidRmApertureId] GO CREATE FUNCTION [dbo].[fu_Constaint_ValidRmApertureId]( @in_RM_ApertureID uniqueidentifier ,@in_DatumVon AS datetime ,@in_DatumBis AS datetime ,@in_Status AS integer ) RETURNS bit AS BEGIN DECLARE @bNoCheckForThisCustomer AS bit DECLARE @bIsInvalidValue AS bit SET @bNoCheckForThisCustomer = 'false' SET @bIsInvalidValue = 'false' IF @in_Status = 99 RETURN 'false' IF @in_DatumVon > @in_DatumBis BEGIN RETURN 'true' END IF @bNoCheckForThisCustomer = 'true' RETURN @bIsInvalidValue IF NOT EXISTS ( SELECT T_Raum.RM_UID ,T_Raum.RM_Status ,T_Raum.RM_DatumVon ,T_Raum.RM_DatumBis ,T_Raum.RM_ApertureID FROM T_Raum WHERE (1=1) AND T_Raum.RM_ApertureID = @in_RM_ApertureID AND @in_DatumVon >= T_Raum.RM_DatumVon AND @in_DatumBis <= T_Raum.RM_DatumBis AND T_Raum.RM_Status <> 99 ) SET @bIsInvalidValue = 'true' -- IF ! RETURN @bIsInvalidValue END GO IF EXISTS (SELECT * FROM sys.check_constraints WHERE object_id = OBJECT_ID(N'[dbo].[Check_RM_ApertureIDisValid_T_ZO_REM_AP_Raum_Reinigung]') AND parent_object_id = OBJECT_ID(N'[dbo].[T_ZO_REM_AP_Raum_Reinigung]')) ALTER TABLE dbo.T_ZO_REM_AP_Raum_Reinigung DROP CONSTRAINT [Check_RM_ApertureIDisValid_T_ZO_REM_AP_Raum_Reinigung] GO -- ALTER TABLE dbo.T_AP_Kontakte WITH CHECK ADD CONSTRAINT [Check_RM_ApertureIDisValid_T_ZO_REM_AP_Raum_Reinigung] ALTER TABLE dbo.T_ZO_REM_AP_Raum_Reinigung WITH NOCHECK ADD CONSTRAINT [Check_RM_ApertureIDisValid_T_ZO_REM_AP_Raum_Reinigung] CHECK ( NOT ( dbo.fu_Constaint_ValidRmApertureId(ZO_RMREM_RM_UID, ZO_RMREM_GueltigVon, ZO_RMREM_GueltigBis, ZO_RMREM_Status) = 1 ) ) GO IF EXISTS (SELECT * FROM sys.check_constraints WHERE object_id = OBJECT_ID(N'[dbo].[Check_RM_ApertureIDisValid_T_ZO_REM_AP_Raum_Reinigung]') AND parent_object_id = OBJECT_ID(N'[dbo].[T_ZO_REM_AP_Raum_Reinigung]')) ALTER TABLE dbo.T_ZO_REM_AP_Raum_Reinigung CHECK CONSTRAINT [Check_RM_ApertureIDisValid_T_ZO_REM_AP_Raum_Reinigung] GO shareedit answered Oct 17 '14 at 15:03

-----------------------------------------------------

t is written in every book that foreign keys are actually primary key in some other table but can we have a foreign key which is not primary key in any other table sql sql-server database-design foreign-keys shareedit edited Sep 7 '10 at 4:41 APC 76.8k962150 asked May 24 '10 at 6:42 Mac 1,27452051

Why would you want to? –  JeffO May 24 '10 at 12:17 there is no specific reason somebody asked me this and i got confused thats why i just wanted to know that's it –  Mac May 24 '10 at 12:22 Why? Well that depends on what your reasons are for choosing a primary key in the first place - formally speaking there is no absolute reason for choosing one primary key over another so there are no particular grounds for saying why it would or wouldn't be the same one used for foreign key references. Also, it's potentially desirable in some cases to use more than one candidate key per table as a foreign key reference. In that case, one of those references obviously isn't the primary key - unless you want to call more than one key the "primary" one! –  sqlvogel May 24 '10 at 12:34  thanks david i get it –  Mac May 24 '10 at 12:49 add a comment

2 Answers

activeoldestvotes

up vote24down voteaccepted

Yes - you can have a foreign key that references a unique index in another table. CREATE UNIQUE INDEX UX01_YourTable ON dbo.YourTable(SomeUniqueColumn) ALTER TABLE dbo.YourChildTable ADD CONSTRAINT FK_ChildTable_Table FOREIGN KEY(YourFKColumn) REFERENCES dbo.YourTable(SomeUniqueColumn) shareedit answered May 24 '10 at 6:52 marc_s 371k65659847

2 This is correct in Microsoft SQL Server but it isn't stanard SQL and isn't supported by all other DBMSs. In ISO standard SQL there is no such thing as an index and FOREIGN KEY constraints are always required to match UNIQUE or PRIMARY KEY constraints. Personally I'd always prefer to use unique constraints rather than unique indexes without a constraint. I think a constraint makes the intended meaning clearer and the constraint syntax is more likely to be understood by another database developer. –  sqlvogel May 24 '10 at 8:51 3 @David: yes, but the OP was asking about SQL Server..... –  marc_s May 24 '10 at 8:59