November 1, 2002 at 10:11 am
I wrote 2 scripts one to add a unique constraint on combination of columns and then I want to add foreign key on only one column from this unique constraint, can i do that ? how ?
BEGIN TRANSACTION
ALTER TABLE dbo.ETHrDepCd
DROP CONSTRAINT fk_DepCd_HRGroup
GO
ALTER TABLE dbo.ETHRGroup
DROP CONSTRAINT UK_HRGrp_Code
GO
ALTER TABLE dbo.ETHRGroup ADD CONSTRAINT
UK_HRGrp_Code UNIQUE NONCLUSTERED
(
HRGrp_Code,
HRGrp_Company
) ON [PRIMARY]
GO
COMMIT
then add Foreign key on one column
ALTER TABLE dbo.ETHrDepCd ADD CONSTRAINT
FK_ETHrDepCd_ETHRGroup FOREIGN KEY
(
DepCD_HRGRoup) REFERENCES dbo.ETHRGroup
(
HRGrp_Code
)
GO
COMMIT
but gives me errors
November 1, 2002 at 2:18 pm
You can't a foreignb key has to reference a unique record in the parent table and so must reference all columns that make the primary key or unique constraint
Simon Sabin
Co-author of SQL Server 2000 XML Distilled
http://www.amazon.co.uk/exec/obidos/ASIN/1904347088
Simon Sabin
SQL Server MVP
http://sqlblogcasts.com/blogs/simons
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply