>> 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 ‘[0-9][0-9][0-9]-00-00’)
alpha_alpha_ein CHAR(10) NOT NULL
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.