Shatha Kt Shatha Kt - 6 months ago 41
SQL Question

SQL Server Foreign Key Constraint References Invalid Table

I have the following code:

CREATE TABLE _CLIENT
(
client_id int ,
client_name varchar(50),
type varchar(50),
constraint _CLIENT_pk PRIMARY KEY(client_id),
constraint _CLIENT_ch CHECK (client_id>0),
typee_id INT NOT NULL REFERENCES CLIENT_TYPE(typee_id)
)

CREATE TABLE CLIENT_TYPE
(
typee_id int NOT NULL,
name_type varchar(50),
constraint CLIENT_TYPE_pk PRIMARY KEY(typee_id)
)


The foreign key throws an error saying:

Foreign key 'FK__Number__Name__1CF15040' references invalid table 'Users.Name'


what's the wrong here?

Answer

I don't know what exact error message you are getting, but you have error in the current script and I think you mean this error:

Foreign key 'FK___CLIENT__typee_i__55BFB948' references invalid table 'CLIENT_TYPE'.

You should first create CLIENT_TYPE table, so the script should look like:

CREATE TABLE CLIENT_TYPE
(
    typee_id INT NOT NULL ,
    name_type VARCHAR(50) ,
    CONSTRAINT CLIENT_TYPE_pk PRIMARY KEY ( typee_id )
)

CREATE TABLE _CLIENT
(
    client_id INT ,
    client_name VARCHAR(50) ,
    type VARCHAR(50) ,
    CONSTRAINT _CLIENT_pk PRIMARY KEY ( client_id ) ,
    CONSTRAINT _CLIENT_ch CHECK ( client_id > 0 ) ,
    typee_id INT NOT NULL
                REFERENCES CLIENT_TYPE ( typee_id )
)

As a general rule, you should first create base tables and then tables which depend on those ones.