Search code examples
c#sqlsql-serverdatabasesql-function

How to reference a UNIQUEIDENTIFIER DEFAULT NEWID() Column


I'm trying to reference a uniqueidentifier default newid() column but its giving me an error in Line 11: There are no primary or candidate keys to the referenced table.

CREATE TABLE tblPhoneTypes (
 PhoneTypeID UNIQUEIDENTIFIER NOT NULL DEFAULT NEWID(),
 PhoneTypeDescription VARCHAR (50) NOT NULL,
 PhoneTypeIsActive BIT NOT NULL,
 UpdateDate DATETIME)
    
    
CREATE TABLE tblPhones(
PhoneID UNIQUEIDENTIFIER NOT NULL DEFAULT NEWID(),
PhoneNumber INT NOT NULL,
PhoneTypeIDFK UNIQUEIDENTIFIER NOT NULL FOREIGN KEY REFERENCES
**tblPhoneTypes**(PhoneTypeID), //ErrorLine
PhoneIsActive BIT NOT NULL,
UpdateDate DATETIME NOT NULL )

Solution

  • Just add the Primary key constraint :

    CREATE TABLE tblPhoneTypes (
     PhoneTypeID UNIQUEIDENTIFIER PRIMARY KEY DEFAULT NEWID(),
     PhoneTypeDescription VARCHAR (50) NOT NULL,
     PhoneTypeIsActive BIT NOT NULL,
     UpdateDate DATETIME)
    

    And for the second table :

    CREATE TABLE tblPhones(
    PhoneID UNIQUEIDENTIFIER PRIMARY KEY DEFAULT NEWID(),
    PhoneNumber INT NOT NULL,
    PhoneTypeID UNIQUEIDENTIFIER FOREIGN KEY REFERENCES tblPhoneTypes(PhoneTypeID),
    PhoneIsActive BIT NOT NULL,
    UpdateDate DATETIME NOT NULL )