March 22, 2013 at 2:50 am
HI,
I am creating a table GV_ReceivedOffice with this strucutre:
But VoucherNo column in this table will reference to one more table GV_Voucher which has column VoucherNo. VoucherNo in GV_Voucher doesnt have any constaint on it.
so If i try do in this way to reference VoucherNo in ReceivedOfficetable
CONSTRAINT FK_GV_ReceivedOffice_VoucherNo Foreign KEY (VoucherNO) REFERENCES GV_Voucher(VoucherNo)
it is not correct. How to do this?
Create table GV_ReceivedOffice
(
ReceivedOfficeID int primary key identity(1,1),
VoucherNo varchar(15),
TransactionID varchar(10),
UserID int,
ReceivedDate datetime
)
_______________________________________________________________
To get quick answer follow this link:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
March 22, 2013 at 3:06 am
table structure of gv_voucher if anyone required:
CREATE TABLE [dbo].[GV_Voucher](
[VoucherId] [int] IDENTITY(1,1) NOT NULL,
[VoucherTypeId] [int] NOT NULL,
[VoucherNo] [varchar](20) NOT NULL,
[Denomination] [int] NOT NULL,
[ExpiryDate] [datetime] NULL,
[CreatedDate] [datetime] NULL,
[ModifyDate] [datetime] NULL,
[UserId] [varchar](10) NULL,
[VoucherStatusId] [int] NOT NULL,
[TransactionID] [varchar](20) NOT NULL,
[Quantity] [int] NOT NULL,
[AmountValue] [int] NULL,
CONSTRAINT [PK_GV_Voucher] PRIMARY KEY CLUSTERED
(
[VoucherId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
ALTER TABLE [dbo].[GV_Voucher] WITH CHECK ADD CONSTRAINT [FK_GV_Voucher_GV_VoucherStatus] FOREIGN KEY([VoucherStatusId])
REFERENCES [dbo].[GV_VoucherStatus] ([VoucherStatusId])
GO
ALTER TABLE [dbo].[GV_Voucher] CHECK CONSTRAINT [FK_GV_Voucher_GV_VoucherStatus]
GO
ALTER TABLE [dbo].[GV_Voucher] WITH CHECK ADD CONSTRAINT [FK_GV_Voucher_GV_VoucherType] FOREIGN KEY([VoucherTypeId])
REFERENCES [dbo].[GV_VoucherType] ([VoucherTypeID])
GO
ALTER TABLE [dbo].[GV_Voucher] CHECK CONSTRAINT [FK_GV_Voucher_GV_VoucherType]
GO
_______________________________________________________________
To get quick answer follow this link:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
March 22, 2013 at 6:58 am
You will have to make VoucherNo a UNIQUE KEY in GV_Voucher table.
Only then you will be able to create the desired FOREIGN KEY constraint.
How to post data/code on a forum to get the best help - Jeff Moden
http://www.sqlservercentral.com/articles/Best+Practices/61537/
March 22, 2013 at 7:13 am
Kingston Dhasian (3/22/2013)
You will have to make VoucherNo a UNIQUE KEY in GV_Voucher table.Only then you will be able to create the desired FOREIGN KEY constraint.
Thanks,
I have implemented it
_______________________________________________________________
To get quick answer follow this link:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply