April 15, 2019 at 5:59 pm
Hi everyone, I currently have two tables:
A.
Entity Name, Entity EIN, Amount
B.
Fiscal Year, Entity Name, Entity EIN
I want to create a foreign key that reference A to B (i.e. B as control table) using Entity Name and Entity EIN only.
However, I couldn't create a primary key with Name and EIN in B because each entity name & ein combination were listed twice with different Fiscal year value in B. How can I create a key in B with Entity name & EIN listed only once?
I tried this:
ALTER TABLE B
ADD CONSTRAINT PK_Entity
PRIMARY KEY ([Entity EIN],[Entity Name])
It gives me an error: The CREATE UNIQUE INDEX statement terminated because a duplicate key was found for the object name
I then tried using DISTINCT and it's not working:
ALTER TABLE B
ADD CONSTRAINT PK_Entity
PRIMARY KEY (Distinct [Entity EIN],[Entity Name] from B)
Error message is: Incorrect syntax near the keyword 'Distinct'.
Thank you so much!!
April 15, 2019 at 6:08 pm
You need a separate table to specify a unique entry for ( [Entity Name], [Entity EIN] ).
(Actually, hopefully EIN by itself would be unique. If it is, use just that. If it's not, use both.)
A FK lookup requires a unique index entry to verify against.
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
April 15, 2019 at 6:17 pm
Thank you that makes sense! Do I just create another table C to store EIN and Name and try to link A to C instead? Can you show me an example code? I'm still new to all this.. Thanks!
April 15, 2019 at 6:41 pm
Yep, just create another table.
CREATE TABLE dbo.Entity_master (
[Entity Name] nvarchar(60) NOT NULL, [Entity EIN] char(10) /*or whatever*/ NOT NULL,
CONSTRAINT Entity_master__PK PRIMARY KEY ( [Entity Name], [Entity EIN] )
)
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
April 15, 2019 at 6:49 pm
Thank you! So I want to read the unique values from table B and put it into the new table I created. How do I do that?
This is what I wrote and it shows error message: Incorrect syntax near the keyword 'select'.
Insert into Cntrl.tbl_Entity_master values (select distinct [Entity Name], [Entity EIN] from B)
April 15, 2019 at 6:53 pm
You can use either VALUES or SELECT, you can't combine them:
Insert into Cntrl.tbl_Entity_master
select distinct [Entity Name], [Entity EIN] from B
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
April 15, 2019 at 6:53 pm
Insert into Cntrl.tbl_Entity_master select distinct [Entity Name], [Entity EIN] from B;
April 15, 2019 at 7:12 pm
Thank you! What if instead of DISTINCT, I want to say put these data in new table if fiscal year = "2017"?
April 15, 2019 at 7:35 pm
One, figure it out by reading the Microsoft documentation. It is your friend.
Two, what if you have [Entity Name] and [Entity EIN] values in FY 2018 that aren't in FY 2017?
April 15, 2019 at 9:07 pm
I would have the same EIN & Name combination listed out for both years, that's the way the table was set up. Is there a way to do it? Or which section should I look at under Microsoft documentation? Thank you!
April 15, 2019 at 9:14 pm
Table "C" only has each unique Name and EIN combination only 1 time, no matter how many fiscal years or amounts it has. That't the whole point of the "C" table: to provide a unique FK reference value for the A and B tables.
And please stop using "tbl_" on table names. They're a programming crutch, not a design or naming feature. Also, you definitely want to be able to change the table name to a view or even perhaps a synonym in the future without having to rename them. And, let's face it, you wouldn't go to all the trouble to actually rename it, so "tbl_whatever" will actually be a view or synonym, leaving it as the wrong name.
Insert into Cntrl.Entity_master
select distinct [Entity Name], [Entity EIN] from A
union
select distinct [Entity Name], [Entity EIN] from B
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
April 15, 2019 at 9:25 pm
I would have the same EIN & Name combination listed out for both years, that's the way the table was set up. Is there a way to do it? Or which section should I look at under Microsoft documentation? Thank you!
If you have a new company in FY 2018 that you didn't have in FY 2017, how will you get the information if you only use the data in FY 2017?
Also, start here: https://docs.microsoft.com/en-us/sql/t-sql/statements/insert-transact-sql?view=sql-server-2017
April 17, 2019 at 5:41 pm
>> I currently have two tables: <<
where is the DDL for these two tables? You expect us to write the DDL for you because you feel you're so privileged? What you did post of course is total garbage and has to be incredibly corrected by people who are working for you for free. Let me try:
CREATE TABLE Alphas
(alpha_alpha_name VARCHAR(25) NOT NULL,
alpha_alpha_ein CHAR(10) NOT NULL PRIMARY KEY,
alpha_amt DECIMAL (8,2) NOT NULL
CHECK (alpha_amt >= 0.00);
Did you notice that a table has to have a key? This is not an option. Nor is it a narrative; it has to be a declaration in DDL. Your next table is also fundamentally wrong. Not a little wrong fundamentally wrong! I am assuming that your alpha_alpha_name is determined by alpha_alpha_ein (too bad you are sooo special we have to do your DDL for you).
CREATE TABLE Betas
(fiscal_yr CHAR(10) NOT NULL
CHECK (fiscal_yr LIKE '[12][0-9][0-9][0-9]-00-00')
PRIMARY KEY,
alpha_alpha_ein CHAR(10) NOT NULL
REFERENCES Alphas(alpha_alpha_ein)
ON DELETE CASCADE
>> I want to create a foreign key that reference A to B (i.e. B as control table) using alpha_alpha_name and alpha_alpha_ein only. <<
I spent 10 years of my life on the ANSI X3H2 standards committee for databases, and I have written over 10 books on SQL and RDBMS. Somewhere along the line, I missed the phrase "control table"; please enlighten me.
If you had posted DDL, as has been required for over 30 years in RDBMS and SQL forums, would we know that the combination of (alpha_alpha_name, alpha_alpha_ein) is a key?
>> However, I couldn’t create a primary key with alpha_name and alpha_ein in B because each alpha_alpha_name & alpha_ein combinations were listed twice with different fiscal_yr value in B. How can I create a key in B with alpha_alpha_name & alpha_ein listed only once? <<
Why do you think that having a three-part primary key is wrong? (alpha_name, alpha_ein, fiscal_yr) is just fine as a key, assuming uniqueness of the triplet.
I'd like to point out that in RDBMS. There is no such crap as "entity", "person", or other generic names for things. RDBMS is based on logic in the most fundamental rule of logic is called the law of identity. If you're a Randite, you will summarize this as "a is a"; but more formally it reads, "to be is to be something in particular; to be nothing in particular or to be everything in general, is to be nothing at all. It has no meaning." This is the problem generics.
You have problems with basic netiquette and posting, but your real problem is you don't know the fundamentals of logic or RDBMS.
Please post DDL and follow ANSI/ISO standards when asking for help.
April 17, 2019 at 9:06 pm
>> I currently have two tables: << where is the DDL for these two tables? You expect us to write the DDL for you because you feel you're so privileged? What you did post of course is total garbage and has to be incredibly corrected by people who are working for you for free. Let me try: CREATE TABLE Alphas (alpha_alpha_name VARCHAR(25) NOT NULL, alpha_alpha_ein CHAR(10) NOT NULL PRIMARY KEY, alpha_amt DECIMAL (8,2) NOT NULL CHECK (alpha_amt >= 0.00); Did you notice that a table has to have a key? This is not an option. Nor is it a narrative; it has to be a declaration in DDL. Your next table is also fundamentally wrong. Not a little wrong fundamentally wrong! I am assuming that your alpha_alpha_name is determined by alpha_alpha_ein (too bad you are sooo special we have to do your DDL for you). CREATE TABLE Betas (fiscal_yr CHAR(10) NOT NULL CHECK (fiscal_yr LIKE '[12][0-9][0-9][0-9]-00-00') PRIMARY KEY, alpha_alpha_ein CHAR(10) NOT NULL REFERENCES Alphas(alpha_alpha_ein) ON DELETE CASCADE >> I want to create a foreign key that reference A to B (i.e. B as control table) using alpha_alpha_name and alpha_alpha_ein only. << I spent 10 years of my life on the ANSI X3H2 standards committee for databases, and I have written over 10 books on SQL and RDBMS. Somewhere along the line, I missed the phrase "control table"; please enlighten me. If you had posted DDL, as has been required for over 30 years in RDBMS and SQL forums, would we know that the combination of (alpha_alpha_name, alpha_alpha_ein) is a key? >> However, I couldn’t create a primary key with alpha_name and alpha_ein in B because each alpha_alpha_name & alpha_ein combinations were listed twice with different fiscal_yr value in B. How can I create a key in B with alpha_alpha_name & alpha_ein listed only once? << Why do you think that having a three-part primary key is wrong? (alpha_name, alpha_ein, fiscal_yr) is just fine as a key, assuming uniqueness of the triplet. I'd like to point out that in RDBMS. There is no such crap as "entity", "person", or other generic names for things. RDBMS is based on logic in the most fundamental rule of logic is called the law of identity. If you're a Randite, you will summarize this as "a is a"; but more formally it reads, "to be is to be something in particular; to be nothing in particular or to be everything in general, is to be nothing at all. It has no meaning." This is the problem generics. You have problems with basic netiquette and posting, but your real problem is you don't know the fundamentals of logic or RDBMS.
And once again you provide no real help but you definitely show strength in attacking others. Your attacks do nothing to help and possibly help drive newbies away. Please, learn some tact when dealing with others on forums like this.
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply