CODEWITHSUNDEEP
sqlsql-server
Shatha Kt
Shatha Kt

Reputation: 57

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?

Upvotes: 1

Views: 4849

Answers (1)

Giorgi Nakeuri
Giorgi Nakeuri

Reputation: 35780

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.

Upvotes: 3

Related Questions