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.
ColumnDatatypeNULLDescription
OWNERVARCHAR2(30)NOT NULLOwner of the constraint definition
CONSTRAINT_NAMEVARCHAR2(30)NOT NULLName of the constraint definition
CONSTRAINT_TYPEVARCHAR2(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_NAMEVARCHAR2(30)NOT NULLName associated with the table (or view) with constraint definition
SEARCH_CONDITIONLONGText of search condition for a check constraint
R_OWNERVARCHAR2(30)Owner of table referred to in a referential constraint
R_CONSTRAINT_NAMEVARCHAR2(30)Name of the unique constraint definition for referenced table
DELETE_RULEVARCHAR2(9)Delete rule for a referential constraint (CASCADE or NO ACTION)
STATUSVARCHAR2(8)Enforcement status of constraint (ENABLED or DISABLED)
DEFERRABLEVARCHAR2(14)Whether the constraint is deferrable
DEFERREDVARCHAR2(9)Whether the constraint was initially deferred
VALIDATEDVARCHAR2(13)Whether all data obeys the constraint (VALIDATED or NOT VALIDATED)
GENERATEDVARCHAR2(14)Whether the name of the constraint is user or system generated
BADVARCHAR2(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.
RELYVARCHAR2(4)Whether an enabled constraint is enforced or unenforced.
See Also: the constraints in Oracle Database SQL Reference
LAST_CHANGEDATEWhen the constraint was last enabled or disabled
INDEX_OWNERVARCHAR2(30)Name of the user owning the index
INDEX_NAMEVARCHAR2(30)Name of the index (only shown for unique and primary-key constraints)
INVALIDVARCHAR2(7)Whether the constraint is invalid
VIEW_RELATEDVARCHAR2(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?
 
shareedit
  
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

3 Answers


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

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

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
-----------------------------------------------------
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
   
shareedit
  
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

2 Answers


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
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

Comments

Post a Comment

Popular posts from this blog

VMware fix for Invalid manifest and ova file import failed errors

Image
Recently we got a OVA file for a virtual machine. The vendor instructions were to import the ova file in vmware Workstation, Player for Windows/Linux, Fusion for Mac, and VirtualBox as well.  The instructions were to take the available package and launch the VM with VMware workstation. The package contained  Module.mf, Module.ovf and Module-disk.vmdk and a Module.ova file. The .mf and .ovf file were 2 KB each whereas the vmdk was several gigs. The package also contained a Module.ova file which was several gigs as well. OVF           Open Virtualization Format MF             Manifest file VMDK       Virtual Machine Disk OVA           Open Virtualization Appliance The ovf file is a xml file that contains metadata for the ovf package The mf file contains the SHA1 hash codes of all files in the package The vmdk file is the disk image of the virtual mac...
Read more

Session Timeout in Oracle Access Manager

Image
Session Timeout in Oracle Access Manager (OAM) The session lifetime and Idle timeout entries control how long a user's session is valid. In OAM, a user session is an object which represents an authenticated user. This object is stored in the server memory and if Database session persistence is enabled, this object is stored or available in the database. Each session is unique and is identified by both a userid and Session ID (session identifier), see screenshot below "OAM User Session Management".  A valid session means a user has been authenticated with OAM. A session can have following three states: 1) active 2) inactive 3) expired When a session is created it is in active state and is available as an object in the OAM. After a set time - idle timeout, the session moves or transitions to inactive state. And finally after the Session Lifetime time, the session is marked as expired. Expired sessions are removed from server memory. Read more about Session Lifetime ...
Read more

SOAPUI - import certificate

Image
Note: SoapUI has two versions, one is open source and second Professional version. The open source can be download here . (confirmed link 12/19/2018). SSL Handshake issue:   There is an Issue in SoapUI version 5.3.0 (and 5.2.0 version) with SSL handshake error. It was resolved by updating below in vmoptions file ( refer here ). However, the error that shows up while trying to load wsdl is "Error loading WSDL" as below The fix is to Enable TLS 1.2 protocol for SOAP/REST calls in SoapUI, by ammending the vmoptions file to add the directive for TLS as (-Dsoapui.https.protocols=TLSv1.2). Refer here . Update: Version 5.5.0 does not have this issue. If you are on 5.3.0 better upgrade to 5.5.0 which is available now (Feb 2019). I had above issue as well as another issue reaching to https endpoint. Upgrade to 5.5.0 resolved issue. Select "Check for updates" under the Help menu and you will get option for upgrade. Select upgrade current version and accept all defaul...
Read more