Re: Check Constraints using a subquery - SSrvr 2000

Tech-Archive recommends: Fix windows errors by optimizing your registry

From: Dan Guzman (guzmanda_at_nospam-online.sbcglobal.net)
Date: 12/21/04 

Date: Tue, 21 Dec 2004 07:48:27 -0600

Tibor is correct as to the cause of the error message. Foreign keys
typically refer to the primary key of the referenced table, although unique
constraint or unique index references are also allowed.

Assuming your PriceList columns RegionID and ProductTypeID do not allow
nulls (a primary key requirement) you can add the PK constraint with the
script below.

ALTER TABLE SiteEquipment
ADD CONSTRAINT PK_PriceList
PRIMARY KEY (RegionID, ProductTypeID)

In a relational database, all tables should have a primary key. Additional
unique constraints can be added to ensure non-PK data are unique. 

-- 
Hope this helps.
Dan Guzman
SQL Server MVP
"L Mehl" <mehlSCRATCHTHIS@cyvest.com> wrote in message 
news:KINxd.5834$RH4.3126@newsread1.news.pas.earthlink.net...
> Hi Dan --
>
> Thanks for responding and for the educational note.
>
> Executing your code in QueryAnalyzer, I receive the error messages
>
> Server: Msg 1776, Level 16, State 1, Line 1
> There are no primary or candidate keys in the referenced table 'PriceList'
> that match the referencing column list in the foreign key
> 'FK_SiteEq_PriceList'.
>
> Server: Msg 1750, Level 16, State 1, Line 1
> Could not create constraint. See previous errors.
>
> Creating a small test I saw that non-matching names is not what the error
> refers to.
>
> Can you suggest what to look for to solve the problem?
>
> Larry
>
>
> Dan Guzman wrote:
>> One usually uses a foreign key constraint to enforce a required 
>> relationship between 2 tables like the example below.
>>
>> ALTER TABLE SiteEquipment
>> ADD CONSTRAINT FK_SiteEquipment_Pricelist
>> FOREIGN KEY (RegionID, EqTypeID)
>> REFERENCES PriceList (RegionID, ProductTypeID)
>>

Relevant Pages

  • Re: New to SQL server
    ... [CONSTRAINT constraint_name] ... | [FOREIGN KEY] ... Is the name of the database in which the table is created. ... REFERENCES permission on the type is ...
    (microsoft.public.access.adp.sqlserver)
  • Re: Create Table Issues
    ... unique index on BookID. ... > CONSTRAINT PubPK PRIMARY KEY ... > CONSTRAINT PubFK FOREIGN KEY REFERENCES Publisher ...
    (microsoft.public.access.gettingstarted)
  • Re: Foreign Key Problem errno 150
    ... CONSTRAINT `FK_tblstammblatt_aufsteck` FOREIGN KEY REFERENCES ... So interpretiere ich die Anleitung. ...
    (de.comp.datenbanken.mysql)
  • Problem merging two queries
    ... CONSTRAINT PRIMARY KEY CLUSTERED ... REFERENCES ( ... CONSTRAINT FOREIGN KEY ...
    (microsoft.public.sqlserver.programming)
  • Re: Modelling Disjoint Subtypes
    ... the only constraints one should have are foreign key references. ... I think the answer is that a foreign key constraint enforces a constraint between columns in two tables. ... these tables also have a primary key K which references K as a foreign key. ...
    (comp.databases.theory)