locked
Column reference to another table column RRS feed

  • Question

  • Hi,

    I have a table Gv_voucher which has data like(VoucherId, VoucherNo, TranactionID)

    1, VVB0001, TRN001

    2,VVB0002, TRN001

    3,VVB003,TRN002

    4,VVB0004, TRN002

    I have created another table GV_ReceivedOffice in which I required these column VoucherNo and TransactionID

    I have created a unique constraint on column VoucherNO in GV_Vocuher table so that I can use it in my another table Gv_received.

    But how to use column TransactionID of Gv_Voucher table in GV_ReceivedOffice. Is it so that without creating constraint we cant reference a column  to another table's column?

    Please suggest.


    Please Mark as Answer if my post works for you or Vote as Helpful if it helps you. Kapil Singh

    • Moved by Kalman Toth Friday, March 22, 2013 1:58 PM Not t-sql
    Friday, March 22, 2013 9:35 AM

Answers

  • It is not clear to me what you want to do. But I think the answer is: don't.

    It is not clear to me how GV_ReceivedOffice relates to GV_voucher, but if TransactionID depends on the voucher number, this should be encoded in a single table. Else you redundancy which cause data consistency problems if you mess up.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    • Marked as answer by Kalman Toth Saturday, March 30, 2013 12:54 PM
    Friday, March 22, 2013 11:13 AM

All replies

  • You can FOREIGN KEY reference:

    1. PRIMARY KEY

    2. UNIQUE KEY

    3. unique index column

    in another table.

    Nonetheless, you should use option 1 only. Option 2 & 3 will create confusion.

    Once you have JOIN keys (PK & FK), you can just JOIN the tables and access any columns in both:

    http://www.sqlusa.com/bestpractices/fourtablejoin/

    Blog:

    http://www.sqlusa.com/bestpractices2008/identity-pk-fk-columns/

    The simplest way to JOIN tables is INT IDENTITY(1,1) SURROGATE PRIMARY KEY.

    For quick assistance post DDL (CREATE TABLEs).


    Kalman Toth Database & OLAP Architect sqlusa.com
    Paperback / Kindle: Pass SQL Exam 70-461 & Job Interview: Programming SQL Server 2012





    Friday, March 22, 2013 9:40 AM
  • Hi,

    One more option is Triggers.

    Create After Trigger in GV_ReceivedOffice for (Insert and delete statements)


    Please vote if you find this posting was helpful or Mark it as answered.

    Friday, March 22, 2013 10:25 AM
  • Hi,

    One more option is Triggers.

    Create After Trigger in GV_ReceivedOffice for (Insert and delete statements)


    Please vote if you find this posting was helpful or Mark it as answered.

    You want to say that when I insert voucherno in this table then TransactionId related to that VoucherNo should get inserted into Gv_receivedOffice table with the help of trigger...

    Is it good way for this or there is nay alternate solution for this?


    Please Mark as Answer if my post works for you or Vote as Helpful if it helps you. Kapil Singh

    Friday, March 22, 2013 10:45 AM
  • It is not clear to me what you want to do. But I think the answer is: don't.

    It is not clear to me how GV_ReceivedOffice relates to GV_voucher, but if TransactionID depends on the voucher number, this should be encoded in a single table. Else you redundancy which cause data consistency problems if you mess up.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    • Marked as answer by Kalman Toth Saturday, March 30, 2013 12:54 PM
    Friday, March 22, 2013 11:13 AM
  • It is not clear to me what you want to do. But I think the answer is: don't.

    It is not clear to me how GV_ReceivedOffice relates to GV_voucher, but if TransactionID depends on the voucher number, this should be encoded in a single table. Else you redundancy which cause data consistency problems if you mess up.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    Erland.

    I am storing VouherNo and TransactionID from GV_Voucher table into GV_ReceivedOffice.

    As VoucherNo is unique so I have created unique constraint on that and used a reference in GV_receivedOffice, But as for multiple VoucherNo there is simgle transaction so I am confused How to reference TransactionId in my Gv_receivedOffice table.

    I hope now its clear to you.


    Please Mark as Answer if my post works for you or Vote as Helpful if it helps you. Kapil Singh

    Friday, March 22, 2013 11:19 AM
  • No, it is not clear. Why do you duplicate TransactionId in GV_ReceivedOffice in the first place?


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Friday, March 22, 2013 1:01 PM
  • No, it is not clear. Why do you duplicate TransactionId in GV_ReceivedOffice in the first place?


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    Based on some some scenarios I want to store data in another table. so I needed these columns information in that table


    Please Mark as Answer if my post works for you or Vote as Helpful if it helps you. Kapil Singh

    Friday, March 22, 2013 1:10 PM
  • Can you post CREATE TABLEs? Thanks.

    Kalman Toth Database & OLAP Architect sqlusa.com
    Paperback / Kindle: Pass SQL Exam 70-461 & Job Interview: Programming SQL Server 2012

    Friday, March 22, 2013 1:34 PM
  • Can you post CREATE TABLEs? Thanks.

    Kalman Toth Database & OLAP Architect sqlusa.com
    Paperback / Kindle: Pass SQL Exam 70-461 & Job Interview: Programming SQL Server 2012

    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],
     CONSTRAINT [UN_GVVoucher_VoucherNo] UNIQUE NONCLUSTERED 
    (
    [VoucherNo] 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

    CREATE TABLE [dbo].[GV_ReceivedOffice](
    [ReceivedOfficeID] [int] IDENTITY(1,1) NOT NULL,
    [VoucherNo] [varchar](20) NULL,
    [TransactionID] [varchar](10) NULL,
    [UserID] [int] NULL,
    [ReceivedDate] [datetime] NULL,
    PRIMARY KEY CLUSTERED 
    (
    [ReceivedOfficeID] 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_ReceivedOffice]  WITH CHECK ADD FOREIGN KEY([VoucherNo])
    REFERENCES [dbo].[GV_Voucher] ([VoucherNo])
    GO


    Please Mark as Answer if my post works for you or Vote as Helpful if it helps you. Kapil Singh

    Friday, March 22, 2013 1:36 PM
  • I redesigned the ReceivedOffice table so no data is duplicated from Voucher. Take a look at it.

    CREATE TABLE dbo.GV_Voucher(
     VoucherId int IDENTITY(1,1) PRIMARY KEY,
     VoucherTypeId int NOT NULL REFERENCES dbo.GV_VoucherType,
     VoucherNo varchar(20) NOT NULL UNIQUE,
     Denomination int NOT NULL,
     ExpiryDate datetime NULL,
     CreatedDate datetime NULL,
     ModifyDate datetime NULL,
     UserId varchar(10) NULL,
     VoucherStatusId int NOT NULL  REFERENCES dbo.GV_VoucherStatus,
     TransactionID varchar(20) NOT NULL,
     Quantity int NOT NULL,
     AmountValue int NULL);
    GO
     
     /*
    CREATE TABLE dbo.GV_ReceivedOffice(
     ReceivedOfficeID int IDENTITY(1,1) PRIMARY KEY,
     VoucherNo varchar(20) NULL REFERENCES dbo.GV_Voucher,
     TransactionID varchar(10) NULL,
     UserID int NULL,
     ReceivedDate datetime NULL);
     
    GO
     
    */
    
    CREATE TABLE dbo.GV_ReceivedOffice(
     ReceivedOfficeID int IDENTITY(1,1) PRIMARY KEY,
     VoucherID INT REFERENCES GV_Voucher,
     ReceivedDate datetime NULL);


    Kalman Toth Database & OLAP Architect sqlusa.com
    Paperback / Kindle: Pass SQL Exam 70-461 & Job Interview: Programming SQL Server 2012

    Friday, March 22, 2013 1:57 PM
  • I redesigned the ReceivedOffice table so no data is duplicated from Voucher. Take a look at it.

    CREATE TABLE dbo.GV_Voucher(
     VoucherId int IDENTITY(1,1) PRIMARY KEY,
     VoucherTypeId int NOT NULL REFERENCES dbo.GV_VoucherType,
     VoucherNo varchar(20) NOT NULL UNIQUE,
     Denomination int NOT NULL,
     ExpiryDate datetime NULL,
     CreatedDate datetime NULL,
     ModifyDate datetime NULL,
     UserId varchar(10) NULL,
     VoucherStatusId int NOT NULL  REFERENCES dbo.GV_VoucherStatus,
     TransactionID varchar(20) NOT NULL,
     Quantity int NOT NULL,
     AmountValue int NULL);
    GO
     
     /*
    CREATE TABLE dbo.GV_ReceivedOffice(
     ReceivedOfficeID int IDENTITY(1,1) PRIMARY KEY,
     VoucherNo varchar(20) NULL REFERENCES dbo.GV_Voucher,
     TransactionID varchar(10) NULL,
     UserID int NULL,
     ReceivedDate datetime NULL);
     
    GO
     
    */
    
    CREATE TABLE dbo.GV_ReceivedOffice(
     ReceivedOfficeID int IDENTITY(1,1) PRIMARY KEY,
     VoucherID INT REFERENCES GV_Voucher,
     ReceivedDate datetime NULL);


    Kalman Toth Database & OLAP Architect sqlusa.com
    Paperback / Kindle: Pass SQL Exam 70-461 & Job Interview: Programming SQL Server 2012

    Kalman,

    can you plz explain me  why you created gv_receivedOffice table in such a way. I added transactionID in this table but you are not using it now.


    Please Mark as Answer if my post works for you or Vote as Helpful if it helps you. Kapil Singh

    Friday, March 22, 2013 6:21 PM
  • When you JOIN the two tables, you can get transactionID from the other table, no need to duplicate information.

    Kalman Toth Database & OLAP Architect sqlusa.com
    Paperback / Kindle: Pass SQL Exam 70-461 & Job Interview: Programming SQL Server 2012

    Friday, March 22, 2013 6:24 PM