Good day gentlemen, i dont know if any one has a work around this. i have a table A that has two columns (Col1 & Col2)that references another column (Col3) in table B. I created foreign key relationships from col3 in table B to col1 & col2 in table A but it did not allow me to put cascading updates & deletes. So i tried using check constraints and i came up with this query which still has a problem cos SubQueries are not supported in Check constraintsCREATE TABLE [Institution_Programme] ( [ProgrammeID] [int] IDENTITY (1, 1) NOT NULL , [ProgrammeTypeID] [int] NOT NULL , [DepartmentID] [int] NOT NULL , [CertificateID] [int] NOT NULL , [ProgrammeCode] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL , [ProgrammeName] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL , [StartLevel] [varchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , [EndLevel] [varchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , [Duration] [varchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , [UnitsRequired] [int] NULL , [Status] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL CONSTRAINT [DF_Institution_Programme_Status] DEFAULT (0), CONSTRAINT [PK_Institution_Programme] PRIMARY KEY CLUSTERED ([ProgrammeID]) ON [PRIMARY], CONSTRAINT [FK_Institution_Programme_Institution_Certificate] FOREIGN KEY ([CertificateID]) REFERENCES [Institution_Certificate] ([CertificateID]) ON DELETE CASCADE ON UPDATE CASCADE, CONSTRAINT [FK_Institution_Programme_Institution_Department] FOREIGN KEY ([DepartmentID]) REFERENCES [Institution_Department] ([DepartmentID]) ON DELETE CASCADE ON UPDATE CASCADE , CONSTRAINT [CK_Institution_Programme_Institution_Level] CHECK ([StartLevel] IN (SELECT LevelCode from Institution_Level)), CONSTRAINT [CK_Institution_Programme_Institution_Level1] CHECK ([EndLevel] IN (SELECT LevelCode from Institution_Level)), CONSTRAINT [FK_Institution_Programme_Institution_ProgrammeType] FOREIGN KEY ([ProgrammeTypeID]) REFERENCES [Institution_ProgrammeType] ([ProgrammeTypeID]) ON DELETE CASCADE ON UPDATE CASCADE ) ON [PRIMARY]
This is the structure of the other table(that is supposed to be table B)CREATE TABLE [Institution_Level] ( [LevelID] [int] IDENTITY (1, 1) NOT NULL , [LevelCode] [varchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL , [LevelName] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , [Status] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL CONSTRAINT [DF_Institution_Level_Status] DEFAULT (0), CONSTRAINT [PK_Institution_Level] PRIMARY KEY CLUSTERED ( [LevelID] ) ON [PRIMARY] , CONSTRAINT [IX_Institution_Level] UNIQUE NONCLUSTERED ( [LevelCode] ) ON [PRIMARY] ) ON [PRIMARY]
. Am only trying to maintain referential integrity here. Does any one know a work around this, or do i have to stick to the only viable option available to me right now:"Preserve the referential integrity from the codes in my pages"?
Subqueries in Check Constraints
Posted - 2006-04-04 : 11:08:16
