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
ADVERTISEMENT
Jun 15, 2006
For example, the table below, has a foreign key (ManagerId) that points to EmployeeId (primary key) of the same table.
-------Employees table--------
EmployeeID . . . . . . . . . . int
Name . . . . . . . . . . . nvarchar(50)
ManagerID . . . . . . . . . . . int
If someone gave you an ID of a manager, and asked you to get him all employee names who directly or indirectly report to this manager.
How can that be achieved?
View 6 Replies
View Related
Sep 18, 2006
This book 'MS SQL Server 2000 Bible' says the following:
'The foreign key can reference primary keys, unique constraints, or unique indexes of any table'
I've never heard of this before, I thought that the FK always established referential integrity by referencing the PK in the parent table. Does anyone have a further explanation of this?
thx,
Kat
View 5 Replies
View Related
May 16, 2008
I have a UniqueIdentifier as a self referencing foreign key. The pk gets set by default using the newid(). I need the foreign key to default to that same value. how can i do that? @@identity doesnt work, $rowguid doesnt work, column name doesnt work. Any ideas?
View 9 Replies
View Related
Oct 26, 2013
I'm encountering this very weird problem, so I create a staff table:
CREATE TABLE Staff (
staffNo numeric(10),
venueNo numeric(10),
name nvarchar(20),
DOB datetime,
position nvarchar(20),
salary numeric(8,2)
CONSTRAINT staff_PK PRIMARY KEY(staffNo, venueNo),
CONSTRAINT venue_FK FOREIGN KEY(venueNo) REFERENCES Venue
);
and then when I create a professional therapist table
CREATE TABLE Professional_Therapist (
staffNo numeric(10),
specialization nvarchar(20),
bonus numeric(8,2),
CONSTRAINT professional_PK PRIMARY KEY(staffNo),
CONSTRAINT staff_FK FOREIGN KEY(staffNo) REFERENCES Staff
);
It says : The number of columns in the referencing column list for foreign key 'staff_fk' does not match those of the primary key in the referenced table 'Staff'.
View 3 Replies
View Related
Aug 4, 2015
I have taken on a contract to improve reporting for an old HR database that was developed using FoxPro (Visual FoxPro, I think) with the data stored in SQL Server 2000. There are no foreign keys in SQL Server 2000 so the relationships are maintained inside FoxPro.Is there a way of extracting the relationships from the FoxPro code and generate foreign keys in SQL Server, so that I can do proper design?
View 7 Replies
View Related
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
Nov 24, 2005
I would have a question, if it is possible to reference a calculated field in T-SQL query. The following expression does not work in SQLExpress 2005
SELECT Qty, UnitPrice, Tax, Qty*UnitPrice as Expr1, Expr1*(1.0 + Tax) AS Expr2
This problem has occurred after upsizing to SQLExpress from Access XP/Jet Engine. Since Access does not have any issue with the expression above, the SQLExpress does not even accept it.
The only way how to avoid the issue in the SQLExpress seems probably to be
- Duplicate some calculations (i.e. expand every expression with duplicating some mathematic operations with some performance loss)
- Utilize computed columns if possible (no idea on performance impact here)
Am I right or is there any other way how to reference a calculated field?
Any suggestion is greatly welcomed! Thanks in advance.
View 1 Replies
View Related
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
View Related
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
Sep 26, 2006
Hi all, I am still in the process of learning ASP.NET. The simple goal I am trying to accomplish is to set a string equal to a field in my SQL Database using ASP.NET.I am just not sure what the correct syntax is to do so. It may be work noting that I am programming in VB.NET. I have my SQL Data Source setup correctly and inserted into my form with an ID of "PDCalculations".The field that I want to reference is "Percentage" which is an integer field.The reason I want to put the amount into a string is so I can use it in some calculations that are happening on my form.Thanks in advance.
View 1 Replies
View Related
Dec 27, 2013
So for years I was using the int identity(1,1) primary_key for all the tables I created, and then in this project I decided, you know, I like the uniqueidentifier using newsequentialid() to ensure a distinctly unique primary key.
then, after working with the php sqlsrv driver, I realized huh, no matter what, i am unable to retrieve the scope_identity() of the insert
So of course I cruised back to the MSSMS and realized crap, I can't even make the uniqueidentifier an identity.
So now I'm wondering 2 things...
1: Can I short cut and pull the uniqueidentifier of a newly inserted record, even though the scope_identity() will return null or
2: do I now have to add a column to each table, keep the uniqueidentifier (as all my tables are unified by that relationship) and also add a pk field as an int identity(1,1) primary_key, in order to be able to pull the scope_identity() on insert...
View 3 Replies
View Related
Jul 23, 2005
I have a table called BidItem which has another table calledBidAddendum related to it by foreign key. I have another table calledBidFolder which is related to both BidItem and BidAddendum, based on acolumn called RefId and one called Type, i.e. type 1 is a relationshipto BidItem and type 2 is a relationship to BidAddendum.Is there any way to specify a foreign key that will allow for thedifferent types indicating which table the relationship should existon? Or do I have to have two separate tables with identical columns(and remove the type column) ?? I would prefer not to have multipleidentical tables.
View 26 Replies
View Related
Jul 13, 2015
I am using SSRS 2014 SP1 x64 and VS2013 Update 5.
I have the following expressions to attempt to calculate ageing on a statement report.
=SUM(IIF(DateDiff("d", (Fields!OrderDate.Value, "Invoice"), Now())<=0, (Fields!LineAmount.Value, "Invoice"), 0))
=SUM(IIF(DateDiff("d", (Fields!OrderDate.Value, "Invoice"), now())>=1 and DateDiff("d", (Fields!OrderDate.Value, "Invoice"),Now())<=30, (Fields!LineAmount.Value, "Invoice"), 0))
=SUM(IIF(DateDiff("d", (Fields!OrderDate.Value, "Invoice"), now())>=31 and DateDiff("d", (Fields!OrderDate.Value, "Invoice"), Now())<=60, (Fields!LineAmount.Value, "Invoice"), 0))
=SUM(IIF(DateDiff("d", (Fields!OrderDate.Value, "Invoice"), now())>=61 and DateDiff("d", (Fields!OrderDate.Value, "Invoice"), Now())<=90, (Fields!LineAmount.Value, "Invoice"), 0))
=SUM(IIF(DateDiff("d", (Fields!OrderDate.Value, "Invoice"), now())>=91, (Fields!LineAmount.Value, "Invoice"), 0))
Now when I try to add First to the field selector I get an error stating ... uses a First, Last or Previous aggregate in an outer aggregate.
How exactly do I reference this field from the Invoice dataset (there are at least two datasets)?
View 5 Replies
View Related
Jul 26, 2015
I have the following query:
Select p.Id [SenderId], p.Username, up.PhotoId,
CASE
WHEN mr.ReadDate is null then 1 -- New message
ELSE 0 -- Message has been read
END AS NewMessage,
p.LastLoggedIn, p.LoggedIn
[Code] ....
The above query returns me all messages (inbox functionality) that have been sent to mr.ReceipientId, the issue I have is when I send another email to the recipient the readdate field will be null, and the other emails linked to the recipient which have also been sent via me will have a readdate date. This causes duplicate rows to appear due to the case statement, I'm trying to figure out if / how it is possible to only display the one row per conversation and set newmessage to 1 if there is an un-read message otherwise show 0 ?
So instead of showing this:
2Person102015-07-26 17:07:24.9370
2Person112015-07-26 17:07:24.9370
I'm trying to get it to look like this:
2Person112015-07-26 17:07:24.9370
Or if we have no new mail (read date is not null) then it will appear like this, and I can confirm when there is no new mail it works as expected.
2Person102015-07-26 17:07:24.9370
View 1 Replies
View Related
Sep 22, 2015
I have a field I am trying to bring into a SQL statement
,ISNULL(Convert(nvarchar(50),OPP1.OriginatingLeadId),'') AS 'OriginatingLeadId'
I get this error message
Conversion failed when converting from a character string to uniqueidentifier.
the field specs' originatingleadid is attached ....
View 9 Replies
View Related
Oct 21, 2014
I have a comma separated field containing numerous 2 digit numbers that I would like splitting out by a corresponding unique code held in another field on the same row.
E.g
Unique Code Comma Separated Field
14587934 1,5,17,18,19,40,51,62,70
6998468 10,45,62,18,19
79585264 1,5,18
These needs to be in column format or held in an array to be used as conditional criteria.
Unique Code Comma Separated Value
79585264 1
79585264 5
79585264 18
View 5 Replies
View Related
Sep 4, 2015
I'm trying to setup my foreign keys so I can use them in a VisualStudio projects but I'm getting an error and I can't track down what's causing it.
'tbl_departments' table saved successfully
'tbl_people' table saved successfully
'tbl_locations' table saved successfully
'tbl_position' table
- Unable to create relationship 'FK_tbl_position_tbl_departments'.
The ALTER TABLE statement conflicted with the FOREIGN KEY constraint "FK_tbl_position_tbl_departments". The conflict occurred in database "MyProjectDB", table "dbo.tbl_departments", column 'ID'. All of my tables have an ID field that is a primary key with Allow Null unchecked.
View 3 Replies
View Related
Jul 2, 2004
First of all, this is my initial thread here on dbforums. I come from the land of Broadband Reports and would like to say, Hello fellow DB enthusiasts. :)
I'm not a novice to relational databases (Access MDBs), but new to implementing a db via SQL SERVER (2000 in this case) and using Access Data Projects.
My partial db schema is as follows:
participants
---DID (pk) char(1)
---LID (fk - schools) char(4)
---studentLast varchar(50)
---studentFirst varchar(25)
Sample Data would be
010191M001 | 5671 | SPARKS | JONATHAN
030495F283 | 5671 | DYLAN | CYNTHIA
=====================================
enrollhist (insert/update trigger for enrollactive)
---EID (pk - autonumber) bigint(8)
---EMID (fk - enrollmode) int(4)
---DID (fk - participants) char(10)
---LID (fk - schools) char(4)
---enrollactive bit(1)
Sample Data would be
38173 | 4 | 030495F283 | 9003 | 0
38266 | 3 | 010191M001 | 5671 | 0
39022 | 6 | 030495F283 | 9003 | 0
39036 | 5 | 030495F283 | 9003 | 0
39044 | 4 | 030495F283 | 5671 | 1
39117 | 4 | 010191M001 | 5671 | 1
=====================================
enrollmode
---EMID (pk) int(4)
---mode varchar(25)
Sample Data would be
1 | RECEIVED
2 | WAITING
3 | PENDING
4 | ENROLLED
5 | DROPPED
6 | TRANSFERRED
10 | ORPHANED
11 | DENIED
=====================================
schools
---LID (pk) varchar(4)
---CTID (fk - caltracks) char(1)
---AID (fk - agencies) char(1)
---SDID (fk - schooldist) char(1)
---COID (fk - countydist) char(1)
---sitename varchar(25)
---sitetitle varchar(75)
Sample Data would be
5671 | 3 | 2 | 1 | 4 | ASCOT | ASCOT AVENUE
9003 | 2 | 1 | 4 | 1 | ROWAN | ROWAN AVENUE
2865 | 1 | 3 | 2 | 3 | BRIGHT | BIRDELEE BRIGHT
=====================================
caltracks
---CTID (pk) char(1)
---legend char(4)
---trktitle varchar(15)
---trkcnt int(4)
Sample Data would be
1 | 9030 | 90/30 | 4
2 | CON6 | CONCEPT-6 | 3
3 | SNGL | SINGLE TRACK | 1
=====================================
agencies
---AID (pk) char(1)
---legend varchar(4)
---agencytitle varvhar(50)
Sample Data would be
1 | CRYS | CRYSTAL STAIRS
2 | MAOF | MEXICAN AMERICAN FOUNDATION
3 | PATH | PATHWAYS
4 | CCRC | CHILD CARE RESOURCE CENTER
5 | CHSC | CHILDREN'S HOME SOCIETY OF CALIFORNIA
==========================================
THE REMAINING "FKs" FROM SCHOOL ARE SIMILAR, as is other tables and their relationships. The design of the foreign keys were made using sql and the keyword "REFERENCES" and "FOREIGN KEY."
My questions are: :confused:
(1) Is the use of FK as a Constraint any different than using an INDEX and how?
(2) Should I Alter the Tables to include CASCADING Up/Down?
(3) Are the use of CHARs Ok for the Keys?
(4) Have I over/under-normalized any of the relationships?
View 4 Replies
View Related
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
Apr 12, 2007
Hello,
I'm hoping someone can point me in the right path with a design issue I'm running into. I'm somewhat new with database design and SQL server, so bear with me.
I'm creating a sort of project management system, and I've got a main PROJECT table, and then a STUDENT_PROJECT_PARTICIPANT table, with foreign keys to STUDENT and PROJECT tables. Each has an 'institute' field. What I want to do is to be able to report on the number of students involved in each project, the number of unique students involved in project with a particular institute, and then the number of unique students involved in all projects. I'd also like to be able to report the number of unique students involved in a custom-picked list of projects (this seems the least feasible). The trick is that I need to report this statics within a specific date range. My original idea was to just save statistics of the numbers of students involved in a project every time it is saved, but from there I'm not sure how I can find out the unique count for all projects, since I don't have the date that each student was saved. That's my basic problem. Let me know if you need any clarification.
If anyone has done something similar or has any advice for me on this, please let me know!
Thanks!
Daryl
View 6 Replies
View Related
Sep 3, 2015
I have one table like below Test table. My requirement is to create constraints to confirm <g class="gr_ gr_331 gr-alert gr_gramm Grammar only-ins replaceWithoutSep" data-gr-id="331" id="331">uniqueness</g> of STID value 101 with LN.
like
ID - LN - STID
1 - 'ABC' - 101 ---- Valid Row
2 - 'ABC' - 202 --- Valid Row
3 - 'ABC' - 202 --Valid Row (as I want only unique when LN = 'ABC' with STID = 101)
4 - 'ABC' - 101 -- Invalid Row (As I want uniqueness base on LN and STID = 1011)
create table dbo.Test
(
ID int identity,
LN varchar(50),
STID bigint
)
Is this possible with constraints as I don't want to use <g class="gr_ gr_1041 gr-alert gr_gramm Grammar only-ins doubleReplace replaceWithoutSep" data-gr-id="1041" id="1041">trigger</g>.
View 4 Replies
View Related
Nov 6, 2015
I have come up with one scenarios where I have three table like Product, Services and Subscription. I have to create one table say Bundle where I can have some of the product id , service id and Subscription id , i.e. a bundle may contains sum prduct , services and subscription . How I can design these relations ?
View 3 Replies
View Related
Sep 27, 2007
Hi All,
I am designing database where few of the master tables will reside in different database or in case different server. Scenario is
Server "A" with Database "A" may host the "Accounts" table.
Server "B" with Database "B" may host the "Product" table.
I am designing database "Project" which will hosted in Server "A".
My application requires this master tables [readonly access] as data inserted in my application refers this tables. Also there are reports to be generated which refer this tables.
How do i design my database and sql queries?
I am thinking of approach of having equivalent tables created in my database and writing service which keep tables in my database in sync. This will ensure good perfomance during transaction and reports as they will need to refer this table locally as opposed to different database or different server.
Any thoughts on above approach?? or any better/standard way for such scenarios ?
Thanks in Advance. Your inputs will be of great help.
View 14 Replies
View Related
Apr 4, 2008
Newbie question. Can someone show me an SQL statement that sorts the results of a query by a field in a different table?
CREATE TABLE `Table1`
(
`table1_id` INTEGER AUTO_INCREMENT ,
`table1_name` VARCHAR(255),
PRIMARY KEY (`table1_id`)
)
CREATE TABLE `Table2`
(
`table2_id` INTEGER AUTO_INCREMENT ,
`table2_name` VARCHAR(255),
`table2_table1` INTEGER,
PRIMARY KEY (`table2_id`)
)
ALTER TABLE `Table2` ADD FOREIGN KEY (`table2_table1`) REFERENCES `Table1`(`table1_id`);
What I'd like is something like:
SELECT * FROM Table2 ORDER BY "Table1(table2_table1).tabel1_name",table2_name
It's the section in double quotes that I can't figure out how to compose.
As an example, if Table 1 has
1, A
2, B
3, C
and Table 2 has
1, a, 2
2, b, 1
3, c, 2
then I'd like the sort to return
2, b, 1
1, a, 2
3, c, 2
TIA,
Stephen
View 2 Replies
View Related
Jul 26, 2007
is there a way to cascade update to foreign keys where both fields (PK & FK) are located in two different database?
View 2 Replies
View Related
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
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
May 31, 2001
I have a bit field only alow one row as 1 all other rows should always 0.
How can I control that -- trigger or constraint?? How?
View 1 Replies
View Related
Nov 17, 2004
I have a database table with a primary key that auto-increments - using MS SQL Server 2000.
However I have another field (standard field) that must not allow duplicates.
Besides writing code to sift through the database every time to check for duplicates (which would take long to search through the db every time), is there any setting in MS SQL Server 2000 that allows for an automatic check for duplication and will prevent duplicates!??
View 14 Replies
View Related
Nov 18, 2004
I have a database table with a primary key that auto-increments - using MS SQL Server 2000.
However I have another field (standard field) that must not allow duplicates.
Besides writing code to sift through the database every time to check for duplicates (which would take long to search through the db every time), is there any setting in MS SQL Server 2000 that allows for an automatic check for duplication and will prevent duplicates!??
Here's an example of what I mean...
PK Entry_Sheet Cust_Id Date
1 62345 cust1 16/11/2004
2 45663 cust6 16/11/2004
3 82917 cust1 16/11/2004
4 19283 cust2 17/11/2004
5 28764 cust2 18/11/2004
I have everything created, with data already in the table. What I need now is some way to prevent duplicates from occurring.
The Entry_Sheet value needs to be unique. Instead of writing code to run through the entire db each time an insert is performed, is there a simpler way using a SQL Server DB!??
View 12 Replies
View Related
Jun 6, 2008
I'm a total newb to SQL so I apologize if I butcher the description of what I'm looking to do :) Hopefully the example makes more sense. Is it possible for a single record in 1 table to contain relationships to several records in a different table?
What I've got so far is 2 tables - Hardware and Software, with ID Specifying primary keys HardwareID and SoftwareID respectively. the hardware records are for computer details and the software obviously are for programs. What I need to be able to do is pull up a hardware record and be able to see what software is installed on it, and vice versa pull up a software record to see which computers it's installed on. The problem I'm running into is that a computer can have multiple programs installed on it and a program can be installed on multiple computers, so I don't know how to create that relationship to account for that.
What I'd like to see is in the Hardware table a column for SoftwareID that has a foreign key relationship to the SoftwareID field in the Software Table, and vice versa....My question though is is that possible to do and have potentially multiple separate records it links to from that same column field? I might have a computer with say Windows XP Pro, Office 2003 Standard, Adobe Acrobat 8 and a proprietary rate calculator program that i need each to be displayed with their details when I open that computer's record. Or on the software side if I need to see which computers a license is already installed on then I want to make sure I can pull up the full list of computers.
And finally if what I'd like to do isn't possible as I described it...any recommendations for a better way?
Thanks all for the help!
View 3 Replies
View Related
Jul 20, 2005
There are two tables, parent and child table.Foreign key field of the child table is not a required field. If thedata is submitted w/o a value into the foreign key field, what shouldI make it as a default value? Should it be "null"?
View 2 Replies
View Related
