Could Not Create A Foreign Key On 2 Unique Columns

Feb 12, 2006

Hello.
Could anyone tell me why it is not possible to create a foreign key on two columns those references on 2 columns in another table? Those 2 columns have each a unique constraint.

I have:
CREATE TABLE T_PK (ID1 INT CONSTRAINT CHK_UNIQUE1 UNIQUE,ID2 INT CONSTRAINT CHK_UNIQUE2 UNIQUE)

CREATE TABLE T_FK (ID1 INT, ID2 INT)

And I want to do:

ALTER TABLE T_FK ADD CONSTRAINT CHK_FK FOREIGN KEY (ID1, ID2) REFERENCES T_PK (ID1,ID2)

I see no reason why this is not working because always
a row in the table T_FK referencing only one row in table T_PK.

Thank you.
Have a nice day.

View 4 Replies


ADVERTISEMENT

Create A Foreign Key To Unique Constraint?

Jul 3, 2015

why it is not possible to create a Foreign key to a Unique constraint?

Table A has column 1 holding a Primay key and two columns (2 and 3) holding a Unique combination (and some more columns).He created an Unique constraint on column 2 and 3 together.

He wanted to use this Unique combination to point to table B (instead of the table 1's PK) so he tried to create a foreign key on a column in table B but an error popped up prompting;

The columns in table 'TABLE_A' do not match an existing primary key or UNIQUE constraint.

Ok - these two columns ar no PK but the hold an Unique constraint......

View 2 Replies View Related

Create Unique Index On Multiple Columns

Jan 19, 2008

Hello, I will explain myself further. I want to make my table in such a way that no two colums have the same value for example:
Row 1 - Column 1 = "cool"
Row 1 - Column 3 = 91
Row 3 - Column 1 = "cool"
Row 3 - Column 3 = 91
 
I dont care about one column having duplicate values, I want to protect against  Column 1 and 3 having the same values on other rows. Is this possible to do in sql server?

View 4 Replies View Related

How To Create A Unique Constraint On Composite Columns

May 5, 2008

I am trying to create a Unique Constraint on a SQL Server 2005 table where the uniqueness is based on 2 columns.

Could anybody provided some help on how I could enforce this on an existing table (link, or example) I have been looking around without luck.

Thanks in advance

John.

View 4 Replies View Related

T-SQL (SS2K8) :: Identify Columns Which Will Create Unique Record In A Table

Sep 15, 2014

I am looking to create a script that will go through a table a pick out the necessary columns to create a unique record. Some of the tables that I am working with have 200 plus columns and I am not sure if I would have to list every column name in the script or if they could be dynamically referenced. I am working with a SQL server that has little next to no documentation and everytime I type to mere some tables, I get too many rows back.

View 4 Replies View Related

Setting Unique 'key' ID, And Foreign Key

May 20, 2004

i am trying to set up a customer table with several personal details, including Login and password combined to make a unique identifier. I have made Login the key to the table, but how do i make Login and Password a combined identifier. When i click key in password it just removes the key from Login and places it there.
Also how do i include a foreign key in another table ??
Sorry if these questions seem a little lame, I am only a beginner.

H

View 1 Replies View Related

DB Design :: Foreign Key Referencing Non Unique Field In Server

Oct 12, 2012

I ran into an interesting situation.  I'm working on contract and was looking at creating an ERD for an existing database when I ran into a problem.  I found FK's that are referencing columns that do not have a unique constraint or a unique index. 

I don't know the history of the database but was there a time in SQL Server history where this would have been possible?  I scripted out the tables and created it in a test database.  When I run the script to create the FK I get the following message. 

I double checked the original tables and this FK does exist in table1 and there is no unique anything in the referenced table, table2.  Currently the database is running on SQL Server 2008 Ent.

ALTER TABLE [dbo].[table1] WITH CHECK ADD CONSTRAINT [FK_table1] FOREIGN KEY([Col1])
REFERENCES [dbo].[table2] ([col2])

There are no primary or candidate keys in the referenced table 'dbo.table2' that match the referencing column list in the foreign key 'FK_table1'.

View 4 Replies View Related

Foreign Key Columns

Aug 8, 2006

I am deleting a column from a table in code.  Before I drop the colum I need to find if the column belongs to a foreign key.  I have the names of the foreign keys for the table from sysobjects.How can I determine what columns are part of the foreign key?

View 9 Replies View Related

How Do I Create A Foreign Key

Apr 27, 2007

I have a one to many relationship between two tables. The master table is keyed using an int ID field set to IsIdentity yes and this is the primary key. The field name is ChurchID. Likewise the detail table has such a field as it's primary key and is named ContactID.
 I carry the ChurchID field in the detail table and would like to make it the foreign key. I could not find this explained in the two books I have and the help was anything but that )perhaps it's just me afterall).
 Well, any help in this matter would be greatly appreciated as I find myself at a standstill until this can be resolved.
Thanks in advance.

View 2 Replies View Related

Why I Can Not Create The Foreign Key?

Jun 4, 2007

Hi, all experts here,

I encountered a very strange problem which not allowed me to create the foreign key between two tables. The column in one table I have set to be primary key, and it is existing in another table already. Therefore I am trying to create the foreign key for these two tables based on this coloum, but the database does not allow me to create this foreign key. The error message is always as: 'Alter table statement conflict with constraint 'foreign key_name', in table 'table_name', column 'table_key'). So why is that? And I am really looking forward to hearing from you shortly for your advices and help.



With best regards,

Yours sincerely,



View 4 Replies View Related

Query Foreign Key Columns And Tables

Dec 20, 2006

I am trying to query the database to get me the foreign key columns and the tables they belong to.I have: The name of the tableI need:The name of the column in the target tableThe name of the column in the referenced tableThe name of the referenced table  Any help would be great, thanks 

View 5 Replies View Related

Find Out The Columns Should Be Referred Through Foreign Key

Aug 2, 2007

For example if we clearly define a foreign key in the master and child table as following script -

CREATE TABLE master(pkey int PRIMARY KEY, data varchar(10))
GO
CREATE TABLE child (fkey int CONSTRAINT fk_master_child
FOREIGN KEY (fkey) REFERENCES master(pkey))


We can find out the two tables reference relationship by looking at INFORMATION_SCHEMA tables.

However, if the two tables are created in this way €“


CREATE TABLE master(pkey int PRIMARY KEY, data varchar(10))
GO
CREATE TABLE child (fkey int)


Does any one know how to programming verify the actual reference relationship exists between pkey and fkey in these two tables?


Thanks

View 1 Replies View Related

UGH! Failed To Enable Constraints. One Or More Rows Contain Values Violating Non-null, Unique, Or Foreign-key Constraints.

Jan 9, 2007

I know this is probably a flick of a switch but I cannot figure out which switch.  Setup is SQL Server / Stored Procedures / DAL / BLL(skipped for testing) / PL.  The stored procedure queries from only one table and two columns are ignored because they are being phased out.  I can run the stored procedure and preview the data in the DAL but when I create a page with an ODS linked to the DAL and a GridView I get this error.  I checked every column that does not allow nulls and they all have values.  I checked unique columns (ID is the only unique and is Identity=Yes in the table definition).  I checked foreign-key columns for values that are not in the foreign table and there are none.  Any ideas why do I get this? 
Failed to enable constraints. One or more rows contain values violating non-null, unique, or foreign-key constraints.

View 3 Replies View Related

Failed To Enable Constraints. One Or More Rows Contain Values Violating Non-null, Unique, Or Foreign-key Constraints.

Jan 17, 2008

Hi,
    I am getting the above error when trying to load a report into my Web Application, I have tracked the error down to one specific field in my database. Even though this field is a NVarChar field and is of size 30 it would seem that  there is an issue returning the value from the field. I can write it into the database no problems but when I try to get it out of the database it returns the above error.
e.g
MOB 401.908.804 - Fails
0401.907.324 - okay
8239 9082 (pager) - fails
Anyone got an idea on how to fix this????
Regards..
Peter.

View 7 Replies View Related

Finding Columns That Are Intended For Foreign Keys

Apr 24, 2006

Hi Friends,

me again. I am trying to find out if all of my intended foreign keys are actually set as foreign keys programmatically.

Ive stuffed something up, please find my error

USE RIQDB1
SELECT DISTINCT 'Alter table '+ table_name +
' ADD CONSTRAINT DF_'+ table_name + '_' + column_name +
' DEFAULT ' + ''''' FOR '+ column_name FROM Information_schema.columns
WHERE ((column_name Like '%fk%') AND (FROM Information_schema.type = F))
go

thanks
Cm

View 9 Replies View Related

Making 2 Columns Unique With Each Other

Feb 21, 2008

Hi Guys, I came here again to ask something about my application, Is there a way to make 2 columns unique with each other even if they don't have a primary key? If there's a way how? I wanna make a rule so, my application wont replicate its record.   Graciaz,Nhoyti 

View 3 Replies View Related

Use Identity For Unique Columns? Or Not?

Feb 13, 2004

I use the identity = yes for my unique columns in most of my tables that need it. I am trying to decide if I should change identity = no, and instead manually update my unique number column myself by adding one when I insert new rows.

The reason I want to do this is for maintainabilty and ease of transfering data for backup to other sql servers. I always have trouble keeping the identity numbers to stay the same as they are in the original database because when they are transfered to a db that has identity = yes, the numbers get rearranged.

It will also make it easier to transfer data from original db to another sql server db and use the data right away without having to configure the destination db to disable identity and then enable it, etc.

Any pro's con's appreciated,

Dan

View 8 Replies View Related

How To Get Unique Record With Columns

Mar 20, 2013

I have result set as the attached in screenshot, where the candidateid gets duplicated, but then the column university and careerchoice column has different values for the same candidate. (the CareerChoice and University comes from different tables and i have used union clause)

What want is e.g. CandidateId 186 repeats three times, it should be one row, with three columns all filled with the values which are in different rows.

So that I don't have duplicate candidate ids and i have single row with all column data.

View 6 Replies View Related

Unique Index On 2 Columns?

Apr 19, 2013

I find to be able to have multiple NULL entries in the following constraint:

Code:

CREATE UNIQUE INDEX my_uidx ON my_table(my_col1,my_col2) WHERE ??? IS NOT NULL;

But is not possible to check multiple columns in "WHERE".

I using SQL Server 2012 Express

View 3 Replies View Related

Designing Unique Columns

Mar 7, 2006

Hi i am designing a database. It will be used by field guys who will insert new records and will replicate at the end of the day with a central database using merge replication. Can anyone tell me if I am choosing wisely or not with the following 3 points:
1. i am going to use a uniqueidentifier value in a column (using newid()) for the merge replication. (I don't care that it's an ugly value as i never plan to look at it.

2. I am using an identity column (INT)to create a unique value within the table

3. I want to create a unique column comprising of data from other columns (e.g. date+identity+salesrep) This gives me an intelligent candidate for a primary key. But I think i have to create this with an instead of trigger (is that right)

many thanks in advance

ICW

View 5 Replies View Related

Can't Create A Foreign Key Using Non_primary Key Fields

Aug 9, 2004

Howdy all !
I'm just a bit on the frustrated side cause I want to create a foreign key but SQL Server won't let me. :(

I have table A with a primary key of main_id
I have table B with a primary key of another_id

Table A has a corresponding field called another_id.

I'm trying to create a foreign key between Table A & Table B on another_id but since it's not the Primary Key in Table A I get the following error:

There are no primary or candidate keys in the referenced table 'A' that match the referencing column list in the foreign key 'fk_classB_classA'.

Am I missing something totally obvious here? Why should I have to create a foreign key on a primary key?

What I find interesting is that I can create the relationship from enterprise manager but when I script it out is when I run into problems.

Here's the script I'm using:

Begin Code

alter table B add
constraint fk_classB_classA foreign key
(classB) references A (classB)
on delete no action
on update no action

End Code

Any help is greatly appreciated.

tam

View 6 Replies View Related

Create Table With Foreign Keys

May 30, 2007

How do I create a table with a foreign key

View 6 Replies View Related

Create Foreign Key Using Composite Primary Key

Jul 24, 2014

I am trying to create a FK using a composite PK and here are the details that I want to achieve.

Table -A
Column A1 not null,
Column A2 not null
Primary key (A1, A2).

Table -B
Column B1 Primary key.
Column B2 not null
FK (B2) References A(A1).

When I try to do this I am getting some errors. Questions: First of all is this possible? if yes, then how I can create it.

View 1 Replies View Related

SQL 2012 :: Explicit Columns Or NULL Foreign Keys?

Mar 19, 2014

I have a table called Appointment, for storing (you guessed it) appointments at a medical practice. If an appointment is cancelled, I want to collect a cancellation reason, so let's say that I create a second table called Cancellation which has a foreign key relating to the Appointment table's primary key, AppointmentID, and another column, Reason. Now, in order to indicate that an appointment was cancelled, I could include a Cancelled column in the Appointment table with a bit datatype, or instead I could infer that an appointment must be cancelled if it has a corresponding record in the Cancellation table.

It may be that it'd be better to store the cancellation reason in the Appointment table - But regardless, let's say I stick with the two-table solution described above, and I subsequently want to write a query to list all appointments which have been cancelled. If I had the Cancelled column in the Appointment table, I could simply query for all records in that table where that column's value was FALSE. If I went the other way and DIDN'T have a Cancelled column, I could instead write a query joining the Appointment and Cancellation tables to return all records in Appointment with a corresponding record in Cancellation.

That latter method, whilst slightly more complicated because it involves joining two tables, seems to me to be the most normalised. Instead of storing the fact that an appointment is cancelled in two different tables, that fact is only stored in the Cancellation table. Would there be a performance hit in using the two-table, 'inferred cancellation' method rather than just having a bit column in the Appointment table? Would that performance hit be enough to persuade you to use a Cancellation column in the Appointment table instead? And what about if I were to apply that method to other things associated with each appointment, e.g. Is it completed? Is it chargeable to the client or an insurance company? Is the client and in-patient or out-patient?

View 6 Replies View Related

How To Find All Primary And Foreign Key Columns In All Database Tables

Apr 17, 2014

how to find all primary key columns & foreign key columns in all database tables?

View 1 Replies View Related

DB Design :: Remove Foreign Keys For Audit Columns?

Aug 25, 2015

We have a database with hundreds of tables, each with "CreatedByLoginId" and "ModifiedByLoginId" FK columns back to the Login table.  This is all fine and well, but 500+ tables all link back to Login table every time a record is inserted or updated.

For strictly performance reasons, what do you think of us REMOVING the FK constraints on all of our tables?  While this does mean that a GUID that is not a valid LoginId could potentially be put in a table, I'm not too worried about it because users don't have direct access to the database.

View 4 Replies View Related

Create Unique Row

Jun 3, 2006

I am new to MS SQL and I was wondering is it possible to create a table with unique rows?? By this I mean if a table has two columns then a duplicate row would be if BOTH columns matched two columns of another row.

Thanks

View 3 Replies View Related

Select Unique Rows For All Columns

May 17, 2007

Using DISTINCT with SELECT have effect only for one column.
But when is needed to select (or to count) queries for all rows for all columns in a table without duplicates, doesn't work.

Select DISTINCT a1,a2,a3,a4 From Y ---> results 167 rows
Select DISTINCT a4 From Y ---> " 85 rows

Any thoughts?


Jorge3921

View 14 Replies View Related

Unique Index On X Columns But Not Primary Key

Feb 23, 2006

my table :

CREATE TABLE [dbo].[users] (
[ID] [int] NOT NULL ,
[A1] [nvarchar] (100) NULL ,
[A2] [nvarchar] (100) NULL ,
[A3] [nvarchar] (100) NULL
) ON [PRIMARY]

i must keep ID columns as primary key

ALTER TABLE [dbo].[users] WITH NOCHECK ADD
CONSTRAINT [PK_users] PRIMARY KEY CLUSTERED
(
[ID]
) ON [PRIMARY]


but now A1+A2 must be unique

how can i do it ?

thank you

View 5 Replies View Related

Merge Tables On Unique Key Using Different Columns

May 15, 2008

hi guys,

i have 3 tables (T1, T2, T3), each with the same structure:
ID1 -> char(10)
ID2 -> char(12) NULL
ID3 -> char(10)
Value1 -> money
Value2 -> money
Value3 -> money
Note1 -> Text
Note2 -> Text

ID1+ID2+ID3 is the clusterd unique key in each table

what i want:
ID1, ID2, ID3 (with distinct occurencies of all 3 tables), T1.Value2, T2.Value2, T2.Value3, T3.Value1, T3.Note1

what i tried is to get all possible rows with

SELECT ID1,ID2,ID3 FROM T1
UNION
SELECT ID1,ID2,ID3 FROM T2
UNION
SELECT ID1,ID2,ID3 FROM T3

but i dont know how to join or add the other columns. maybe with

WITH RowList (ID1,ID2,ID3) AS ( ... code above.... ) SELECT ...


any ideas?
thx

View 10 Replies View Related

Unique Constraint On Multiple Columns

Oct 31, 2005

Can you create a unique constraint on multiple columns, or does it haveto be implemented as a unique index?If possible can someone please post some sample code?Thanks,

View 4 Replies View Related

Unique Index For Two Columns In A Table

Jul 20, 2005

Hi,I would like to add a unique index that consists of two fields in atable.e.g. tbl_A (field1,field2) -- field1 & field2 Indexed and combinationmust be Unique.Can anyone tell me the actual sql syntax to create this index?Thanks,June.

View 3 Replies View Related

Data Flows And Unique Columns.

Feb 9, 2007

Perhaps one too many 2000 DTS packages have permanently damaged my ability to think clearly - however, I've find myself very frustrated attempting to create a SSIS Data Flow which replaces a very simple 2000 DTS package.

Take data from table1 in database1, put it in table2 in database2. Table2 in Database2 has an additional column as part of the primary key - so I need to add an arbitrary unique value in each row as it's inserted. Previously, I did this in the transformation script through a variable I incremented.

What's the recommend method to do this now - since row level processing of variables seem to be a no-no?

View 5 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved